SQL Index Manager — бесплатный тул для дефрагментации и обслуживания индексов

    Много лет работая SQL Server DBA и занимаясь то администрированием серверов, то оптимизацией производительности. В общем, захотелось в свободное время сделать что-то полезное для Вселенной и коллег по цеху. Так в итоге получился небольшой опенсорс тул по обслуживанию индексов для SQL Server и Azure.

    SQL Index Manager

    Идея


    Порой люди при работе над своими приоритетами могут напоминать пальчиковую батарейку — мотивационного заряда хватает лишь на одну вспышку, и потом все. И до недавнего времени я не был исключением из этого жизненного наблюдения. Часто меня посещали идеи создать что-то свое, но менялись приоритеты и ничего не доводилось до конца.

    Достаточно сильное влияние на мою мотивацию и профессиональное развитие оказала работа в харьковской компании Devart, которая занималась созданием софта для разработки и администрирования баз данных SQL Server, MySQL и Oracle.

    До того как прийти к ним, я мало представлял себе специфику создания собственного продукта, но уже в процессе работы почерпнул много знаний о внутреннем устройстве SQL Server. Больше года занимаясь оптимизацией запросов к метаданным в их продуктовых линейках, я постепенно стал понимать, какой функционал больше другого востребован на рынке.

    На определенном этапе возникла идея сделать новый нишевый продукт, но в силу обстоятельств эта задумка не взлетела. На тот момент для нового проекта банально не нашлось достаточного количества свободных ресурсов внутри компании без ущерба для основной деятельности.

    Уже когда работал на новом месте и пытался делать проект своими силами, приходилось постоянно идти на какие-то компромиссы. Первоначальная задумка сделать большой и напичканный фичами продукт быстро сошла на нет и постепенно трансформировалась в иное русло — разбить запланированный функционал на отдельные мини-тулы и реализовывать их независимо друг от друга.

    В итоге так на свет появился SQL Index Manager — бесплатный тул по обслуживанию индексов для SQL Server и Azure. Главной идеей было взять за основу коммерческие альтернативы от компаний RedGate и Devart и постараться улучшить их функционал. Предоставить, как для начинающих, так и опытных пользователей, возможность удобно анализировать и обслуживать индексы.

    Реализация


    На словах все всегда звучит просто… такой взял посмотрел пару мотивирующих видосиков, встал в стоечку и начал делать крутой продукт. Но на практике не все так радужно, поскольку существует много подводных камней при работе с системной табличной функцией sys.dm_db_index_physical_stats и по совместительству единственным местом, откуда можно получить актуальную информацию о фрагментации индексов.

    С первых дней разработки была отличная возможность проложить себе тоскливый путь среди стандартных схем и скопировать уже отлаженную логику работы приложений-конкурентов, добавив при этом немного отсебятины. Но после анализа запросов к метаданным захотелось сделать что-то более оптимизированное, что в силу бюрократии больших компаний никогда бы не появилось в их продуктах.

    При анализе RedGate SQL Index Manager (1.1.9.1378 — 155$) можно увидеть, что приложение использует весьма простой подход: одним запросом получаем список пользовательских таблиц и представлений, а после вторым запросом возвращается список всех индексов в рамках выбранной базы данных.

    SELECT objects.name AS tableOrViewName
         , objects.object_id AS tableOrViewId
         , schemas.name AS schemaName
         , CAST(ISNULL(lobs.NumLobs, 0) AS BIT) AS ContainsLobs
         , o.is_memory_optimized
    FROM sys.objects AS objects
    JOIN sys.schemas AS schemas ON schemas.schema_id = objects.schema_id
    LEFT JOIN (
        SELECT object_id
             , COUNT(*) AS NumLobs
        FROM sys.columns WITH (NOLOCK)
        WHERE system_type_id IN (34, 35, 99)
            OR max_length = -1
        GROUP BY object_id
    ) AS lobs ON objects.object_id = lobs.object_id
    LEFT JOIN sys.tables AS o ON o.object_id = objects.object_id
    WHERE objects.type = 'U'
        OR objects.type = 'V'
    
    SELECT i.object_id AS tableOrViewId
         , i.name AS indexName
         , i.index_id AS indexId
         , i.allow_page_locks AS allowPageLocks
         , p.partition_number AS partitionNumber
         , CAST((c.numPartitions - 1) AS BIT) AS belongsToPartitionedIndex
    FROM sys.indexes AS i
    JOIN sys.partitions AS p ON p.index_id = i.index_id
                            AND p.object_id = i.object_id
    JOIN (
        SELECT COUNT(*) AS numPartitions
             , object_id
             , index_id
        FROM sys.partitions
        GROUP BY object_id
               , index_id
    ) AS c ON c.index_id = i.index_id
          AND c.object_id = i.object_id
    WHERE i.index_id > 0 -- ignore heaps
        AND i.is_disabled = 0
        AND i.is_hypothetical = 0

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

    EXEC sp_executesql N'
    SELECT index_id, avg_fragmentation_in_percent, page_count
    FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)'
        , N'@databaseId int,@objectId int,@indexId int,@partitionNr int'
        , @databaseId = 7, @objectId = 2133582639, @indexId = 1, @partitionNr = 1
    
    EXEC sp_executesql N'
    SELECT index_id, avg_fragmentation_in_percent, page_count
    FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)'
        , N'@databaseId int,@objectId int,@indexId int,@partitionNr int'
        , @databaseId = 7, @objectId = 2133582639, @indexId = 2, @partitionNr = 1
    
    EXEC sp_executesql N'
    SELECT index_id, avg_fragmentation_in_percent, page_count
    FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)'
        , N'@databaseId int,@objectId int,@indexId int,@partitionNr int'
        , @databaseId = 7, @objectId = 2133582639, @indexId = 3, @partitionNr = 1

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

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

    В отличие от RedGate, аналогичный продукт разработанный в Devart — dbForge Index Manager for SQL Server (1.10.38 — 99$) получает информацию одним большим запросом и затем отображает все на клиенте:

    SELECT SCHEMA_NAME(o.[schema_id]) AS [schema_name]
         , o.name AS parent_name
         , o.[type] AS parent_type
         , i.name
         , i.type_desc
         , s.avg_fragmentation_in_percent
         , s.page_count
         , p.partition_number
         , p.[rows]
         , ISNULL(lob.is_lob_legacy, 0) AS is_lob_legacy
         , ISNULL(lob.is_lob, 0) AS is_lob
         , CASE WHEN ds.[type] = 'PS' THEN 1 ELSE 0 END AS is_partitioned
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
    JOIN sys.partitions p ON s.[object_id] = p.[object_id]
                         AND s.index_id = p.index_id
                         AND s.partition_number = p.partition_number
    JOIN sys.indexes i ON i.[object_id] = s.[object_id]
                      AND i.index_id = s.index_id
    LEFT JOIN (
        SELECT c.[object_id]
             , index_id = ISNULL(i.index_id, 1)
             , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END)
             , is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END)
        FROM sys.columns c
        LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id]
                                     AND c.column_id = i.column_id
                                     AND i.index_id > 0
        WHERE c.system_type_id IN (34, 35, 99)
            OR c.max_length = -1
        GROUP BY c.[object_id], i.index_id
    ) lob ON lob.[object_id] = i.[object_id]
         AND lob.index_id = i.index_id
    JOIN sys.objects o ON o.[object_id] = i.[object_id]
    JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
    WHERE i.[type] IN (1, 2)
        AND i.is_disabled = 0
        AND i.is_hypothetical = 0
        AND s.index_level = 0
        AND s.alloc_unit_type_desc = 'IN_ROW_DATA'
        AND o.[type] IN ('U', 'V')

    От основной проблемы с пеленой однотипных запросов в конкурирующем продукте удалось избавиться, но недостатки данной реализации в том, что в функцию sys.dm_db_index_physical_stats не передается никаких дополнительных параметров, которые могут ограничить сканирование заведомо ненужных индексов. По факту это приводит к получению информации по всем индексам в системе и лишним дисковым нагрузкам на этапе сканирования.

    Важно отметить, что данные, получаемые из sys.dm_db_index_physical_stats, не кешируется на постоянной основе в buffer pool, поэтому минимизирование физических чтений при получении информации о фрагментации индексов было одной из приоритетных задач при разработке.

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

    INSERT INTO #AllocationUnits (ContainerID, ReservedPages, UsedPages)
    SELECT [container_id]
         , SUM([total_pages])
         , SUM([used_pages])
    FROM sys.allocation_units WITH(NOLOCK)
    GROUP BY [container_id]
    HAVING SUM([total_pages]) BETWEEN @MinIndexSize AND @MaxIndexSize

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

    SELECT [object_id]
         , [index_id]
         , [partition_id]
         , [partition_number]
         , [rows]
         , [data_compression]
    INTO #Partitions
    FROM sys.partitions WITH(NOLOCK)
    WHERE [object_id] > 255
        AND [rows] > 0
        AND [object_id] NOT IN (SELECT * FROM #ExcludeList)

    В зависимости от настроек получаются только те типы индексов, которые пользователь хочет проанализировать (поддерживается работа с кучами, кластерными/некластерными индексами и колумнсторами).

    INSERT INTO #Indexes
    SELECT ObjectID         = i.[object_id]
         , IndexID          = i.index_id
         , IndexName        = i.[name]
         , PagesCount       = a.ReservedPages
         , UnusedPagesCount = a.ReservedPages - a.UsedPages
         , PartitionNumber  = p.[partition_number]
         , RowsCount        = ISNULL(p.[rows], 0)
         , IndexType        = i.[type]
         , IsAllowPageLocks = i.[allow_page_locks]
         , DataSpaceID      = i.[data_space_id]
         , DataCompression  = p.[data_compression]
         , IsUnique         = i.[is_unique]
         , IsPK             = i.[is_primary_key]
         , FillFactorValue  = i.[fill_factor]
         , IsFiltered       = i.[has_filter]
    FROM #AllocationUnits a
    JOIN #Partitions p ON a.ContainerID = p.[partition_id]
    JOIN sys.indexes i WITH(NOLOCK) ON i.[object_id] = p.[object_id]
                                   AND p.[index_id] = i.[index_id] 
    WHERE i.[type] IN (0, 1, 2, 5, 6)
        AND i.[object_id] > 255

    После этого начинается небольшая магия: для всех маленьких индексов мы определяем уровень фрагментации за счет многократного вызова функции sys.dm_db_index_physical_stats с полным указанием всех параметров.

    INSERT INTO #Fragmentation (ObjectID, IndexID, PartitionNumber, Fragmentation)
    SELECT i.ObjectID
         , i.IndexID
         , i.PartitionNumber
         , r.[avg_fragmentation_in_percent]
    FROM #Indexes i
    CROSS APPLY sys.dm_db_index_physical_stats(@DBID, i.ObjectID, i.IndexID, i.PartitionNumber, 'LIMITED') r
    WHERE i.PagesCount <= @PreDescribeSize
        AND r.[index_level] = 0
        AND r.[alloc_unit_type_desc] = 'IN_ROW_DATA'
        AND i.IndexType IN (0, 1, 2)

    Далее мы возвращаем всю возможную информацию на клиент, отфильтровывая лишние данные:

    SELECT i.ObjectID
         , i.IndexID
         , i.IndexName
         , ObjectName       = o.[name]
         , SchemaName       = s.[name]
         , i.PagesCount
         , i.UnusedPagesCount
         , i.PartitionNumber
         , i.RowsCount
         , i.IndexType
         , i.IsAllowPageLocks
         , u.TotalWrites
         , u.TotalReads
         , u.TotalSeeks
         , u.TotalScans
         , u.TotalLookups
         , u.LastUsage
         , i.DataCompression
         , f.Fragmentation
         , IndexStats       = STATS_DATE(i.ObjectID, i.IndexID)
         , IsLobLegacy      = ISNULL(lob.IsLobLegacy, 0)
         , IsLob            = ISNULL(lob.IsLob, 0)
         , IsSparse         = CAST(CASE WHEN p.ObjectID IS NULL THEN 0 ELSE 1 END AS BIT)
         , IsPartitioned    = CAST(CASE WHEN dds.[data_space_id] IS NOT NULL THEN 1 ELSE 0 END AS BIT)
         , FileGroupName    = fg.[name]
         , i.IsUnique
         , i.IsPK
         , i.FillFactorValue
         , i.IsFiltered
         , a.IndexColumns
         , a.IncludedColumns
    FROM #Indexes i
    JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = i.ObjectID
    JOIN sys.schemas s WITH(NOLOCK) ON s.[schema_id] = o.[schema_id]
    LEFT JOIN #AggColumns a ON a.ObjectID = i.ObjectID
                           AND a.IndexID = i.IndexID
    LEFT JOIN #Sparse p ON p.ObjectID = i.ObjectID
    LEFT JOIN #Fragmentation f ON f.ObjectID = i.ObjectID
                              AND f.IndexID = i.IndexID
                              AND f.PartitionNumber = i.PartitionNumber
    LEFT JOIN (
        SELECT ObjectID      = [object_id]
             , IndexID       = [index_id]
             , TotalWrites   = NULLIF([user_updates], 0)
             , TotalReads    = NULLIF([user_seeks] + [user_scans] + [user_lookups], 0)
             , TotalSeeks    = NULLIF([user_seeks], 0)
             , TotalScans    = NULLIF([user_scans], 0)
             , TotalLookups  = NULLIF([user_lookups], 0)
             , LastUsage     = (
                                    SELECT MAX(dt)
                                    FROM (
                                        VALUES ([last_user_seek])
                                             , ([last_user_scan])
                                             , ([last_user_lookup])
                                             , ([last_user_update])
                                    ) t(dt)
                               )
        FROM sys.dm_db_index_usage_stats WITH(NOLOCK)
        WHERE [database_id] = @DBID
    ) u ON i.ObjectID = u.ObjectID
       AND i.IndexID = u.IndexID
    LEFT JOIN #Lob lob ON lob.ObjectID = i.ObjectID
                      AND lob.IndexID = i.IndexID
    LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id]
                                                          AND i.PartitionNumber = dds.[destination_id]
    JOIN sys.filegroups fg WITH(NOLOCK) ON ISNULL(dds.[data_space_id], i.DataSpaceID) = fg.[data_space_id] 
    WHERE o.[type] IN ('V', 'U')
        AND (
                f.Fragmentation >= @Fragmentation
            OR
                i.PagesCount > @PreDescribeSize
            OR
                i.IndexType IN (5, 6)
        )

    После этого точечными запросами определяем уровень фрагментации для больших индексов.

    EXEC sp_executesql N'
    DECLARE @DBID INT = DB_ID()
    SELECT [avg_fragmentation_in_percent]
    FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'')
    WHERE [index_level] = 0
        AND [alloc_unit_type_desc] = ''IN_ROW_DATA'''
        , N'@ObjectID int,@IndexID int,@PartitionNumber int'
        , @ObjectId = 1044198770, @IndexId = 1, @PartitionNumber = 1
    
    EXEC sp_executesql N'
    DECLARE @DBID INT = DB_ID()
    SELECT [avg_fragmentation_in_percent]
    FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'')
    WHERE [index_level] = 0
        AND [alloc_unit_type_desc] = ''IN_ROW_DATA'''
        , N'@ObjectID int,@IndexID int,@PartitionNumber int'
        , @ObjectId = 1552724584, @IndexId = 0, @PartitionNumber = 1

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

    Вначале была реализована поддержка работы с WAIT_AT_LOW_PRIORITY, потом стало возможным использовать DATA_COMPRESSION и FILL_FACTOR при ребилде индексов.

    SQL Index Manager Settings

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

    SELECT *
    FROM (
        SELECT IndexID          = [index_id]
             , PartitionNumber  = [partition_number]
             , PagesCount       = SUM([size_in_bytes]) / 8192
             , UnusedPagesCount = ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0) / 8192
             , Fragmentation    = CAST(ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0)
                                * 100. / SUM([size_in_bytes]) AS FLOAT)
        FROM sys.fn_column_store_row_groups(@ObjectID)
        GROUP BY [index_id]
               , [partition_number]
    ) t
    WHERE Fragmentation >= @Fragmentation
        AND PagesCount BETWEEN @MinIndexSize AND @MaxIndexSize

    Или возможности создавать некластерные индексы на основе информации из dm_db_missing_index:

    SELECT ObjectID     = d.[object_id]
         , UserImpact   = gs.[avg_user_impact]
         , TotalReads   = gs.[user_seeks] + gs.[user_scans]
         , TotalSeeks   = gs.[user_seeks]
         , TotalScans   = gs.[user_scans]
         , LastUsage    = ISNULL(gs.[last_user_scan], gs.[last_user_seek])
         , IndexColumns =
                    CASE
                        WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NOT NULL
                            THEN d.[equality_columns] + ', ' + d.[inequality_columns]
                        WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NULL
                            THEN d.[equality_columns]
                        ELSE d.[inequality_columns]
                    END
         , IncludedColumns = d.[included_columns]
    FROM sys.dm_db_missing_index_groups g WITH(NOLOCK)
    JOIN sys.dm_db_missing_index_group_stats gs WITH(NOLOCK) ON gs.[group_handle] = g.[index_group_handle]
    JOIN sys.dm_db_missing_index_details d WITH(NOLOCK) ON g.[index_handle] = d.[index_handle]
    WHERE d.[database_id] = DB_ID()

    Итоги


    После шести месяцев активной фазы разработки радует, что на этом планы не заканчиваются, поскольку хочется и дальше развивать этот продукт. На очереди планируется добавить функционал по поиску дублирующих или неиспользуемых индексов, а также реализовать полноценную поддержку по обслуживанию статистики в рамках SQL Server.

    Исходя из того, что на рынке сейчас много платных решений, хочется верить, что за счет бесплатного позиционирования, более оптимизированного дескрайба метаданных и наличия разного рода полезных мелочей для кого-то этот продукт точно станет полезным в повседневных задачах.

    Актуальную версию приложения можно скачать на GitHub. Исходники лежат там же.
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 8

      0
      Спасибо! Искал такой инструмент, тестирую. Интерфейс немного подтормаживает.
        0
        Открыл базу, сказало «No Indexes Found». Индексы точно есть. Что я делаю не так?
          0

          Что в файле *.log? Ситуаций может быть тьма. Как вариант все индексы не требуют обслуживания. Или слишком большие, что не включаются в общий список при анализе. Может быть бажина какая-то в запросе. Если покажете лог в личке, то смогу точно сказать. Ну и как вариант посмотрите на настройки свои.

          0
          Отличный тул, спасибо большое!
            0
            Комментарии и вопросы, но нисколько не придирки.
            При ребилде индексов активно пишется лог, и если свободного место на диске не хватит, то потом ждет долгий rollback. Если уж утилита предназначена для «чайников», то неплохо бы просчитать сколько свободного места в логе нужно для ребилда и предупредить о последствиях. В случае с ONLINE еще + место на диске размером с индекс в базе, т.к. индекс создается практически «как новый», но оригинал еще действует и будет удален после ребилда.
            И на картинке есть выбор режима дефрагментации: отдельно ROW, PAGE и ONLINE, а разве не может быть ONLINE+PAGE например?
            Опция ONLINE учитывает редакцию SQL или всегда есть в меню, т.к. применима не на всех редакциях?
            Возможна ли дефрагментация отдельно выбранной секции секционированного индекса?
              0
              Возможна ли дефрагментация отдельно выбранной секции секционированного индекса?

              Да. Изначально так и работает.

              Опция ONLINE учитывает редакцию SQL или всегда есть в меню, т.к. применима не на всех редакциях?

              Учитывает редакцию сиквела. И если есть возможность только тогда используется (при условии что в настройках включена).

              И на картинке есть выбор режима дефрагментации: отдельно ROW, PAGE и ONLINE, а разве не может быть ONLINE+PAGE например?

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

                0
                Обязательно потестю! Сам для себя тоже делаю разные утилитки для скуля для повседневного использования, но причесать красиво руки не доходят, да в работе это и без надобности )))
              0
              Весьма полезная утилитка. Спасибо.

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое