Today I am sharing another useful query often required during performance tuning/Optimization phase of the project.The query makes use of system DMV’s provided by SQL server.Open new query window in SQL Server and fire the below query against the respective database..
SELECT DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ‘IsMsShipped’) = 0
ORDER BY fill_factor DESC
I would like to have feedback from my blog readers.
Please post your feedback, question, or comments about this article.