9.10.25

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

Автор: Aaron Bertrand , Managing Underused Indexes in SQL Server Availability Groups – Part 2

В первой части статьи «Малоиспользуемые индексы в группах доступности – Часть 1» я показал, как с помощью динамического SQL собрать статистику использования индексов для заданной таблицы со всех реплик в группе доступности. Знать использование по всем нагрузкам, безусловно, лучше, чем смотреть только на первичную или на одну вторичную. Но что если я хочу принимать ещё более взвешенные решения, добавив к выводу количество строк, размер и столбцы индексов?

В первой части использовалась таблица следующего вида:

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

Мы заполняли её при помощи этой функции:

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')))
       )
  );

И затем — таким кодом:

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

Добавляем дополнительную информацию об индексах

Имея заполненную таблицу, теперь можно добавить больше контекста. Наряду со статистикой использования я всегда смотрю на следующее:

  • Сколько строк в фильтрованном индексе — помогает понять, стоит ли его поддерживать: это 99% таблицы, 1% или что‑то между?
  • Сколько места занимает каждый индекс — если индекс велик и при этом почти не читается, возможно, стоит отдать ему приоритет на удаление и/или тщательнее изучить потенциальное использование, опережая более мелкие индексы.
  • Какие столбцы входят в ключ индекса и в INCLUDE, и в каком порядке идут столбцы ключа — часто я разбираю конкретные запросы (и их изменения) и хочу быстро понять, покрывает ли данный индекс — или почти покрывает — запрос или паттерн запросов.
  • Каково определение фильтра — фильтрованные индексы часто создаются под очень конкретный запрос, и явный список условий упрощает поиск индексов — кандидатов на консолидацию.

Для этого можно локально запросить sys.indexes, sys.dm_db_partition_stats, sys.index_columns и sys.columns. Нет нужды обращаться удалённо, даже на вторичной реплике, поскольку эти сведения из каталога синхронны на всех репликах. Исключения — разве что неудачная конкуренция по времени или приостановленное перемещение данных.

Тем не менее динамический SQL всё равно понадобится, поскольку в итоге я хочу оформить это в хранимую процедуру, принимающую имя базы данными в качестве параметра. Всё‑таки у нас порядка 400 баз...

Запрос для получения сведений об индексах

Сначала посмотрим на статический запрос, который требуется для получения информации, когда временная таблица уже есть:

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;

Это даёт примерно такой вывод (простите за условность: имена индексов и столбцов реальной таблицы здесь несущественны).

Хранимая процедура для анализа использования индексов

Теперь соберём всё воедино в одной хранимой процедуре, чтобы вызывать её было совсем просто:

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

Теперь получить тот же полезный вывод можно так:

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

Анализируем данные

Двигаясь по этому пути и начиная анализировать такой вывод, не обесценивайте индексы, которые велики и/или в которые много пишут, но мало читают. Вот несколько качественных моментов, о которых стоит помнить, когда у вас уже есть количественные данные:

  • Индекс может использоваться редко, но — очень важным человеком или критически важным с точки зрения производственных процессов или производительности запросов.
  • Индекс может пока не фигурировать в DMV для конкретной реплики.
  • Тот факт, что в ключах присутствуют одни и те же столбцы, не делает два индекса избыточными — учитывайте порядок ключевых столбцов, INCLUDE‑столбцы и фильтры.

Больше об этом я говорю в статье: «Index cleanup: Harder than it looks».

Вывод

Этот приём можно использовать для любых данных, которые нужно агрегировать по репликам. Например, подойдут DMV с «пропущенными индексами». Но в этих двух частях я сознательно сосредоточился на одной из возможных тем.




на
Ярлыки: , , , , ,

Комментариев нет:

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)