我作為SQL Server DBA工作了8年多，管理和優化伺服器的性能。在我的空閑時間，我想為宇宙和我的同事做一些有用的事情。這就是我們最終為SQL Server和Azure 提供免費索引維護工具的方法。 理念 每隔一段時間，人們在處理他們的優先事項時，可能就像一個手指式電池 - 一個激勵充電只持續 ...
我作為SQL Server DBA工作了8年多，管理和優化伺服器的性能。在我的空閑時間，我想為宇宙和我的同事做一些有用的事情。這就是我們最終為SQL Server和Azure 提供免費索引維護工具的方法。
理念
每隔一段時間，人們在處理他們的優先事項時，可能就像一個手指式電池 - 一個激勵充電只持續一閃，然後一切都消失了。直到最近，我在這一生活觀察中也不例外。我經常被想法創造屬於我自己的想法所困擾，但優先順序從一個變為另一個並且沒有完成任何事情。
DevArt開發用於開發和管理SQL Server，MySQL和Oracle資料庫的軟體，對我的動機和專業成長產生了很大的影響。
在他們來之前，我對創建自己的產品的具體細節知之甚少，但在此過程中，我獲得了很多關於SQL Server內部結構的知識。一年多以來，我一直致力於優化產品線中的查詢，逐漸開始瞭解市場上哪些功能比另一種功能更受歡迎。
在某個階段，製作一個新的利基產品的想法出現在我面前，但由於某些情況，這個想法沒有成功。那時，基本上我沒有為公司內部的新項目找到足夠的資源而不影響核心業務。
在一個嶄新的地方工作，並試圖自己創建一個項目讓我不斷妥協。製造一個擁有所有花里胡哨的大產品的最初想法很快就會停止並逐漸轉變為一個不同的方向 - 將計劃的功能分解為單獨的迷你工具並相互獨立地實現它們。
因此，SQL Index Manager誕生了，它是SQL Server和Azure的免費索引維護工具。主要想法是將RedGate和Devart公司的商業替代品作為基礎，並嘗試在我自己的項目中改進其功能。
履行
口頭上說，一切聽起來都很簡單......只需觀看幾個激勵視頻，打開“Rocky Balboa”模式，開始製作一款很酷的產品。但讓我們面對音樂，一切都不那麼樂觀，因為在使用系統表函數時存在許多陷阱，sys.dm_db_index_physical_stats同時，它是唯一可以從中獲取有關索引碎片的最新信息的地方。
從開發的最初幾天起，就有很好的機會在標準方案中製造沉悶的方式，並複製已經調試過的競爭應用程式的邏輯，同時添加一些自組織。但在分析了元數據的查詢後，我想做一些更優化的事情，由於大公司的官僚主義，它們永遠不會出現在他們的產品中。
在分析RedGate SQL索引管理器（v1.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
接下來，在while每個索引分區的迴圈中，發送請求以確定其大小和碎片級別。在掃描結束時，客戶端上會顯示重量小於進入閾值的索引。
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開發的類似產品 - 用於SQL Server的dbForge索引管理器（v1.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中維護統計信息的完全支持。
現在市場上有很多付費解決方案。我想相信，由於自由定位，更優化的查詢以及各種有用的gismos的可用性，這個產品肯定會在日常任務中變得有用。
