Малоиспользуемые индексы в группах доступности – Часть 1

Автор: Aaron Bertrand , Managing Underused Indexes in SQL Server Availability Groups – Part 1
В рамках оптимизации производительности я оцениваю использование индексов на множестве экземпляров и в разных базах данных. Часто обнаруживается, что некоторые индексы используются нечасто или, по крайней мере на первый взгляд, кажутся неиспользуемыми. Поскольку мы используем группы доступности (AG), разные рабочие нагрузки выполняются на репликах в разных ролях. Все операции записи, разумеется, происходят на первичной. Однако некоторые запросы выполняются только на вторичных репликах для чтения (либо за счёт маршрутизации чтения, либо потому, что отдельные процессы вручную направляются на конкретные вторичные, либо же и то и другое). К сожалению, статистика использования индексов нигде не агрегируется по всем репликам сразу. Это значит, что анализ только первичной реплики даёт неполную картину. Как убедиться, что я учитываю активность индексов везде, а не только на первичной?

Решение
Я бы с удовольствием имел простую хранимую процедуру, агрегирующую данные об использовании индексов по первичной и всем вторичным репликам. В идеале я передаю имя базы и имя таблицы, а процедура определяет набор реплик, собирает сведения об использовании индексов со всех них и выдаёт отчёт по результатам.

Старый подход

Ещё совсем недавно я делал это так:

• Ручным подключением к первичной и получением вывода из sys.dm_db_index_usage_stats;
• Вставкой этого первого набора в электронную таблицу;
• Ручным сбором того же вывода с каждой реплики;
• Вставкой результатов рядом с уже имеющимися данными (и, скорее всего, «подгонкой» строк, потому что не все индексы всегда возвращают строки на всех репликах);
• И ручным сравнением чтений и записей.

Единственное преимущество: когда список уже есть, я могу субъективно убирать строки из таблицы без необходимости корректировать текст запроса для фильтрации. Но кроме этого? Мука. Поэтому я взялся ускорить процесс и сделать его не таким трудоёмким.

По сути, запрос, который я выполнял вручную, выглядит так:

USE [Database I'm after];
DECLARE @TableName sysname = N'dbo.BasketWeavingStats';
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); /* not touching the PK */';
 
EXEC sys.sp_executesql @sql, N'@TableName sysname', @TableName;

Далее нам нужно поместить результаты запроса с каждой реплики в единую временную таблицу #temp.

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

Это можно сделать вручную так:

INSERT #IndexUsage 
  EXEC [EachReplica].[Database I'm after].sys.sp_executesql @sql, 
       N'@TableName sysname', 
         @TableName;

Подход с табличной функцией

Это всё равно слишком много шагов и чревато ошибками. Чтобы выполнить запрос везде, создадим встроенную табличную функцию (на одной или на всех репликах), возвращающую все прочие реплики для заданной базы данных. Она понадобится для выборки данных с остальных узлов AG. Предполагается, что для них настроены связанные серверы (если нет — можно сделать подобное через PowerShell или иным способом).

CREATE OR ALTER FUNCTION dbo.GetOtherReplicas
(
  @DatabaseName sysname
/* 
   What are all the non-local replicas for a given database?
   Are they all linked servers? 
   Notes: 
     - Linked servers may use FQDN/aliases (replica names should not)
     - @@SERVERNAME is unreliable (e.g. if a server is renamed)
     - named instances may also complicate things
*/
)
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
           /* or ON s.name = a.replica_server_name + N'.your.FQDN.domain.suffix' */
     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')))
       )
  );

Затем я могу вставить данные во временную таблицу для локального экземпляра, а потом пройтись по остальным репликам, добавив префикс связанного сервера.

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;
IF EXISTS (SELECT 1 FROM #servers WHERE ServerName IS NULL)
BEGIN
  /* 
      missing linked server
      you can decide here if you want to fix that or collect data without it 
  */
  RAISERROR(N'At least one replica is not linked.', 11, 1) WITH NOWAIT;
  /* RETURN; */
END
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); /* not touching the PK */';
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

Так я получаю все сведения об использовании индексов и могу выполнять дальнейший анализ.

Пример оценки индексов

Например, следующий запрос покажет суммарные чтения и записи, а также момент последнего чтения индекса по всем репликам:

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;

Записи всегда происходят на первичной, поэтому источник записей менее важен. Чтения же могут идти с любой реплики.

Определение чтений по репликам

Можно использовать динамическую условную агрегацию, чтобы показать, какая реплика выполняла конкретные чтения.

DECLARE @OutputColumns nvarchar(max), 
        @sql nvarchar(max);
 
SELECT @OutputColumns = STRING_AGG
  (
    CONVERT(nvarchar(max), 
    CONCAT
    (
      QUOTENAME(N'Reads_' + ServerName), 
      N' = MAX(CASE ServerName WHEN N''' + ServerName + ''' THEN Reads END),', 
      QUOTENAME(N'LastRead_' + ServerName), 
      N' = MAX(CASE ServerName WHEN N''' + ServerName + ''' THEN LastRead END)'
    )), 
    N','
  )
  FROM
  (
    SELECT ServerName FROM #IndexUsage GROUP BY ServerName
  ) AS s;
  SELECT @sql = N'SELECT IndexName, ' + @OutputColumns + ', Writes = MAX(Writes)
    FROM 
    (
      SELECT IndexName, ServerName,
             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, ServerName
    ) AS x GROUP BY IndexName;';
PRINT @sql;
EXEC sys.sp_executesql @sql;

Это порождает запрос примерно следующего вида:

SELECT IndexName, 
       [Reads_SrvA]    = MAX(CASE ServerName WHEN N'SrvA' THEN Reads END),
       [LastRead_SrvA] = MAX(CASE ServerName WHEN N'SrvA' THEN LastRead END) ,
       [Reads_SrvB]    = MAX(CASE ServerName WHEN N'SrvB' THEN Reads END),
       [LastRead_SrvB] = MAX(CASE ServerName WHEN N'SrvB' THEN LastRead END) ,
       [Reads_SrvC]    = MAX(CASE ServerName WHEN N'SrvC' THEN Reads END),
       [LastRead_SrvC] = MAX(CASE ServerName WHEN N'SrvC' THEN LastRead END),
       Writes = MAX(Writes)
  FROM 
  (
    SELECT IndexName, ServerName,
           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, ServerName
  ) AS x 
  GROUP BY IndexName;

Выполнив этот запрос, можно увидеть, что некоторые индексы едва используются — в зависимости от сервера.

Теперь у нас есть дополнительные сведения о том, как эти индексы используются на всех узлах группы доступности, а не только на той реплике, к которой мы сейчас подключены.
(Я мог бы также учитывать, какая реплика в текущий момент является первичной, но цель — убедиться, что мы собираем чтения отовсюду, а не только с текущей реплики. Поэтому, несмотря на доступность сведений по отдельным репликам, первый агрегирующий запрос для меня намного полезнее. Это может измениться в будущем, если появится возможность создавать индексы только на вторичной или индексы, которые не реплицируются на вторичные.)

В следующей статье я расскажу, как добавить дополнительные данные — например, размер и столбцы индексов — чтобы принимать ещё более взвешенные решения.