April 17, 2012 at 9:33 am
I've been archiving index usage stats for about 60 days now. Gradually, table by table - I've dropped some indexes that have 0 reads (or very few) and a high number of writes.
I have indexes that are being written to anywhere from 4-400 times as much as reads. Looking for a good rule of thumb for read to write ratio to help pinpoint which indexes are doing more harm than good.
If the indexes have reads that are 10% or less than the number of writes should I consider dropping?
I'm focusing on core tables that have a high number of records, writes, and reads - and have at least 8-25 NC indexes per table (ie: sales, salesline, address, cust, etc).
April 17, 2012 at 9:36 am
Be very careful dropping indexes. An index you drop may not appear to be used but it may be critical to a process or report that is run infrequently or perhaps as part of a year end process. If you are dropping indexes, be sure you keep a copy of the script needed to recreate it should something come up later.
April 17, 2012 at 9:48 am
Yes, this is a concern of mine. I actually have a spreadsheet/log that records tablename, index, reads, writes, fields in index, date dropped, etc.
I can use this to re-add indexes.
April 17, 2012 at 9:50 am
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 🙂
Jared
CE - Microsoft
April 17, 2012 at 10:00 am
25 indexes on an OLTP table is quite high. You may also want to look for partial duplicate indexes which can be merged with other indexes.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 17, 2012 at 10:32 am
S_Kumar_S (4/17/2012)
25 indexes on an OLTP table is quite high. You may also want to look for partial duplicate indexes which can be merged with other indexes.
There are a lot of indexes that overlap each other - I should focus on those first. This is where I tend to see indexes that aren't being used and have little or no reads.
Guess there's no shortcuts to adding or removing indexes properly, just wish that developers weren't so careless when adding indexes to tables. Most of them think more is better and don't care what already exists.
April 17, 2012 at 10:35 am
SkyBox (4/17/2012)
S_Kumar_S (4/17/2012)
25 indexes on an OLTP table is quite high. You may also want to look for partial duplicate indexes which can be merged with other indexes.There are a lot of indexes that overlap each other - I should focus on those first. This is where I tend to see indexes that aren't being used and have little or no reads.
Guess there's no shortcuts to adding or removing indexes properly, just wish that developers weren't so careless when adding indexes to tables. Most of them think more is better and don't care what already exists.
Just be sure that they are actually "overlapping." I have seen OLTP with more tan 25 indexes that needed all of them. You just have to be sure.
Jared
CE - Microsoft
April 17, 2012 at 10:43 am
SkyBox (4/17/2012)
S_Kumar_S (4/17/2012)
25 indexes on an OLTP table is quite high. You may also want to look for partial duplicate indexes which can be merged with other indexes.There are a lot of indexes that overlap each other - I should focus on those first. This is where I tend to see indexes that aren't being used and have little or no reads.
Guess there's no shortcuts to adding or removing indexes properly, just wish that developers weren't so careless when adding indexes to tables. Most of them think more is better and don't care what already exists.
It isn't necessarily that they are careless. They may not see that an existing index can be used by their query because it has additional columns, wether index columns or included columns. It may be inattention, that don't take the time to review the current structure of the table to see what indexes may already exist, or maybe they pay more attention to the missing index information provided by the dmv's or in the execution plan, or DTA.
This is where a good DBA can earn his (or her) keep, working closely with the developers to ensure duplicate or overlapping indexes aren't created. The key is to be helpful, not a roadblock.
April 17, 2012 at 10:48 am
Lynn Pettis (4/17/2012)
SkyBox (4/17/2012)
S_Kumar_S (4/17/2012)
25 indexes on an OLTP table is quite high. You may also want to look for partial duplicate indexes which can be merged with other indexes.There are a lot of indexes that overlap each other - I should focus on those first. This is where I tend to see indexes that aren't being used and have little or no reads.
Guess there's no shortcuts to adding or removing indexes properly, just wish that developers weren't so careless when adding indexes to tables. Most of them think more is better and don't care what already exists.
It isn't necessariyl that they are careless. They may not see that an existing index can be used by their query because it has additional columns, wether index columns or included columns. It may be inattention, that don't take the time to review the current structure of the table to see what indexes may already exist, or maybe they pay more attention to the missing index information provided by the dmv's or in the execution plan, or DTA.
This is where a good DBA can earn his (or her) keep, working closely with the developers to ensure duplicate or overlapping indexes aren't created. The key is to be helpful, not a roadblock.
And as a note to the OP, (not to you Lynn) not knowing your experience, make sure you really know what an overlapping index is. I have seen people say "I filter on column1 and column3, and since this index is on column1, column2, and column3... I don't need an index just for column1 and column3." Oops!
Jared
CE - Microsoft
April 17, 2012 at 1:14 pm
SQLKnowItAll (4/17/2012)
Lynn Pettis (4/17/2012)
SkyBox (4/17/2012)
S_Kumar_S (4/17/2012)
25 indexes on an OLTP table is quite high. You may also want to look for partial duplicate indexes which can be merged with other indexes.There are a lot of indexes that overlap each other - I should focus on those first. This is where I tend to see indexes that aren't being used and have little or no reads.
Guess there's no shortcuts to adding or removing indexes properly, just wish that developers weren't so careless when adding indexes to tables. Most of them think more is better and don't care what already exists.
It isn't necessariyl that they are careless. They may not see that an existing index can be used by their query because it has additional columns, wether index columns or included columns. It may be inattention, that don't take the time to review the current structure of the table to see what indexes may already exist, or maybe they pay more attention to the missing index information provided by the dmv's or in the execution plan, or DTA.
This is where a good DBA can earn his (or her) keep, working closely with the developers to ensure duplicate or overlapping indexes aren't created. The key is to be helpful, not a roadblock.
And as a note to the OP, (not to you Lynn) not knowing your experience, make sure you really know what an overlapping index is. I have seen people say "I filter on column1 and column3, and since this index is on column1, column2, and column3... I don't need an index just for column1 and column3." Oops!
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 doesn't list the exact field order or included columns per index, so I don't how helpful it really is. Don't know of a better way to compare all indexes to find what truly overlaps.
Thanks
Query:
SELECT Object_name (SS.object_id) AS TABLE_NAME
,SS.name AS INDEX_NAME
,COALESCE (SI.type_desc, '***NO INDEX***') AS INDEX_TYPE
,CASE SI.is_unique
WHEN 1 THEN 'Y'
ELSE 'N'
END AS UNIQUE_INDEX
,Stuff ((SELECT ', ' + C.name
FROM sys.stats_columns AS SC
INNER JOIN sys.columns AS C ON SC.object_id = C.object_id
AND SC.column_id = C.column_id
WHERE SS.object_id = SC.object_id
AND SS.stats_id = SC.stats_id
ORDER BY C.column_id
FOR XML PATH('')), 1, 1, '') AS INDEX_COLUMNS
,CASE SI.is_primary_key
WHEN 1 THEN 'Y'
ELSE 'N'
END AS PRIMARY_KEY
,CASE SI.is_unique_constraint
WHEN 1 THEN 'Y'
ELSE 'N'
END AS UNIQUE_CONSTRAINT
,CASE
WHEN SI.name IS NOT NULL THEN Stats_date (T.object_id, SI.index_id)
ELSE Stats_date (T.object_id, SS.stats_id)
END AS LAST_UPDATE_STATS
,CASE auto_created
WHEN 1 THEN 'Y'
ELSE 'N'
END AS STAT_AUTO_CREATED
,CASE user_created
WHEN 1 THEN 'Y'
ELSE 'N'
END AS STAT_CREATED_BY_USER
FROM sys.stats AS SS
INNER JOIN sys.tables AS T ON Object_name (SS.object_id) = T.name
LEFT OUTER JOIN sys.indexes AS SI ON SS.name = SI.name
WHERE T.is_ms_shipped = 0
AND Object_name (SS.object_id) = 'SALESLINE'
--AND auto_created <> 1 --comment out this line to include stats auto created by SQL SERVER
AND SI.type_desc <> '***NO INDEX***'
ORDER BY T.name,COALESCE (SI.index_id, 999)
April 17, 2012 at 1:33 pm
After a quick look, I saw a duplicate. But without know which columns are indexes or just included columns, not really sure otherwise.
April 17, 2012 at 1:36 pm
Without the field order, it is useless. Index on columnA, columnB, columnC is completely different from columnC, columnB, columnA.
Jared
CE - Microsoft
April 17, 2012 at 1:42 pm
SQLKnowItAll (4/17/2012)
And as a note to the OP, (not to you Lynn) not knowing your experience, make sure you really know what an overlapping index is. I have seen people say "I filter on column1 and column3, and since this index is on column1, column2, and column3... I don't need an index just for column1 and column3." Oops!
Those people may actually be correct. It may be that the existing index on column1, column2, and column3 is good enough for that query that filters on just column 1, column 3 and that a new index is not required. If column 1 is highly selective, then creating the perfect index for that query may be unnecessary, the query could perform perfectly adequately on the existing index.
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 1:43 pm
Do either of you know a better way to compare indexes or know of a utility?
April 17, 2012 at 1:44 pm
GilaMonster (4/17/2012)
SQLKnowItAll (4/17/2012)
And as a note to the OP, (not to you Lynn) not knowing your experience, make sure you really know what an overlapping index is. I have seen people say "I filter on column1 and column3, and since this index is on column1, column2, and column3... I don't need an index just for column1 and column3." Oops!Those people may actually be correct. It may be that the existing index on column1, column2, and column3 is good enough for that query that filters on just column 1, column 3 and that a new index is not required. If column 1 is highly selective, then creating the perfect index for that query may be unnecessary, the query could perform perfectly adequately on the existing index.
Good point. To clarify, it may not b good enough... and simply looking at the definition is not enough to tell us that. (Right?)
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply