April 17, 2012 at 1:46 pm
SkyBox (4/17/2012)
I attached a file that show the indexes from a table and all the fields in each index. Without spending much time, do you see any obvious indexes that overlap?This output file is from sys.stats and ...
sys.stats doesn't show indexes, it shows statistics and is not what you want to look at when trying to ID duplicate indexes.
Try this script:
SELECT OBJECT_NAME(object_id) AS TableName, i.name AS IndexName, type_desc, is_primary_key, is_unique, LEFT(keycolumns,LEN(keycolumns)-1) AS KeyColumns, LEFT(includecolumns,LEN(includecolumns)-1) AS IncludeColumns
FROM sys.indexes AS i
CROSS APPLY (
SELECT
name + ', ' AS [text()]
FROM
sys.index_columns AS ic INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE
i.object_id = ic.object_id AND i.index_id = ic.index_id
AND ic.is_included_column = 0
ORDER BY
ic.key_ordinal
FOR XML PATH('')
) keycolumnlist(keycolumns)
CROSS APPLY (
SELECT
name + ', ' AS [text()]
FROM
sys.index_columns AS ic INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE
i.object_id = ic.object_id AND i.index_id = ic.index_id
AND ic.is_included_column = 1
ORDER BY
ic.key_ordinal
FOR XML PATH('')
) includecolumnlist (includecolumns)
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND i.index_id>0
ORDER BY TableName, IndexName
Also, for some thoughts on removing indexes:
http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/
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 17, 2012 at 5:44 pm
Thanks for all the advice.
That query is exactly what I needed! I re-attached the sheet with the columns (ordered) for each index (not stat), also listing included columns.
Without understanding my data structure or usage, doubt you can identify overlapping or any indexes that could be merged, but thanks for all the good info!
April 17, 2012 at 6:41 pm
SQLKnowItAll (4/17/2012)
I've dropped some indexes that have 0 reads (or very few) and a high number of writes.
If I did that, I'd be fired. If you don't know why the index is there, find out before you drop it. Repeating Lynn, we have several reports that are run once a year. Without those indexes, it may take just that long to run 🙂
There may also be some indexes that are NEVER hit directly. These indexes sometimes work out to be UNIQUE indexes that support constraints that sometimes make the optimizer not work so hard at finding a good solution.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 17, 2012 at 6:59 pm
Jeff Moden (4/17/2012)
SQLKnowItAll (4/17/2012)
I've dropped some indexes that have 0 reads (or very few) and a high number of writes.
If I did that, I'd be fired. If you don't know why the index is there, find out before you drop it. Repeating Lynn, we have several reports that are run once a year. Without those indexes, it may take just that long to run 🙂
There may also be some indexes that are NEVER hit directly. These indexes sometimes work out to be UNIQUE indexes that support constraints that sometimes make the optimizer not work so hard at finding a good solution.
+1 I learn something new every day... 😉
Jared
CE - Microsoft
April 18, 2012 at 2:02 pm
I've attached another file of indexes on a table that could use some consolidation.
I see a couple that are obvious, but would appreciate any feedback. Just a quick look if you have time.
ie: On lines 11 and 12, can I safely drop 11 and keep 12?
This is a sales line shipments table that is 73gb's and 55gb's of that are indexes. The db is 450bg's
Thanks!
April 18, 2012 at 2:09 pm
SkyBox (4/18/2012)
I've attached another file of indexes on a table that could use some consolidation.I see a couple that are obvious, but would appreciate any feedback. Just a quick look if you have time.
ie: On lines 11 and 12, can I safely drop 11 and keep 12?
This is a sales line shipments table that is 73gb's and 55gb's of that are indexes. The db is 450bg's
Thanks!
You do know that you really should be doing this wwith your developers, right? We don't know your systems so any feedback we give you should be taken with several tons of salt.
April 18, 2012 at 3:32 pm
Lynn Pettis (4/18/2012)
SkyBox (4/18/2012)
I've attached another file of indexes on a table that could use some consolidation.I see a couple that are obvious, but would appreciate any feedback. Just a quick look if you have time.
ie: On lines 11 and 12, can I safely drop 11 and keep 12?
This is a sales line shipments table that is 73gb's and 55gb's of that are indexes. The db is 450bg's
Thanks!
You do know that you really should be doing this wwith your developers, right? We don't know your systems so any feedback we give you should be taken with several tons of salt.
Yes. There's TONS of DTA indexes that were carelessly created by one person over a period of 5 years and I doubt he has a clue which ones he needs or is using. I'll still run it by him and save create index scripts. I'm here for advice because I want to do things the "right" way.
I just want an idea of your general thought processes, so I can apply it for my situation.
April 18, 2012 at 3:53 pm
These 4 can be replaced with one index
_dta_index_line_item_disposition_5_1662628966__K1NONCLUSTERED00order_numberNULL
_dta_index_line_item_disposition_5_1662628966__K1_9987NONCLUSTERED00order_numberNULL
_dta_index_line_item_disposition_5_1662628966__K1_32NONCLUSTERED00order_numberupdate_dtm
_dta_index_line_item_disposition_6_1662628966__K1_K15_5379NONCLUSTERED00order_number, date_shippedNULL
Single index on OrderNumber, DateShipped include UpdateDTM
These 4 can be replaced with one index
_dta_index_line_item_disposition_5_1662628966__K1_K2NONCLUSTERED00order_number, line_numberNULL
_dta_index_line_item_disposition_5_1662628966__K1_K2_18NONCLUSTERED00order_number, line_numberquantity_shipped
PK_line_item_disposition__order_number__line_number__backorder_numberCLUSTERED11order_number, line_number, backorder_numberNULL
_dta_index_line_item_disposition_5_1662628966__K1_K2_K8_18NONCLUSTERED00order_number, line_number, backorder_numberquantity_shipped
Single index on order_number, line_number, backorder_number include quantity_shipped
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 19, 2012 at 3:30 pm
GilaMonster (4/18/2012)
These 4 can be replaced with one index
_dta_index_line_item_disposition_5_1662628966__K1NONCLUSTERED00order_numberNULL_dta_index_line_item_disposition_5_1662628966__K1_9987NONCLUSTERED00order_numberNULL
_dta_index_line_item_disposition_5_1662628966__K1_32NONCLUSTERED00order_numberupdate_dtm
_dta_index_line_item_disposition_6_1662628966__K1_K15_5379NONCLUSTERED00order_number, date_shippedNULL
Single index on OrderNumber, DateShipped include UpdateDTM
These 4 can be replaced with one index
_dta_index_line_item_disposition_5_1662628966__K1_K2NONCLUSTERED00order_number, line_numberNULL_dta_index_line_item_disposition_5_1662628966__K1_K2_18NONCLUSTERED00order_number, line_numberquantity_shipped
PK_line_item_disposition__order_number__line_number__backorder_numberCLUSTERED11order_number, line_number, backorder_numberNULL
_dta_index_line_item_disposition_5_1662628966__K1_K2_K8_18NONCLUSTERED00order_number, line_number, backorder_numberquantity_shipped
Single index on order_number, line_number, backorder_number include quantity_shipped
VERY helpful!
So basically, if you can create or keep an index that can cover serveral others, then queries can use the larger index with more coverage just as efficiently?
If so, then what's a general max number of columns to safely set on a NC index when considering index consolidation? 3-4?
One of our systems (AX 2009) doesn't allow "included columns" on indexes, so with that in mind, I want be sure that those indexes aren't covering too many columns.
April 19, 2012 at 3:51 pm
SkyBox (4/19/2012)
So basically, if you can create or keep an index that can cover serveral others, then queries can use the larger index with more coverage just as efficiently?
It won't be as efficient, but the chances of it being noticable is very slim
If so, then what's a general max number of columns to safely set on a NC index when considering index consolidation? 3-4?
No way to answer that question. Depends on column widths, table size, query types, etc, etc
One of our systems (AX 2009) doesn't allow "included columns" on indexes, so with that in mind, I want be sure that those indexes aren't covering too many columns.
SQL 2000?
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 19, 2012 at 3:53 pm
SkyBox (4/19/2012)
VERY helpful!So basically, if you can create or keep an index that can cover serveral others, then queries can use the larger index with more coverage just as efficiently?
If so, then what's a general max number of columns to safely set on a NC index when considering index consolidation? 3-4?
One of our systems (AX 2009) doesn't allow "included columns" on indexes, so with that in mind, I want be sure that those indexes aren't covering too many columns.
I'll chime in a bit but realize that Gail can run me over with her knowledge and I'd never see it coming. 🙂
Regarding disallowing included columns, the clustered index is one big inclusion list by its very nature. Now, I realize that you're speaking of non-clustered indexes, but can I inquire as to the thought process behind not allowing included columns?
For safely setting NonClustered indexes, it's not just column count that needs to be considered. Column width, existing clustered index columns (which are 'included' in every non-clustered index), rate of update/change, and a number of other things can come into play. Also, the column order, as you've found.
Add to that the explicitness of your where clauses that approach the data and a number of other factors, and anything from "never more than 2" to "Shove the entire table into a second index" can be a viable guideline, depending on the environment.
What you'll need to do is go through a large volume of your queries for rough review, and figure out how often you're doing equivalency checks (IE: colA = @b-2). Ranges mess with how deep into an index tree you can go... and unfortunately I can't find the article(s) that described how the index tree stops driving during the seek, returns the range, and then performs a second filter in the execution plans. If I trip on it again I'll update.
In general, though, # of equivalencies + 1 for your general rule, but always leave room for exceptions. Each table will most likely have a different 'rule'.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 19, 2012 at 4:08 pm
Evil Kraig F (4/19/2012)
Add to that the explicitness of your where clauses that approach the data and a number of other factors, and anything from "never more than 2" to "Shove the entire table into a second index" can be a viable guideline, depending on the environment.
I've done that once. Had a table with clustered index and 2 nonclustered indexes. Both nonclustered indexes had as their include columns every single other column in the table. It really was necessary in that one specific case
and unfortunately I can't find the article(s) that described how the index tree stops driving during the seek, returns the range, and then performs a second filter in the execution plans. If I trip on it again I'll update.
*sniff*
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
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 19, 2012 at 4:10 pm
GilaMonster (4/19/2012)
Evil Kraig F (4/19/2012)
*sniff*http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
Ah hah! There it is. I got my keywords all confuzzled. :crying: Thanks Gail (both for the blog post AND linking it for me. :w00t: )
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 19, 2012 at 5:27 pm
To add a bit that people often forget is the uniqueness of the column(s) indexed. If the data is skewed the index may not be very helpful unless filtered. Do I know how to tell if it needs this offhand? No... Sorry. However, I do know that it is a consideration that should be looked at.
Jared
CE - Microsoft
April 19, 2012 at 5:30 pm
SQLKnowItAll (4/19/2012)
To add a bit that people often forget is the uniqueness of the column(s) indexed. If the data is skewed the index may not be very helpful unless filtered.
That's not necessarily true. Depends very much on the specifics, the query, the full index definition, etc.
At a client a few years ago, over half of the indexes they had (ones that I'd created) had a bit column as the leading column of the index key.
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
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply