Index Read/Write stats

 Index Read/Write stats

Here's a handy SQL query to help you analyze index usage in your database. This script provides valuable insights into how frequently your indexes are being read (through seeks, scans, and lookups) and updated, which can be crucial for performance tuning.

    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);
  

This query focuses on user tables and orders the results by the total number of reads, making it easy to spot your most heavily accessed indexes. Understanding these metrics can guide you in optimizing your database performance.

What kind of performance issues are you currently troubleshooting?