Get Free Tips
MSSQLTips.com
Home » Managing Underused Indexes in SQL Server Availability Groups – Part 2
Indexing

Managing Underused Indexes in SQL Server Availability Groups – Part 2

written by Aaron Bertrand 0 comments

Problem

In my previous tip, Managing Unused Indexes in SQL Server Availability Groups – Part 1, I showed how I use dynamic SQL to gather index usage statistics for a given table from all replicas in an availability group. Knowing the usage from all workloads is definitely better than focusing on only the primary or a single secondary. But what if I want to make more informed decisions, incorporating row counts, size, and index columns into this output?

Managing Underused Indexes in SQL Server Availability Groups
Managing Underused Indexes in SQL Server Availability Groups

Solution

The previous tip showed a table like this:

CREATE TABLE #IndexUsage
(
  ServerName   nvarchar(128),
  IndexName    nvarchar(128),
  UserSeeks    bigint,
  UserScans    bigint,
  UserLookups  bigint,
  UserUpdates  bigint,
  LastScan     datetime,
  LastSeek     datetime 
);

We populated it using this function:

CREATE OR ALTER FUNCTION dbo.GetOtherReplicas
(
  @DatabaseName sysname
)
RETURNS TABLE
AS
  RETURN
  (
    SELECT ServerName = s.name
      FROM master.sys.availability_databases_cluster AS dc
     INNER JOIN master.sys.availability_replicas AS ar 
           ON dc.group_id = ar.group_id
      LEFT OUTER JOIN sys.servers AS s 
           ON ar.replica_server_name = s.name
     WHERE dc.database_name = @DatabaseName
       AND LOWER(ar.replica_server_name) NOT IN 
       (
           LOWER(@@SERVERNAME), 
           LOWER(CONVERT(sysname, SERVERPROPERTY('ServerName'))), 
           LOWER(CONVERT(sysname, SERVERPROPERTY('ComputerNamePhysicalNetBIOS')))
       )
  );

And then this code:

DECLARE @DatabaseName sysname = N'Database I''m After',
        @TableName    sysname = N'dbo.BasketWeavingStats';
DECLARE @Counter     tinyint = 1,
        @ServerCount tinyint,
        @RemoteExec  nvarchar(384),
        @LocalExec   nvarchar(256) = QUOTENAME(@DatabaseName) 
                                   + N'.sys.sp_executesql';
DROP TABLE IF EXISTS #servers;
CREATE TABLE #servers
(
  id         int identity(1,1), 
  ServerName sysname
);
INSERT #servers(ServerName) 
SELECT ServerName 
  FROM dbo.GetOtherReplicas(@DatabaseName);
SELECT @ServerCount = @@ROWCOUNT;
DECLARE @sql nvarchar(max) = N'SELECT @@SERVERNAME,
        i.name,
        s.user_seeks,
        s.user_scans,
        s.user_lookups,
        s.user_updates,
        s.last_user_scan,
        s.last_user_seek
   FROM sys.indexes AS i
   LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s
     ON i.object_id   = s.object_id
    AND i.index_id    = s.index_id
    AND s.database_id = DB_ID()
  WHERE i.object_id   = OBJECT_ID(@TableName)
    AND i.index_id NOT IN (0,1);';
INSERT #IndexUsage EXEC @LocalExec @sql, N'@TableName sysname', @TableName;
WHILE @Counter <= @ServerCount
BEGIN
  SELECT @RemoteExec = QUOTENAME(ServerName) + N'.' + @LocalExec
    FROM #servers
   WHERE id = @Counter 
     AND ServerName IS NOT NULL;
  INSERT #IndexUsage EXEC @RemoteExec @sql, N'@TableName sysname', @TableName;
  SET @Counter += 1;
END

Adding Additional Index Information

With that table populated, now I can add more context.

Alongside usage statistics, I always review:

  • How many rows are in a filtered index – This helps to determine whether it is valuable to maintain; is it 99% of the table, 1%, or something in between?
  • How much space each index occupies – If an index is large but isn’t being read, it may be a priority to drop and/or review potential usage way ahead of smaller indexes.
  • What are the key/include columns and what order are the key columns in – Often I review specific queries (and query changes) and want a quick way to see if a given index covers – or almost covers – a query or query pattern.
  • What is the filter definition – Filtered indexes are often created to tailor-match a very specific query, and listing the explicit filter makes it easy to identify indexes that are candidates for consolidation.

For this, I can join to sys.indexes, sys.dm_db_partition_stats, sys.index_columns, and sys.columns locally. There’s no need to query remotely, even on a secondary, since these catalog details are consistent across replicas. The only exceptions are unlucky timing or suspended data movement.

I’ll still need dynamic SQL, though, since I ultimately want to put this into a stored procedure that takes the database name as a parameter. We do have some 400 databases, after all.

Query to Get Index Information

First, let’s look at a static query required to get this information when the #temp table is already:

USE [Database I'm after];
DECLARE @TableName sysname = N'dbo.BasketWeavingStats';
DECLARE @obj int = OBJECT_ID(@TableName);
WITH IndexUsage AS 
(
  SELECT IndexName, 
         Reads    = SUM(COALESCE(UserSeeks, 0) 
                    + COALESCE(UserScans, 0) 
                    + COALESCE(UserLookups, 0)), 
         Writes   = SUM(COALESCE(UserUpdates, 0)), 
         LastRead = MAX(COALESCE(LastSeek, LastScan))
    FROM #IndexUsage
   GROUP BY IndexName
),
ColumnLists AS
(
  SELECT ic.index_id, 
         KeyColumns = STRING_AGG(CASE ic.is_included_column WHEN 0 
           THEN CONCAT(c.name, CASE ic.is_descending_key WHEN 1 THEN N' (↓)' END) 
           END, ',') WITHIN GROUP (ORDER BY ic.index_column_id),
         IncludeColumns = STRING_AGG(CASE ic.is_included_column WHEN 1 THEN c.name 
           END, ',')
    FROM sys.index_columns AS ic
   INNER JOIN sys.columns AS c
      ON c.object_id = ic.object_id 
     AND c.column_id = ic.column_id
   WHERE ic.object_id = @obj
   GROUP BY ic.index_id
),
IndexInfo AS
(
  SELECT i.index_id,
         IndexName = i.name COLLATE DATABASE_DEFAULT,
         ps.SizeMB,
         ps.Rows,
         i.filter_definition
    FROM sys.indexes AS i
   CROSS APPLY
         (
           SELECT SizeMB = SUM(ps.used_page_count)/128, 
                  Rows   = SUM(row_count)
             FROM sys.dm_db_partition_stats AS ps
            WHERE ps.object_id = @obj 
              AND ps.index_id = i.index_id
         ) AS ps
   WHERE i.object_id = @obj
)
SELECT ii.IndexName, 
       iu.Reads,
       iu.LastRead,
       iu.Writes,
       ii.Rows,
       ii.SizeMB,
       TotalSizeMB = SUM(ii.SizeMB) OVER(),
       cl.KeyColumns,
       cl.IncludeColumns,
       ii.filter_definition
  FROM IndexInfo AS ii 
 INNER JOIN ColumnLists AS cl 
    ON cl.index_id = ii.index_id
 INNER JOIN IndexUsage AS iu 
    ON iu.IndexName = ii.IndexName;

This produces output like this (sorry for obscurity, but the index and column names of the actual table I’m looking at are not important):

query output

Stored Procedure for Index Usage

Now let’s put it all together in a single stored procedure so the call is really easy:

CREATE OR ALTER PROCEDURE dbo.MSSQLTips_IndexUsage
  @DatabaseName sysname = N'Database I''m after ',
  @TableName    sysname = N'dbo.BasketWeavingStats'
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Counter     tinyint = 1,
          @ServerCount tinyint,
          @RemoteExec  nvarchar(384),
          @LocalExec   nvarchar(256) = QUOTENAME(@DatabaseName) 
                                     + N'.sys.sp_executesql';
  CREATE TABLE #servers
  (
    id         int identity(1,1), 
    ServerName sysname
  );
  INSERT #servers(ServerName) 
  SELECT ServerName 
    FROM dbo.GetOtherReplicas(@DatabaseName);
  SELECT @ServerCount = @@ROWCOUNT;
  DECLARE @sql nvarchar(max) = N'SELECT @@SERVERNAME,
          i.name,
          s.user_seeks,
          s.user_scans,
          s.user_lookups,
          s.user_updates,
          s.last_user_scan,
          s.last_user_seek
     FROM sys.indexes AS i
     LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s
       ON i.object_id   = s.object_id
      AND i.index_id    = s.index_id
      AND s.database_id = DB_ID()
    WHERE i.object_id   = OBJECT_ID(@TableName)
      AND i.index_id NOT IN (0,1);';
  CREATE TABLE #IndexUsage
  (
    ServerName   nvarchar(128),
    IndexName    nvarchar(128),
    UserSeeks    bigint,
    UserScans    bigint,
    UserLookups  bigint,
    UserUpdates  bigint,
    LastScan     datetime,
    LastSeek     datetime 
  );
  INSERT #IndexUsage EXEC @LocalExec @sql, N'@TableName sysname', @TableName;
  WHILE @Counter <= @ServerCount  
  BEGIN
    SELECT @RemoteExec = QUOTENAME(ServerName) + N'.' + @LocalExec
      FROM #servers
     WHERE id = @Counter 
       AND ServerName IS NOT NULL;
    INSERT #IndexUsage EXEC @RemoteExec @sql, N'@TableName sysname', @TableName;
    SET @Counter += 1;
  END
  SET @sql = N'DECLARE @obj int = OBJECT_ID(@TableName);
  WITH IndexUsage AS   
  (
    SELECT IndexName, 
           Reads    = SUM(COALESCE(UserSeeks, 0) 
                      + COALESCE(UserScans, 0) 
                      + COALESCE(UserLookups, 0)), 
           Writes   = SUM(COALESCE(UserUpdates, 0)), 
           LastRead = MAX(COALESCE(LastSeek, LastScan))
      FROM #IndexUsage
     GROUP BY IndexName
  ),
  ColumnLists AS
  (
    SELECT ic.index_id, 
           KeyColumns = STRING_AGG(CASE ic.is_included_column WHEN 0 
             THEN CONCAT(c.name, CASE ic.is_descending_key WHEN 1 THEN N'' (↓)'' END) 
             END, '','') WITHIN GROUP (ORDER BY ic.index_column_id),
           IncludeColumns = STRING_AGG(CASE ic.is_included_column WHEN 1 THEN c.name 
             END, '','')
      FROM sys.index_columns AS ic
     INNER JOIN sys.columns AS c
        ON c.object_id = ic.object_id 
       AND c.column_id = ic.column_id
     WHERE ic.object_id = @obj
     GROUP BY ic.index_id
  ),
  IndexInfo AS
  (
    SELECT i.index_id,
           IndexName = i.name COLLATE DATABASE_DEFAULT,
           ps.SizeMB,
           ps.Rows,
           i.filter_definition
      FROM sys.indexes AS i
     CROSS APPLY
           (
             SELECT SizeMB = SUM(ps.used_page_count)/128, 
                    Rows   = SUM(row_count)
               FROM sys.dm_db_partition_stats AS ps
              WHERE ps.object_id = @obj 
                AND ps.index_id = i.index_id
           ) AS ps
     WHERE i.object_id = @obj
  )
  SELECT ii.IndexName, 
         iu.Reads,
         iu.LastRead,
         iu.Writes,
         ii.Rows,
         ii.SizeMB,
         TotalSizeMB = SUM(ii.SizeMB) OVER(),
         cl.KeyColumns,
         cl.IncludeColumns,
         ii.filter_definition
    FROM IndexInfo AS ii 
   INNER JOIN ColumnLists AS cl 
      ON cl.index_id = ii.index_id
   INNER JOIN IndexUsage AS iu 
      ON iu.IndexName = ii.IndexName;';
  EXEC @LocalExec @sql, N'@TableName sysname', @TableName;
END

Now we can get that same helpful output with:

EXEC dbo.MSSQLTips_IndexUsage 
     @DatabaseName = N'Database I''m After',
     @TableName    = N'dbo.BasketWeavingStats';

Analyzing the Data

As you go on this journey and start analyzing this type of output, don’t devalue indexes that are large and/or have many writes, but few reads. A few qualitative things to consider once you have the quantitative information:

  • An index may be infrequently used, but by a very important person or a performance-critical process.
  • An index may not be represented in the DMV for a given replica yet.
  • Just because the same columns are in each key doesn’t make two indexes redundant – think about key column order, include columns, and filters.

I discuss more about this in a short post: Index cleanup: Harder than it looks.

Conclusion

This pattern could be used for any type of data you need to aggregate across replicas. The missing indexes DMVs would be a translatable candidate, for example. But, for this tip series, I wanted to focus on only one concept.

Next Steps

Review the following tips and other resources:

Copyright (c) 2006-2025 Edgewood Solutions, LLC All rights reserved
sql server 2025 guide