В течение многих лет я работал администратором базы данных SQL Server и занимался либо администрированием серверов, либо оптимизацией производительности.
В общем, мне хотелось в свободное время сделать что-то полезное для Вселенной и своих коллег.
В итоге получилось маленькое инструмент с открытым исходным кодом по поддержанию индексов для SQL Server и Azure. 
Идея
Иногда люди при работе над своими приоритетами могут напоминать пальчиковую батарейку – мотивационного заряда хватает только на одну вспышку, и все.И я до недавнего времени не был исключением из этого жизненного наблюдения.
У меня часто были идеи создать что-то своё, но приоритеты менялись и ничего так и не было доведено до конца.
Достаточно сильное влияние на мою мотивацию и профессиональное развитие оказала работа в харьковской компании Devart, создавшей программное обеспечение для разработки и администрирования баз данных SQL Server, MySQL и Oracle. До прихода к ним я мало имел представление о специфике создания собственного продукта, но уже в процессе работы получил много знаний о внутреннем устройстве SQL Server. Занимаясь оптимизацией запросов по метаданным в своих линейках продуктов более года, я постепенно начал понимать, какой функционал более востребован на рынке, чем другие.
На определенном этапе возникла идея сделать новый нишевый продукт, но в силу обстоятельств эта идея не получила развития.
На тот момент внутри компании просто не хватало свободных ресурсов для нового проекта без ущерба для основного бизнеса.
Даже когда я работал на новом месте и пытался сделать проект самостоятельно, мне приходилось постоянно идти на какие-то компромиссы.
Первоначальная идея сделать большой, напичканный фичами продукт быстро угасла и постепенно трансформировалась в другое направление — разбить запланированный функционал на отдельные мини-инструменты и реализовать их независимо друг от друга.
В конце концов, вот так оно и родилось Менеджер индексов SQL — бесплатный инструмент для поддержки индексов для 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 для 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, не кэшируются постоянно в пуле буферов, поэтому минимизация физического чтения при получении информации о фрагментации индекса была приоритетом разработки.
После нескольких экспериментов удалось объединить оба подхода, разделив сканирование на две части.
Сначала одним большим запросом определяется размер разделов, заранее отфильтровывая те, которые не входят в диапазон фильтрации: 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 при перестроении индексов.
Приложение постепенно приобрело ранее незапланированный функционал, например, обслуживание столбцов: 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 .
Исходники находятся там.
Теги: #открытый исходный код #.
NET #sql #azure #Microsoft SQL Server #sql-сервер #columnstore index #devart #sql-менеджер индексов #redgate #ведение индекса #ведение статистики #перестроение индекса #ola Hallengren
-
Цена Прогресса, Этические Уравнения И Фашизм
19 Oct, 24 -
Мегафон И Подарки За Лояльность.
19 Oct, 24
