-- http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
-- U: Percent of Update Operations on the Object
-- To compute U, use the statistics in the DMV sys.dm_db_index_operational_stats. U is the ratio (expressed in percent) of updates performed on a table or index to the sum of all operations (scans + DMLs + lookups) on that table or index. The following query reports U for each table and index in the database.
SELECT s.name AS SchemaName,
o.name AS TableName,
x.name AS [Index_Name],
--i.partition_number AS [Partition],
i.index_id AS [Index_ID],
x.type_desc AS [Index_Type],
SUM(i.leaf_update_count) * 100.0 / (SUM(i.range_scan_count) + SUM(i.leaf_insert_count) + SUM(i.leaf_delete_count)
+ SUM(i.leaf_update_count) + SUM(i.leaf_page_merge_count)
+ SUM(i.singleton_lookup_count)) AS [Percent_Update]
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) i
INNER JOIN sys.tables o ON o.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.schemas s ON s.SCHEMA_ID = o.SCHEMA_ID
INNER JOIN sys.indexes x ON x.OBJECT_ID = i.OBJECT_ID
AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + leaf_update_count + i.leaf_page_merge_count
+ i.singleton_lookup_count) != 0
AND o.is_ms_shipped = 0
GROUP BY s.name,
o.name,
x.name,
--i.partition_number,
i.index_id,
x.type_desc
ORDER BY [Percent_Update] ASC
GO
-- S: Percent of Scan Operations on the Object
-- To compute S, use the statistics in the DMV sys.dm_db_index_operational_stats. S is the ratio (expressed in percent) of scans performed on a table or index to the sum of all operations (scans + DMLs + lookups) on that table or index. In other words, S represents how heavily the table or index is scanned. The following query reports S for each table, index, and partition in the database.
SELECT s.name AS SchemaName,
o.name AS TableName,
x.name AS [Index_Name],
--i.partition_number AS [Partition],
i.index_id AS [Index_ID],
x.type_desc AS [Index_Type],
SUM(i.range_scan_count) * 100.0 / (SUM(i.range_scan_count) + SUM(i.leaf_insert_count) + SUM(i.leaf_delete_count)
+ SUM(i.leaf_update_count) + SUM(i.leaf_page_merge_count) + SUM(i.singleton_lookup_count)) AS [Percent_Scan]
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) i
INNER JOIN sys.objects o ON o.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.schemas s ON s.SCHEMA_ID = o.SCHEMA_ID
INNER JOIN sys.indexes x ON x.OBJECT_ID = i.OBJECT_ID
AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + leaf_update_count + i.leaf_page_merge_count
+ i.singleton_lookup_count) != 0
AND o.is_ms_shipped = 0
GROUP BY s.name,
o.name,
x.name,
--i.partition_number,
i.index_id,
x.type_desc
ORDER BY [Percent_Scan] DESC