SQL 索引管理器 – SQL Server 和 Azure 索引维护的免费 GUI 工具
4.99/5 (148投票s)
SQL Server 和 Azure 的开源索引维护工具
- 下载 SQL Index Manager v1.0.0.69 (最新)
- 下载源代码 - 16.3 MB
- 下载 SQL Index Manager v1.0.0.68 - 16.2 MB
- 下载 SQL Index Manager v1.0.0.67 - 16.2 MB
引言
我担任 SQL Server DBA 超过 8 年，负责管理和优化服务器性能。在我的业余时间，我想为宇宙和我的同事做一些有用的事情。这就是我们最终为 SQL Server 和 Azure 推出一款免费的索引维护工具的过程。
想法
人们在追求自己的优先事项时，有时会像充电电池一样，动力只持续一瞬间，然后一切就烟消云散了。直到最近，我也没能摆脱这种生活观察。我经常被创造自己事物的想法困扰，但优先级从一个转移到另一个，没有一件事完成。
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 (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 开发的类似产品 dbForge Index Manager for SQL Server (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 中统计信息的完整维护支持（**已完成**）。
市场上现在有很多付费解决方案。我希望相信，由于其免费的定位、更优化的查询以及各种有用的小工具，这款产品对某些人来说肯定会在日常任务中派上用场。
可以在GitHub上下载该应用程序的最新版本。源代码也在同一个地方。
我期待任何反馈。
谢谢阅读！ :)
历史
- 2019年7月15日：初版
- 2019 年 7 月 27 日：1.0.0.46 - 显示重复和未使用的索引
- 2019 年 8 月 3 日：1.0.0.47 - 修复错误和扫描引擎中的新功能
- 2019 年 8 月 31 日：1.0.0.51 - 统计信息维护、新选项和错误修复
- 2019 年 9 月 9 日：1.0.0.52 - 修复与 Azure 相关的问题
- 2019 年 11 月 3 日：1.0.0.53 - 扫描 Azure SQL Database 时无法插入重复键
- 2019 年 12 月 7 日：1.0.0.54 - GUI 小改进
- 2019 年 12 月 15 日：1.0.0.55 - 自动单元格过滤器和 GUI 多项改进
- 2019 年 12 月 29 日：1.0.0.56 - 显示所有索引，更改多行修复操作，新选项，查询取消改进
- 2020 年 1 月 7 日：1.0.0.57 - 实时统计信息，重构和错误修复
- 2020 年 1 月 21 日：1.0.0.58 - 数据库对话框改进，显示驱动器磁盘空间，UI 错误修复
- 2020 年 2 月 2 日：1.0.0.59 - 异步刷新数据库，脚本生成错误修复和早期查询取消
- 2020 年 2 月 13 日：1.0.0.60 - 列存储处理期间的错误修复
- 2020 年 3 月 29 日：1.0.0.61 - 新增索引使用统计列和 UI 改进
- 2020 年 5 月 11 日：1.0.0.62 - 列存储过滤期间的错误修复，UI 和未使用索引功能改进
- 2020 年 5 月 31 日：1.0.0.63 - 将堆迁移到聚集 CCL 索引，删除手动刷新数据库列表时的闪烁，新选项：“忽略带压缩的堆”，“忽略行数少于 1000 的表”，UI 错误修复和其他小型改进
- 2020 年 6 月 7 日：1.0.0.64 - 删除未使用的 DevExpress 库，删除导出功能，资源优化，UI 小改进
- 2020 年 6 月 29 日：1.0.0.65 - 滚动到维护索引，排序改进
- 2021 年 4 月 27 日：1.0.0.66 - 修复了在索引维护期间忽略
NO_RECOMPUTE选项的问题，添加了新列“不重新计算”和“统计信息采样”，以本地时区显示日期时间列，可通过弹出窗口排除网格行 - 2021 年 6 月 27 日：1.0.0.67 - 修复了 '
STATISTICS_NORECOMPUTE' 不是有效的ALTER INDEX REBUILD PARTITION选项的问题，修复了当任何数据库元数据损坏时数据库列表为空的问题，网格上下文菜单改进，增加了截断表的功能，改进了自动滚动，删除了延迟功能 - 2021 年 6 月 28 日：1.0.0.68 - 状态栏附加工具提示，
ErrorBox更改 - 2021 年 11 月 8 日：1.0.0.69 - 通过选项忽略已更新的统计信息的
UPDATE STATISTICS，在维护期间更新StatsSampled/RowsSampled列，列存储索引的截断表/分区语句生成期间的错误修复，服务器 ComboBox 控件搜索改进，删除保存密码功能