July 21, 2009 at 10:51 am
I just found that i have an index which has all columns(85)..need advise on it if it make any sense.
CREATE NONCLUSTERED INDEX [index_allfields] ON [dbo].[EmpRev_A]
(
15 columns
)
INCLUDE ( 70 columns) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
I see many performance issues when i do a simple query having a where clause from the 70 columns listed above.
Its not that i just have only this index but i also have 8 more indexes on the same table with the columns listed in this index also. I assume it shouldnt be like this... let me know.
July 21, 2009 at 10:57 am
Looks to me like someone (not necessarily you, Tara) followed the advice of DTA without reviewing what it suggested. That index just duplicates your table. If you need the index on the 15 columns specified, I'd drop it and recreate it without the included columns.
July 21, 2009 at 11:11 am
Yeah, Lynn has it right. That's not a good thing. Not only should you do what he said, but I'd review the other eight indexes. See if there are duplications based on the leading edge. What I mean is, see if there are two indexes that look like this:
ColA, ColB
and
ColA, ColB, ColC
If so, the first index is made redundant by the second. You could drop it too, unless it's the cluster or the primary key.
"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
July 21, 2009 at 6:56 pm
Lynn
what is DTA?
July 21, 2009 at 7:00 pm
The Database Tunig advisor... one of the features in SSMS you can use to figure out what might be missing.
It tends to be overeager on some index recommendations. Not bad, but you have to take it with a grain of salt.
----------------------------------------------------------------------------------
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?
July 22, 2009 at 6:57 am
Grant
I think your point make sense to me but would like to know about how it works.
As you said we do have a column " Cdate " which is there in all the indexes i have on the table and also it has a seperate index just on that Cdate so.. I think it should be only in one place. What if i take out that field from all the indexes and just keep the which one which has seperate index just on that field. Does the queries use this index when ever this field is called?
finally if i want to make this change how would i test on thart CDate field before making real change.
July 22, 2009 at 7:04 am
Is CDate the leading column on any or all of the indexes?
July 22, 2009 at 7:12 am
It is the 1st columns in couple indexes and the rest it is either 2nd or 3rd column with in the indexes.
I dont think they had any reason to put Cdate in certain manner they just put it like that may be as you said what ever DTA gave.
July 22, 2009 at 7:20 am
Tara (7/22/2009)
GrantI think your point make sense to me but would like to know about how it works.
As you said we do have a column " Cdate " which is there in all the indexes i have on the table and also it has a seperate index just on that Cdate so.. I think it should be only in one place. What if i take out that field from all the indexes and just keep the which one which has seperate index just on that field. Does the queries use this index when ever this field is called?
finally if i want to make this change how would i test on thart CDate field before making real change.
Two different topics.
First, what I was trying to show was how two indexes that have the same columns in the same order, even if additional columns are there, actually duplicate the work. So the bigger index, ColA-B-C, duplicates an index that is just ColA-B which duplicates an index of ColA. If you have the first, biggest index, you can drop the other two because they just won't be needed.
Second, it is possible for SQL Server to combine two indexes, called an index intersection, but it is exceedingly rare for it to do so. I had to work very hard to make a query perform an index intersection for an example in the book. It just doesn't happen that much. So no, pulling a column from a bunch of the indexes and giving it an index of it's own is extremely unlikely to result in that index being used in combination with the other indexes.
"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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply