February 3, 2009 at 1:14 pm
I read about indexes from many forums and they are so confusing. I hope someone can help me by answering the following questions. I know some of you might need more information, but I can’t give you anymore than I could, sorry.
This is a heavily use OLTP database. All PKs are unique clustered and most of them are IDENTITY(1,1). SQL Server was restarted 3 months ago.
1. Some people told me that these indexes are redundant. Are they?
[Employees].[IX_Unique_Name] (LastName,FirstName,MiddleName)
[Employees].[IX_Employees_LastName] (LastName)
[Employees].[IX_Employees_FirstName] (FirstName)
2. I added PK to these tables because some say all tables should have a clustered index. According to the values below (pulled from sys.dm_db_index_usage_stats view), should I drop these PKs and leave them Heap?
- [Table1].[PK_Table1] (PKID) --> Seek:150 SCAN:1700 Write:150.
- [Table2].[PK_Table2] (PKID) --> Lookup:3000000 Write:177500
3. I also have some PKs have no records in sys.dm_db_index_usage_stats. Why is that? Should I drop them?
4. [Programs].[IX_Programs_ProgramID] (ProgramID) --> Scan:39 Write:99221 - Does it mean this index is useless and should be dropped?
5. Some indexes have low density (i.e. 50 unique values out of 10,000 records).
I was told that SQL Server will ignore indexes with low density, so I expected to see 0 in user_seeks and some vales in user_scans, but instead I saw 0 in user_scans and some number in user_seeks. Why is that?
6. Numbers in user_updates of some indexes with low density in #5 above are higher than total counts of user_seeks+user_scans+user_lookups. Does this mean I should keep them although they are low density?
Thank you in advance.
February 3, 2009 at 1:27 pm
I recommend that you read this excellent blog post[/url] by Gail Shaw (GilaMonster on SSC) and then this one as well. You may need to create an account or login to view the second one.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 3, 2009 at 1:31 pm
jungnaja (2/3/2009)
I read about indexes from many forums and they are so confusing. I hope someone can help me by answering the following questions. I know some of you might need more information, but I can’t give you anymore than I could, sorry.This is a heavily use OLTP database. All PKs are unique clustered and most of them are IDENTITY(1,1). SQL Server was restarted 3 months ago.
1. Some people told me that these indexes are redundant. Are they?
[Employees].[IX_Unique_Name] (LastName,FirstName,MiddleName)
[Employees].[IX_Employees_LastName] (LastName)
[Employees].[IX_Employees_FirstName] (FirstName)
The second one is because, to SQL Server, it's the same as the first index. The leading edge of the index (the first field) is the same.
2. I added PK to these tables because some say all tables should have a clustered index. According to the values below (pulled from sys.dm_db_index_usage_stats view), should I drop these PKs and leave them Heap?
- [Table1].[PK_Table1] (PKID) --> Seek:150 SCAN:1700 Write:150.
- [Table2].[PK_Table2] (PKID) --> Lookup:3000000 Write:177500
Simply counting scans or writes or lookups is not the way I'd determine the usefulness of a given index. I'd have to see the composition of the index and the types of queries being run against it before I dropped it out of hand.
3. I also have some PKs have no records in sys.dm_db_index_usage_stats. Why is that? Should I drop them?
With some exceptions, every table should have a primary key and every table should have a clustered index. Frequently these are one and the same, but they don't have to be. Either way, no, I wouldn't drop the primary key from a table. I might consider using some other column or set of columns as the clustered index though.
4. [Programs].[IX_Programs_ProgramID] (ProgramID) --> Scan:39 Write:99221 - Does it mean this index is useless and should be dropped?
Same as 2
5. Some indexes have low density (i.e. 50 unique values out of 10,000 records).
I was told that SQL Server will ignore indexes with low density, so I expected to see 0 in user_seeks and some vales in user_scans, but instead I saw 0 in user_scans and some number in user_seeks. Why is that?
Possibly filtering allowed a seek to be used... I'm not sure. Maybe your statistics are out of date? Based on the information supplied, I'd hesitate to provide a definitive answer
6. Numbers in user_updates of some indexes with low density in #5 above are higher than total counts of user_seeks+user_scans+user_lookups. Does this mean I should keep them although they are low density?
Thank you in advance.
Again, I'm not sure I'd use those numbers for definitive decisions on whether or not to keep an index. However, one that has a very high number scans AND a very low density combined is a definite drop. After that, it depends on other factors.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 7, 2009 at 11:09 am
Grant Fritchey (2/3/2009)
jungnaja (2/3/2009)
I read about indexes from many forums and they are so confusing. I hope someone can help me by answering the following questions. I know some of you might need more information, but I can’t give you anymore than I could, sorry.This is a heavily use OLTP database. All PKs are unique clustered and most of them are IDENTITY(1,1). SQL Server was restarted 3 months ago.
1. Some people told me that these indexes are redundant. Are they?
[Employees].[IX_Unique_Name] (LastName,FirstName,MiddleName)
[Employees].[IX_Employees_LastName] (LastName)
[Employees].[IX_Employees_FirstName] (FirstName)
The second one is because, to SQL Server, it's the same as the first index. The leading edge of the index (the first field) is the same.
To take this a step further. Indexes are actually read from left to right, just like a book. So if you had an index that was like
(Lastname, Firstname) this would essentially be the same as the IX_UniqueName index above in that when sql server went to lookup the values it would search for lastname 1st, then firstname. Since the IX_UniqueName index also includes MiddleName, it does not make sense to have two indexes with the same first to columns, and the one that covers the least amount of data should be removed.
However, if you had an index with the columns of (lastname, FirstName, MiddleName, Address, City, State, Zip), this would actually cover all of the columns from the IX_UniqueName index and more. Had this been the case, you could have dropped the UniqueName index instead.
2. I added PK to these tables because some say all tables should have a clustered index. According to the values below (pulled from sys.dm_db_index_usage_stats view), should I drop these PKs and leave them Heap?
- [Table1].[PK_Table1] (PKID) --> Seek:150 SCAN:1700 Write:150.
- [Table2].[PK_Table2] (PKID) --> Lookup:3000000 Write:177500
Simply counting scans or writes or lookups is not the way I'd determine the usefulness of a given index. I'd have to see the composition of the index and the types of queries being run against it before I dropped it out of hand.
Grant is correct. These number's don't mean a lot with more details. 1700 scans may be perfectly normal for the table if the majority of the work done on the table requires scans (functions like sum and avg will allways require a scan of either the index or table. Also, using the WHERE Col1 BETWEEN Value AND Value also requires scans).
A lookup is basically where it uses an index to find part of the data, but then needs to lookup the remainder of the data from somewhere else. As an example from above. Let's say that the query wanted to also find the phone number of the individual. The ID_UniqueName index would be used to find the first, middle, and lastname, but then it would have to LOOKUP the phone number.
This is generally a bad thing, although there are times that it can be good and unavoidable. However, my personal though process on the matter is that Lookups should be far smaller in number on a table than a scan or seek count is. If not, then the indexes are not being used correctly and MIGHT need to be adjusted.
As for a heap, I never think any table should be in one. Even it is has less than 100 values, and those values never change over 100 years. I still think it should have a clustered index to atleast cover the columns that are getting pulled out of the table on a regular basis
3. I also have some PKs have no records in sys.dm_db_index_usage_stats. Why is that? Should I drop them?
With some exceptions, every table should have a primary key and every table should have a clustered index. Frequently these are one and the same, but they don't have to be. Either way, no, I wouldn't drop the primary key from a table. I might consider using some other column or set of columns as the clustered index though.
4. [Programs].[IX_Programs_ProgramID] (ProgramID) --> Scan:39 Write:99221 - Does it mean this index is useless and should be dropped?
Same as 2
My initial reaction is the same as Grants is that you don't provide enough of the fact to make an educated decision about the index. What are the seeks. What are the lookups? What are the columns in comparison to the other indexes on the table and what are the seeks, scan, lookup values for those indexes. However, my personally, if an index has almost 100k writes, 0 seeks, 0 lookup, and 30 scan, I consider it a waste of space.
NOTE: Something I am unsure of and have never tested is whether a bulk insert would artificially inflate these numbers.
5. Some indexes have low density (i.e. 50 unique values out of 10,000 records).
I was told that SQL Server will ignore indexes with low density, so I expected to see 0 in user_seeks and some vales in user_scans, but instead I saw 0 in user_scans and some number in user_seeks. Why is that?
Possibly filtering allowed a seek to be used... I'm not sure. Maybe your statistics are out of date? Based on the information supplied, I'd hesitate to provide a definitive answer
Same here
6. Numbers in user_updates of some indexes with low density in #5 above are higher than total counts of user_seeks+user_scans+user_lookups. Does this mean I should keep them although they are low density?
Thank you in advance.
Again, I'm not sure I'd use those numbers for definitive decisions on whether or not to keep an index. However, one that has a very high number scans AND a very low density combined is a definite drop. After that, it depends on other factors.
Agree with Grant.
Have a great day.
Fraggle
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply