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?

Solution

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

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