Managing Underused Indexes in SQL Server Availability Groups – Part 2

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?

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:

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *