Get Read-to-Write Ratio for All Indexes

Here's a quick script to help you quantify how useful your indexes are, in the sense of how much they're used to speed up queries, compared to how much work it is for SQL Server to maintain them during inserts, updates, and deletes. This only reflects the usage since the last SQL Server boot.

-- Displays the read-to-update ratio of all indexes.  Good for finding ones that may not be needed.
DECLARE @dbid INT = DB_ID(DB_NAME())

; WITH cteUser AS
(
    SELECT object_id,
           index_id,
           user_seeks + user_scans + user_lookups   AS 'User Reads',
           user_updates                             AS 'User Updates'
      FROM sys.dm_db_index_usage_stats   
     WHERE database_id = @dbid
)
,
cteSystem AS
(
    SELECT object_id,
           index_id,
           system_seeks + system_scans + system_lookups   AS 'System Reads',
           system_updates                                 AS 'System Updates'
      FROM sys.dm_db_index_usage_stats   
     WHERE database_id = @dbid
)
,
cteTotal AS
(
    SELECT u.object_id,
           u.index_id,
           [User Reads]   + [System Reads]     AS 'Total Reads',
           [User Updates] + [System Updates]   AS 'Total Updates'
      FROM cteUser     u
      JOIN cteSystem   s
        ON u.object_id = s.object_id
       AND u.index_id  = s.index_id
)
,
cteReadToUpdateRatio AS
(
    SELECT object_id,
           index_id,
           CONVERT(NVARCHAR,
                  CONVERT(MONEY, ISNULL(
                                        CAST([Total Reads] AS REAL)
                                        /
                                        NULLIF([Total Updates], 0.0)
                                        , [Total Reads]
                                        )
                               , 1
                         )
                  )   AS 'Read-to-Update Ratio'
                  
      FROM cteTotal
)
SELECT OBJECT_SCHEMA_NAME(i.object_id)                                                                  AS 'Schema Name',
       OBJECT_NAME(i.object_id)                                                                         AS 'Table Name',
       i.name                                                                                           AS 'Index Name',
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, u.[User Reads]),                      1), '.00', '')    AS 'User Reads',                   
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, u.[User Updates]),                    1), '.00', '')    AS 'User Updates',                 
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, s.[System Reads]),                    1), '.00', '')    AS 'System Reads',                 
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, s.[System Updates]),                  1), '.00', '')    AS 'System Updates',               
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, t.[Total Reads]),                     1), '.00', '')    AS 'Total Reads',                
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, t.[Total Updates]),                   1), '.00', '')    AS 'Total Updates',                
                                                r.[Read-to-Update Ratio]                                AS 'Read-to-Update Ratio',         
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, t.[Total Reads] + t.[Total Updates]), 1), '.00', '')    AS 'Total Reads + Total Updates'
  FROM cteUser                        u
  JOIN cteSystem                      s
    ON u.object_id = s.object_id
   AND u.index_id  = s.index_id
  JOIN cteTotal                       t
    ON u.object_id = t.object_id
   AND u.index_id  = t.index_id
  JOIN cteReadToUpdateRatio           r
    ON u.object_id = r.object_id
   AND u.index_id  = r.index_id
  JOIN sys.indexes                    i
    ON u.object_id = i.object_id
   AND u.index_id  = i.index_id
  JOIN sys.objects                    o
    ON u.object_id = o.object_id
 WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
   AND i.is_primary_key = 0
 ORDER BY CAST(r.[Read-to-Update Ratio] AS REAL)

The results appear in "least useful" index first. Be sure to read and test this script before using in a Production environment.

What About "Under-Used" Indexes?

We've all spent time looking for unused indexes (right?), but today my boss asked about under-used indexes. Now, I wrote a script a while ago to find completely unused indexes, but finding indexes that were only being used a little was just different enough to require a new script.

The script below displays all indexes, with the least-used (including unused) ones first, and totals for user and system reads and updates. I figured having the ratio between reads and updates might be useful, since if it takes more updates to maintain the index than it gets used, you might want to investigate its cost-benefit.

Some caveats:

• If your server just rebooted, these numbers probably won't mean a lot; wait for at least a full day to allow time for the indexes to be read or written to.
• Also, don't forget about periodic events like end-of-month processing. You don't want a phone call at 12:15 am on the first of the month when it turns out that index was needed after all.
• Just because it costs more to maintain an index doesn't mean it's not still worth having. The cost of maintaining the index is probably spread out over a long period of time; the benefit to a waiting user of a query that takes two seconds instead of five minutes to run might outweigh that cost. It depends on your particular environment.
• Note that indexes that support primary key constraints are excluded, as I'm pretty sure I'm not going to ever want to drop them!

Finally, consider disabling any under-used indexes you find, rather than just dropping them. If it turns out the index was important, you'll still have to rebuild the index, but at least the DDL code will be there already.

-- Display unused and under-used indexes. 

DECLARE @dbid INT = DB_ID(DB_NAME())

; WITH cteUser AS
(
    SELECT object_id,
           index_id,
           user_seeks + user_scans + user_lookups   AS 'User Reads',
           user_updates                             AS 'User Updates'
      FROM sys.dm_db_index_usage_stats   
     WHERE database_id = @dbid
)
,
cteSystem AS
(
    SELECT object_id,
           index_id,
           system_seeks + system_scans + system_lookups   AS 'System Reads',
           system_updates                                 AS 'System Updates'
      FROM sys.dm_db_index_usage_stats   
     WHERE database_id = @dbid
)
,
cteTotal AS
(
    SELECT u.object_id,
           u.index_id,
           [User Reads]   + [System Reads]     AS 'Total Reads',
           [User Updates] + [System Updates]   AS 'Total Updates'
      FROM cteUser     u
      JOIN cteSystem   s
        ON u.object_id = s.object_id
       AND u.index_id  = s.index_id
)
,
cteReadToUpdateRatio AS
(
    SELECT object_id,
           index_id,
           CONVERT(NVARCHAR,
                  CONVERT(MONEY, ISNULL(
                                        CAST([Total Reads] AS REAL)
                                        /
                                        NULLIF([Total Updates], 0.0)
                                        , [Total Reads]
                                        )
                               , 1
                         )
                  )   AS 'Read-to-Update Ratio'
                  
      FROM cteTotal
)
SELECT OBJECT_NAME(i.object_id)               AS 'Table Name',
       i.name                                 AS 'Index Name',
       u.[User Reads],
       u.[User Updates],
       s.[System Reads],
       s.[System Updates],
       t.[Total Reads],
       t.[Total Updates],
       r.[Read-to-Update Ratio],
       t.[Total Reads] + t.[Total Updates]    AS 'TOTAL READS + UPDATES'
  FROM cteUser                        u
  JOIN cteSystem                      s
    ON u.object_id = s.object_id
   AND u.index_id  = s.index_id
  JOIN cteTotal                       t
    ON u.object_id = t.object_id
   AND u.index_id  = t.index_id
  JOIN cteReadToUpdateRatio           r
    ON u.object_id = r.object_id
   AND u.index_id  = r.index_id
  JOIN sys.indexes                    i
    ON u.object_id = i.object_id
   AND u.index_id  = i.index_id
  JOIN sys.objects                    o
    ON u.object_id = o.object_id
 WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
   AND i.is_primary_key = 0
   --AND (t.[Total Reads] = 0                                  -- These definitely need to be dropped. 
   -- OR CAST(r.[Read-to-Update Ratio] AS REAL) < 1.0)         -- These may need to be dropped. 
 ORDER BY CAST(r.[Read-to-Update Ratio] AS REAL)