SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Usage Stats


Index Usage Stats

Author
Message
oradbguru
oradbguru
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1080 Visits: 316
Comments posted to this topic are about the item Index Usage Stats
Chris Harshman
Chris Harshman
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16235 Visits: 5172
I'm thinking there's something wrong in this script... when I run it against one of my development databases on a table with 6 indexes, it only shows 2 of them, because of the join to sys.dm_db_index_usage_stats should be LEFT OUTER JOIN. Also, it returns 2 copies of each of the 2 indexes it does return, so there's likely another bad join somewhere.

I'm thinking to truly do analysis like this, it would be best to see all the indexes on the table, even if they are not currently in the usage stats. I'd be wary of dropping an index without doing repeated analysis over time in case an index is used by a weekly or monthly process not currently reflected. Here's a query I use to look at index usage:

SELECT s.name + N'.' + t.name AS table_name, i.name AS index_name, i.type_desc, i.is_unique,
SubString(
(SELECT N', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N'' END
FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
ORDER BY ic.key_ordinal FOR XML PATH('')), 3, 1000) AS columns,
SubString(
(SELECT N', ' + c.name
FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
ORDER BY ic.index_column_id FOR XML PATH('')), 3, 1000) AS included,
i.filter_definition, ps.size_MB, d.name AS FileGroup, iu.*
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s on t.schema_id = s.schema_id
INNER JOIN sys.data_spaces d on i.data_space_id = d.data_space_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats iu ON i.object_id = iu.object_id AND i.index_id = iu.index_id AND iu.database_id = DB_ID()
LEFT OUTER JOIN (SELECT object_id, index_id, SUM(used_page_count) / 128 AS size_MB FROM sys.dm_db_partition_stats GROUP BY object_id, index_id) ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE i.name IS NOT NULL
AND i.object_id > 100
AND t.name in ('myTableName')
ORDER BY s.name, t.name, i.name

oradbguru
oradbguru
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1080 Visits: 316
Wonder if it pasted wrong. I don't have that issue in my database with over 14,000 indexes. sorry, I will have a look at it.
oradbguru
oradbguru
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1080 Visits: 316
The script does exactly as it is intended to do and is great for analyzing whether or not indexes are being used efficiently or not. It excludes clustered, unique and primary key indexes but you can easily include them as well by uncommenting those filters in the where clause.
SMNayak
SMNayak
Mr or Mrs. 500
Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)

Group: General Forum Members
Points: 518 Visits: 276
STATS_DATE ( i.object_id , i.index_id ) as IndexCreatedDate
STATS_DATE can't be the IndexCreate date; Per BOL, "Returns the date of the most recent update for statistics on a table or indexed view."
Lee Linares
Lee Linares
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1122 Visits: 1509
Thanks for taking the time to share this with us. Great script. I did however find one issue that was easily fixed though. The same index name can exist in multiple tables. I thought I was getting duplicates until I looked at the CREATE INDEX DDL column and found that the code returned for 1 table was for creating the index on 2 DIFFERENT tables. The statement that needed to be fixed was:

FROM #TMP2 a INNER JOIN #TMP1 b ON a.indexname = b.indexname

Which I changed to:

FROM #TMP2 a INNER JOIN #TMP1 b ON a.indexname = b.indexname AND a.ObjectName=b.TableName

That fixed my problem. Thanks again.

Lee
oradbguru
oradbguru
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1080 Visits: 316
Great find. I never had any duplicate index names so it never reared itself. Thank you for the correction.

David
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search