Index Usage

Overall Index Usage – Reads

This query returns index read/write statistics for all tables in the current database ordered by reads. It shows us which indexes in the current database are most active for reads.

SQL Query

SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName], i.[name] AS [IndexName], i.index_id,
       s.user_seeks, s.user_scans, s.user_lookups,
       s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],
       s.user_updates AS [Writes], 
       i.[type_desc] AS [Index Type], i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition,
       s.last_user_scan, s.last_user_lookup, s.last_user_seek
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC OPTION (RECOMPILE); -- Order by reads

Output

Overall Index Usage – Writes

This query returns index read/write statistics for all tables in the current database ordered by writes. It shows us which indexes in the current database are most active for writes.

SQL Query

SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName], i.[name] AS [IndexName], i.index_id,
          s.user_updates AS [Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],
          i.[type_desc] AS [Index Type], i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition,
          s.last_system_update, s.last_user_update
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1
ORDER BY s.user_updates DESC OPTION (RECOMPILE);    -- Order by writes

Output

Possible Bad Non-Clustered Indexes (Writes > Reads)

This query returns possible bad non-clustered indexes (writes > reads) in the current database. From the output of this query look for indexes with high numbers of writes and zero or very low numbers of reads. Consider your complete workload, and how long your instance has been running and investigate further before dropping an index.

SQL Query

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,

i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,

user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],

user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON s.[object_id] = i.[object_id]

AND i.index_id = s.index_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND s.database_id = DB_ID()

AND user_updates > (user_seeks + user_scans + user_lookups)

AND i.index_id > 1 -- NONCLUSTERED

ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

Output

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql

User Defined Functions Execution Statistics

This query returns User-Defined Functions (UDFs) execution statistics for the current database.

SQL Query

SELECT OBJECT_NAME(object_id) AS [Function Name], execution_count,

          total_worker_time, total_logical_reads, total_physical_reads,

       total_elapsed_time

FROM sys.dm_exec_function_stats WITH (NOLOCK)

WHERE database_id = DB_ID()

ORDER BY total_worker_time DESC OPTION (RECOMPILE);

Output

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-function-stats-transact-sql