September 30, 2008 at 2:24 pm
We have a large 100 Million+ record table on which were created the following 6 indexes. (I replaced the real field names with letters so it might be easier to read on the forum).
These indexes take up a huge amount of storage and I have always wondered it might be possible to revise these indexes somehow to reduce them somehow since fields A is in 5 indexes and B is in all 6
Ideas anyone ?
CREATE NONCLUSTERED INDEX [Index1] ON [TableName] (A, B, C, D, E, F, G, H)
CREATE NONCLUSTERED INDEX [Index2] ON [TableName] (A, B, I, J, L, E, F)
CREATE NONCLUSTERED INDEX [Index3] ON [TableName] (A, B, C, E, F, G, H, N)
CREATE NONCLUSTERED INDEX [Index4] ON [TableName] (A, B, K)
CREATE NONCLUSTERED INDEX [Index5] ON [TableName] (B)
CREATE UNIQUE NONCLUSTERED INDEX [Index6] ON [TableName] (A, B, C, D, J, I, M, O, E, F, H, G, P)
September 30, 2008 at 2:47 pm
Would it be possible for you to give us the table design as well as a general idea on how often each of the indexes are used (i.e. index 1 is used 300 times a day while index2 is only used once a week for a report).
I would also ask if column B is unique.
Fraggle
September 30, 2008 at 3:05 pm
These indexes could be covering indexes and before you removed any of them I urge you to read BOL Creating Indexes with Included Columns at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/973b128d-5114-4d48-8eab-52497b47611e.htm
Briefly, and very briefly:
index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.
Removing some of these indexes can seriously slow down your systems response time, I urge you to tread carefully before removing them.
September 30, 2008 at 3:06 pm
What's the clustered index on this table? None of these are.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 30, 2008 at 3:14 pm
No, Column B is not unique. These are detail transactional records so the only singularly unique field is an Identity field (which is not part of any of the indexes). Index6 is the only other form of uniqueness.
As far as usage, this system was designed and coded by a long departed member. My guess is that these are mostly used to speed reporting as covering indexes.
This is really just a warehouse type app which gets loaded once a day and the reported against at a VERY low level pace.
No one is actually complaining about performance. I am just trying to boost my understanding of how SQL uses indexes. I have always looked at these indexes and wondered if there was not a better way since many had very similar elements. (Indexes 1, 2, 3, 4, 6 all start with A, B and Indexes 1, 3, 6 all start with A, B, C)
I am hoping that someone with more index background than I will look at this as see something obvious and point it out.
September 30, 2008 at 3:17 pm
The clustered index is on the RecordNumber which is an Identity field.
September 30, 2008 at 3:35 pm
Maybe this is wrong, and I am sure someone will correct me if I am, but I would try adding columns A & B to the clustered index. Then use the non-clustered indexes for the remaining columns. The non-clustered should use the clustered index to find A & B and then use the pointer to get the remainder of the data required.
Someone please let me know if I am wrong.
Fraggle.
October 1, 2008 at 2:15 am
Depends on how the tables are been used. If many of the queries are of the form
SELECT A, B, C, E, F, G, H, N FROM [TableName] WHERE A = @a and B=@B
then removing A, B from the NC indexes and adding them to the cluster is going to make the index useless.
The clustered index keys are added at the end, if they are not present in the index anyway, so removing A, B from the noncluster and adding to the cluster will result in an effective index of:
C, E, F, G, H, N, RowNumber, A, B
which is useless for seeks on A, B
The non-clustered should use the clustered index to find A & B and then use the pointer to get the remainder of the data required.
If the cluster is used to find rows, then the nonclusters will not be touched. The clustered index is the table, and hence contains every column in it.
To really say anything useful, we need to see the form of some of the queries that frequently run. Is that possible?
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
October 2, 2008 at 8:01 am
One point of indices is to get to the data faster when reading (the otehr is to assist uniqueness when inserting/updating data).
If the records being sought can be satisified by supplying A,B & C alone, then having duplicate A,B, & C prefixed indices is redundant. If however there are two style of queries one of which needs A,B,C & D to get optimal results and the other needs A,B,C & E...then you may do better with 2 indices. You will get decent results from just having A,B,C...it's a balancing act to see if having A,B,C,D & A,B,C,E gives better overall performance.....but you won't be able to drop the A,B,C portions from both and just go with 2 indices D & E.
Searching the telephone book for Europe, Ireland, Murphy, Andrew is a lot faster than searching the same book for Andrew....(and then filtering out all the records which don't match the non-indexed search pattern)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply