May 8, 2017 at 12:48 pm
Comments posted to this topic are about the item Index Usage Stats
May 8, 2017 at 1:50 pm
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
May 8, 2017 at 1:59 pm
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.
May 8, 2017 at 4:12 pm
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.
May 16, 2017 at 8:58 am
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
May 16, 2017 at 1:04 pm
Great find. I never had any duplicate index names so it never reared itself. Thank you for the correction.
David
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply