Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am writing a script to quickly check the indexes in a given database. I think I have the script written ok but to remove duplicate rows I have done alot of SUM functions and I am unsure whether this is correct or not:

SELECT      OBJECT_NAME(i.object_id)                                                                                                                AS 'Table Name'
,           COALESCE(i.name, '')                                                                                                                    AS 'Index Name'
,           i.type_desc                                                                                                                             AS 'Index Type'
,           i.is_primary_key                                                                                                                        AS 'Primary Key'
,           i.is_disabled                                                                                                                           AS 'Index Disabled'
,           ROUND(CONVERT(FLOAT,SUM(user_seeks)) / (CONVERT(FLOAT,ISNULL(NULLIF(SUM(user_scans),0),1))),2)                                          AS 'Seeks Per Scan'
,           ROUND((CONVERT(FLOAT,SUM(user_seeks) +SUM(user_scans) + SUM(user_lookups))) / (CONVERT(FLOAT,ISNULL(NULLIF(SUM(user_updates),0),1))),2) AS 'Reads Per Write'
,           (SUM(user_seeks) + SUM(user_scans) + SUM(user_lookups))                                                                                 AS 'Reads'
,           SUM(user_updates)                                                                                                                       AS 'Writes'
,           SUM(user_seeks)                                                                                                                         AS 'Seeks'
,           SUM(user_scans)                                                                                                                         AS 'Scan'
,           ROUND(AVG(ps.avg_fragmentation_in_percent),2)                                                                                           AS 'Avg Fragmentation %'
,           NULLIF((SUM(leaf_insert_count) + SUM(leaf_update_count) + SUM(leaf_delete_count)),0) / NULLIF(SUM(leaf_allocation_count),0)             AS 'Leaf Page Splits Per Write'
,           NULLIF((SUM(nonleaf_insert_count) + SUM(nonleaf_update_count) + SUM(nonleaf_delete_count)),0) / NULLIF(SUM(nonleaf_allocation_count),0) AS 'Non-Leaf Page Splits Per Write'
,           CAST (100.0 * SUM(row_lock_wait_count) / (1 + SUM(row_lock_count)) AS NUMERIC(15,2))                                                    AS 'Block %'
,           SUM(row_lock_wait_in_ms)                                                                                                                AS 'Row Lock Wait (ms)'
,           CAST(1.0 * SUM(row_lock_wait_in_ms) / (1 + SUM(row_lock_wait_count)) AS NUMERIC(15,2))                                                  AS 'Average Row Lock Wait'
FROM sys.indexes i
LEFT JOIN sys.objects o ON o.object_id = i.object_id 
LEFT JOIN sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) s ON o.object_id = s.object_id AND i.index_id = s.index_id
LEFT JOIN sys.dm_db_index_usage_stats u ON s.object_id = u.object_id AND u.index_id = s.index_id
LEFT JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps ON u.index_id = ps.index_id AND u.object_id = ps.object_id
WHERE OBJECTPROPERTY(o.object_id,'IsUserTable') = 1 
AND NOT OBJECT_NAME(i.object_id) = 'sysdiagrams' -- exclude sysdiagrams
--AND i.is_primary_key = 0
GROUP BY OBJECT_NAME(i.object_id), COALESCE(i.name, ''),i.type_desc,i.is_primary_key,i.is_disabled
ORDER BY OBJECT_NAME(i.object_id), COALESCE(i.name, '')

Is this ok? Or is SUMing the rows bad?

Also, is there any information that you think would be helpful for finding indexes that aren't used/need re-indexing etc

share|improve this question
I'd normally separate the queries out: one for fragmentation, one for missing, one for unused etc –  gbn Sep 22 '11 at 11:39
@gbn, I can understand that. The aim of this was to see if I could get the best of all the scripts (except missing indexes) into 1 script/view –  Stuart Blackler Sep 22 '11 at 11:49
add comment

1 Answer

up vote 3 down vote accepted

Why not just use one of the scripts which are out there and available for free. The current reigning version of the one posted on sqlfool.com.

share|improve this answer
because im trying to learn, i dont think anyone can learn by copy - paste - execute –  Stuart Blackler Sep 22 '11 at 21:53
No but you can learn by reading the code that has been written and figuring out how it works. Code that hasn't been reviewed and understood shouldn't ever be put into production, no matter who wrote it. –  mrdenny Sep 22 '11 at 22:04
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.