April 12, 2017 at 11:37 am
How many rows does a table need to have before you start thinking about adding an index?
April 12, 2017 at 12:01 pm
You must create constraints/indexes along with the table right from the start or you will end up fixing data later.
If you have wide rows, you will only get a couple rows per page. Indexing key columns or creating covering indexes will help tremendously even with as few as a thousand rows.
I generally don't start adding non-clustered, non-unique, non-constraint indexes until a few months into new projects even with tables of 10,000 rows or more.
And I really don't take my non-clustered indexes seriously until I do some production monitoring and look for unused indexes and missing indexes using DMV queries.
April 12, 2017 at 12:02 pm
NineIron - Wednesday, April 12, 2017 11:37 AMHow many rows does a table need to have before you start thinking about adding an index?
Before creating a table, there must have been some design. Right from the outset, you already have an idea of the main queries that will be run against the table. Use that as a starting point for the indexes that will be useful.
With time, the queries and/or data distributions may change, so you need to keep an eye on the worst performing queries in the DB, and look to improve the SQL and/or modify the indexing strategies of the affected tables.
So in short, I always have at least a CLI on a table, even before the first record is inserted.
April 12, 2017 at 12:10 pm
You should determine the best clustered index for every table (the only exceptions could be a bulk load or other staging table). Note that typically this is not an identity column. In other words, don't just lazily slap an identity column on every table so you can cluster on it. Review the actual table stats, including missing index stats, and make an informed decision of the best clustering index for that specific table.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 12, 2017 at 12:15 pm
Folks,
I have inherited a table with 80K-100K records. Mostly, patient demographic data so, the columns are not wide. I was just wondering if I should expect that SQL would use the index with that few records.
April 12, 2017 at 12:49 pm
NineIron - Wednesday, April 12, 2017 12:15 PMFolks,
I have inherited a table with 80K-100K records. Mostly, patient demographic data so, the columns are not wide. I was just wondering if I should expect that SQL would use the index with that few records.
Why not start by checking if SQL thinks you could use an index on the table.
SELECT
DatabaseName = DB_NAME(mid.database_id)
, Avg_Estimated_Impact = migs.avg_user_impact *(migs.user_seeks+migs.user_scans)
, Last_User_Seek = migs.last_user_seek
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
WHERE mid.database_ID = DB_ID()
AND mid.object_id = OBJECT_ID(N'dbo.YourTableName', N'U');
April 12, 2017 at 12:55 pm
NineIron - Wednesday, April 12, 2017 12:15 PMFolks,
I have inherited a table with 80K-100K records. Mostly, patient demographic data so, the columns are not wide. I was just wondering if I should expect that SQL would use the index with that few records.
There are a lot of factors that go into determining whether an index is used and whether it uses an index scan or an index seek. For instance, whether the index is a covering index. Also, the percentage of records to be selected is a much better measure than the total number of records. If you're returning all of the records, you will never get an index seek, but you may get an index scan.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 12, 2017 at 2:02 pm
NineIron - Wednesday, April 12, 2017 11:37 AMHow many rows does a table need to have before you start thinking about adding an index?
One.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 13, 2017 at 4:11 am
Thanx Gail. An answer even I can understand.
April 13, 2017 at 4:14 am
DesNorton,
What specifically am I to look for in the results of this query?
April 13, 2017 at 5:45 am
NineIron - Wednesday, April 12, 2017 12:15 PMFolks,
I have inherited a table with 80K-100K records. Mostly, patient demographic data so, the columns are not wide. I was just wondering if I should expect that SQL would use the index with that few records.
That's not "few" records if you're getting repeated scans from every query that runs against those tables. Although, it depends on the size of your system, the number of people querying it, etc.. Just in general terms, we get past a few hundred rows and I start seeing scans, I'll probably add an index (not counting what everyone else has said, every table, with exceptions, should already have a clustered index).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 13, 2017 at 5:48 am
DesNorton - Wednesday, April 12, 2017 12:49 PMWhy not start by checking if SQL thinks you could use an index on the table.
SELECT
DatabaseName = DB_NAME(mid.database_id)
, Avg_Estimated_Impact = migs.avg_user_impact *(migs.user_seeks+migs.user_scans)
, Last_User_Seek = migs.last_user_seek
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
WHERE mid.database_ID = DB_ID()
AND mid.object_id = OBJECT_ID(N'dbo.YourTableName', N'U');
Just note that while this will show missing index suggestions, it doesn't in any way correlate to the queries that generated those suggestions. That query could have been called a single time and will never be called again or it could be called hundreds of times a minute. Using this method shows that there are missing index suggestions, but it's grossly inadequate information to make decisions about those suggestions.
Instead, if you're going to use this approach, I'd suggest you search up queries that use the information from the DMVs to query the execution plans for missing index suggestions. That allows you to correlate to number of times called, resources used, etc.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 13, 2017 at 5:53 am
NineIron - Thursday, April 13, 2017 4:14 AMDesNorton,
What specifically am I to look for in the results of this query?
If you get any results, then you have queries that are getting data from the specified table, but that SQL believes you need to add indexes to the table to improve performance.
April 13, 2017 at 6:58 am
Pardon my ignorance but, could you comment on the attached file? Looks like I need an index on MRN.
April 13, 2017 at 7:19 am
NineIron - Thursday, April 13, 2017 6:58 AMPardon my ignorance but, could you comment on the attached file? Looks like I need an index on MRN.
Those results merely show that you have a number of queries that could benefit from indexing. Remember, these are merely suggestions from the query engine.
PLEASE - Read Grant's Post above
The correct thing to do is take a look at the queries that touch your table, and see what is REALLY needed.
That said, based purely on the attached, I would start with the following index, and test the effectiveness.
CREATE NONCLUSTERED INDEX [Give-Your-Index-A-Name]
ON [dbo].[YourTableName] ([MRN])
INCLUDE ([Payer], [MbrID], [dob], [PCPNPI]);
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply