目录
介绍
主意
实现
结果和计划
- 下载源 - 16.3 MB
- 下载 SQL 索引管理器 v1.0.0.68.zip - 16.2 MB
- 下载 SQL 索引管理器 v1.0.0.67.zip - 16.2 MB
我已经担任SQL Server DBA超过8年，负责管理和优化服务器的性能。在空闲时间，我想做一些对宇宙和我的同事有用的事情。这就是我们最终获得SQL Server和Azure的免费索引维护工具的方式。
主意每隔一段时间，人们在处理他们的优先事项时，就像一个手指型电池——激励性充电只持续一闪，然后一切都消失了。直到最近，我在这种生活观察中也不例外。我经常被想法所困扰，以创造自己的东西，但优先级从一个变为另一个，没有完成。
DevArt开发了用于开发和管理 SQL Server、MySQL和Oracle数据库的软件，对我的动力和职业发展产生了重大影响。
在来之前，我对创建自己的产品的具体细节知之甚少，但在这个过程中，我获得了很多关于SQL Server内部结构的知识。一年多来，在他们的产品线中从事优化查询，我逐渐开始了解市场上哪个功能比另一个更受欢迎。
在某个阶段，做一个新的小众产品的想法出现在我面前，但由于某些情况，这个想法没有成功。那个时候，基本上没能在公司内部为一个新项目找到足够的资源而不影响核心业务。
在一个全新的地方工作并试图自己创建一个项目让我不断妥协。最初的想法是制作一个具有所有花里胡哨的大产品，但很快就停止了，并逐渐转向了不同的方向——将计划中的功能分解为单独的迷你工具，并相互独立地实施。
于是，SQL 索引管理器诞生了，一个免费的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开发的类似产品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 = @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()关键是开发计划并没有就此结束，因为我渴望进一步开发这个应用程序。下一步是添加用于查找重复(done)或未使用的索引(done)的功能，并在SQL Server中实现对维护统计信息(done)的全面支持。
现在市场上有很多付费解决方案。我想相信，由于自由定位、更优化的查询以及各种有用的gismos可供某人使用，该产品肯定会在日常任务中变得有用。
最新版本的应用程序可以在GitHub 上下载。
https://www.codeproject.com/Articles/5162340/SQL-Index-Manager-Free-GUI-Tool-for-Index-Maintena
