SQL Server - Index Workload Breakdown - CRUD Mix

gistfile1.txt

--- Index Scan/Seek/Lookup/Update Breakdown

SELECT
	DB_NAME([ddius].[database_id]) AS [database name],
	s.[name] AS 'Schema_Name',
	OBJECT_NAME([ddius].[object_id]) AS [Table name],
	CASE
		WHEN ddius.index_id=1 THEN '*'+[i].[name]+'*'
		ELSE [i].[name]
	END AS [index name],
	i.is_unique,	
	ddius.database_id,
	--ddius.object_id,
	ddius.index_id,
	p.partition_number,
	ddius.user_seeks,
	ddius.user_scans,
	ddius.user_lookups,
	ddius.user_updates,
	ddius.system_seeks,
	ddius.system_scans,
	ddius.system_lookups,
	ddius.system_updates,
	CAST(p.used_page_count * 0.0078125 AS NUMERIC(18,2)) AS 'UsedPages_MB',
	p.[ROW_COUNT] AS 'RowCount'
FROM
	[sys].[dm_db_index_usage_stats] AS ddius
INNER JOIN
	[sys].[indexes] AS i
ON
	[ddius].[index_id] = [i].[index_id] AND [ddius].[object_id] = [i].[object_id]
JOIN
	sys.dm_db_partition_stats p
ON	
	i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
JOIN
	sys.objects O
ON O.object_id = ddius.object_id
JOIN
	sys.schemas S
ON S.schema_id = O.schema_id
WHERE
	OBJECT_NAME([ddius].[object_id])<>'sysdiagrams' -- filter out sysdiagrams UML table
    AND OBJECTPROPERTY(ddius.OBJECT_ID, 'IsUserTable') = 1 -- User Tables Only
	AND ddius.index_id > 0  -- filter out heaps
	AND ddius.database_id = db_id() -- current DB only
	AND ddius.index_id>1 -- NCIX only
ORDER BY
	1,2,3

-- Server rebooted/restarted engine
-- scheduled clients havent run yet since reboot