免费工具 SQLIndexManager 回顾

如您所知，索引在 DBMS 中发挥着重要作用，提供对所需记录的快速搜索。 这就是为什么及时为他们提供服务如此重要。 关于分析和优化的材料已经写了很多，包括在互联网上。 例如，该主题最近在 本出版物.

为此有许多付费和免费的解决方案。 例如，有一个现成的 决定，基于自适应索引优化方法。

接下来我们看看免费的实用工具 SQL索引管理器，作者： 艾伦·丹顿.

SQLIndexManager 与许多其他类似物之间的主要技术差异由作者本人给出 这里 и 这里.

在本文中，我们将从外部角度了解该项目以及该软件解决方案的运营功能。

讨论这个实用程序 这里.
随着时间的推移，大多数评论和错误都得到了纠正。

那么，现在让我们继续讨论 SQLIndexManager 实用程序本身。

该应用程序是在 Visual Studio 4.5 中用 C# .NET Framework 2017 编写的，并使用 DevExpress 作为表单：

看起来像这样：

所有请求均在以下文件中生成：

1. Index
2. 询问
3. 查询引擎
4. 服务器信息

当连接到数据库并向 DBMS 发送查询时，应用程序将按如下方式签名：

ApplicationName=”SQLIndexManager”

当您启动应用程序时，将打开一个模式窗口以添加连接：

在这里，加载可通过本地网络访问的所有 MS SQL Server 实例的完整列表尚不可行。

您还可以使用主菜单最左侧的按钮添加连接：

接下来，将启动对 DBMS 的以下查询：

1. 获取有关 DBMS 的信息

   SELECT ProductLevel  = SERVERPROPERTY('ProductLevel')
        , Edition       = SERVERPROPERTY('Edition')
        , ServerVersion = SERVERPROPERTY('ProductVersion')
        , IsSysAdmin    = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT)
   

2. 获取可用数据库及其简要属性的列表

   SELECT DatabaseName = t.[name]
        , d.DataSize
        , DataUsedSize  = CAST(NULL AS BIGINT)
        , d.LogSize
        , LogUsedSize   = CAST(NULL AS BIGINT)
        , RecoveryModel = t.recovery_model_desc
        , LogReuseWait  = t.log_reuse_wait_desc
   FROM sys.databases t WITH(NOLOCK)
   LEFT JOIN (
       SELECT [database_id]
            , DataSize = SUM(CASE WHEN [type] = 0 THEN CAST(size AS BIGINT) END)
            , LogSize  = SUM(CASE WHEN [type] = 1 THEN CAST(size AS BIGINT) END)
       FROM sys.master_files WITH(NOLOCK)
       GROUP BY [database_id]
   ) d ON d.[database_id] = t.[database_id]
   WHERE t.[state] = 0
       AND t.[database_id] != 2
       AND ISNULL(HAS_DBACCESS(t.[name]), 1) = 1
   

执行上述脚本后，将出现一个窗口，其中包含有关所选 MS SQL Server 实例的数据库的简要信息：

值得注意的是，扩展信息是根据权限显示的。 如果有 系统管理员，然后您可以从视图中选择数据 系统主文件。 如果没有这样的权限，则只需返回较少的数据，以免减慢请求速度。

在这里您需要选择感兴趣的数据库并单击“确定”按钮。

接下来，将为每个选定的数据库执行以下脚本来分析索引的状态：

指数状况分析

declare @Fragmentation float=15;
declare @MinIndexSize bigint=768;
declare @MaxIndexSize bigint=1048576;
declare @PreDescribeSize bigint=32768;
SET NOCOUNT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
IF OBJECT_ID('tempdb.dbo.#AllocationUnits') IS NOT NULL
DROP TABLE #AllocationUnits
CREATE TABLE #AllocationUnits (
ContainerID   BIGINT PRIMARY KEY
, ReservedPages BIGINT NOT NULL
, UsedPages     BIGINT NOT NULL
)
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
IF OBJECT_ID('tempdb.dbo.#ExcludeList') IS NOT NULL
DROP TABLE #ExcludeList
CREATE TABLE #ExcludeList (ID INT PRIMARY KEY)
INSERT INTO #ExcludeList
SELECT [object_id]
FROM sys.objects WITH(NOLOCK)
WHERE [type] IN ('V', 'U')
AND ( [is_ms_shipped] = 1 )
IF OBJECT_ID('tempdb.dbo.#Partitions') IS NOT NULL
DROP TABLE #Partitions
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)
IF OBJECT_ID('tempdb.dbo.#Indexes') IS NOT NULL
DROP TABLE #Indexes
CREATE TABLE #Indexes (
ObjectID         INT NOT NULL
, IndexID          INT NOT NULL
, IndexName        SYSNAME NULL
, PagesCount       BIGINT NOT NULL
, UnusedPagesCount BIGINT NOT NULL
, PartitionNumber  INT NOT NULL
, RowsCount        BIGINT NOT NULL
, IndexType        TINYINT NOT NULL
, IsAllowPageLocks BIT NOT NULL
, DataSpaceID      INT NOT NULL
, DataCompression  TINYINT NOT NULL
, IsUnique         BIT NOT NULL
, IsPK             BIT NOT NULL
, FillFactorValue  INT NOT NULL
, IsFiltered       BIT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)
INSERT INTO #Indexes
SELECT ObjectID         = i.[object_id]
, IndexID          = i.index_id
, IndexName        = i.[name]
, PagesCount       = a.ReservedPages
, UnusedPagesCount = CASE WHEN ABS(a.ReservedPages - a.UsedPages) > 32 THEN a.ReservedPages - a.UsedPages ELSE 0 END
, 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
DECLARE @files TABLE (ID INT PRIMARY KEY)
INSERT INTO @files
SELECT DISTINCT [data_space_id]
FROM sys.database_files WITH(NOLOCK)
WHERE [state] != 0
AND [type] = 0
IF @@ROWCOUNT > 0 BEGIN
DELETE FROM i
FROM #Indexes i
LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id]
WHERE ISNULL(dds.[data_space_id], i.DataSpaceID) IN (SELECT * FROM @files)
END
DECLARE @DBID   INT
, @DBNAME SYSNAME
SET @DBNAME = DB_NAME()
SELECT @DBID = [database_id]
FROM sys.databases WITH(NOLOCK)
WHERE [name] = @DBNAME
IF OBJECT_ID('tempdb.dbo.#Fragmentation') IS NOT NULL
DROP TABLE #Fragmentation
CREATE TABLE #Fragmentation (
ObjectID         INT NOT NULL
, IndexID          INT NOT NULL
, PartitionNumber  INT NOT NULL
, Fragmentation    FLOAT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)
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)
IF OBJECT_ID('tempdb.dbo.#Columns') IS NOT NULL
DROP TABLE #Columns
CREATE TABLE #Columns (
ObjectID     INT NOT NULL
, ColumnID     INT NOT NULL
, ColumnName   SYSNAME NULL
, SystemTypeID TINYINT NULL
, IsSparse     BIT
, IsColumnSet  BIT
, MaxLen       INT
, PRIMARY KEY (ObjectID, ColumnID)
)
INSERT INTO #Columns
SELECT ObjectID     = [object_id]
, ColumnID     = [column_id]
, ColumnName   = [name]
, SystemTypeID = [system_type_id]
, IsSparse     = [is_sparse]
, IsColumnSet  = [is_column_set]
, MaxLen       = [max_length]
FROM sys.columns WITH(NOLOCK)
WHERE [object_id] IN (SELECT DISTINCT i.ObjectID FROM #Indexes i)
IF OBJECT_ID('tempdb.dbo.#IndexColumns') IS NOT NULL
DROP TABLE #IndexColumns
CREATE TABLE #IndexColumns (
ObjectID   INT NOT NULL
, IndexID    INT NOT NULL
, OrderID    INT NOT NULL
, ColumnID   INT NOT NULL
, IsIncluded BIT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, ColumnID)
)
INSERT INTO #IndexColumns
SELECT ObjectID   = [object_id]
, IndexID    = [index_id]
, OrderID    = CASE WHEN [is_included_column] = 0 THEN [key_ordinal] ELSE [index_column_id] END
, ColumnID   = [column_id]
, IsIncluded = ISNULL([is_included_column], 0)
FROM sys.index_columns ic WITH(NOLOCK)
WHERE EXISTS(
SELECT *
FROM #Indexes i
WHERE i.ObjectID = ic.[object_id]
AND i.IndexID = ic.[index_id]
AND i.IndexType IN (1, 2)
)
IF OBJECT_ID('tempdb.dbo.#Lob') IS NOT NULL
DROP TABLE #Lob
CREATE TABLE #Lob (
ObjectID    INT NOT NULL
, IndexID     INT NOT NULL
, IsLobLegacy BIT
, IsLob       BIT
, PRIMARY KEY (ObjectID, IndexID)
)
INSERT INTO #Lob (ObjectID, IndexID, IsLobLegacy, IsLob)
SELECT c.ObjectID
, IndexID     = ISNULL(i.IndexID, 1)
, IsLobLegacy = MAX(CASE WHEN c.SystemTypeID IN (34, 35, 99) THEN 1 END)
, IsLob       = 0
FROM #Columns c
LEFT JOIN #IndexColumns i ON c.ObjectID = i.ObjectID AND c.ColumnID = i.ColumnID
WHERE c.SystemTypeID IN (34, 35, 99)
GROUP BY c.ObjectID
, i.IndexID
IF OBJECT_ID('tempdb.dbo.#Sparse') IS NOT NULL
DROP TABLE #Sparse
CREATE TABLE #Sparse (ObjectID INT PRIMARY KEY)
INSERT INTO #Sparse
SELECT DISTINCT ObjectID
FROM #Columns
WHERE IsSparse = 1
OR IsColumnSet = 1
IF OBJECT_ID('tempdb.dbo.#AggColumns') IS NOT NULL
DROP TABLE #AggColumns
CREATE TABLE #AggColumns (
ObjectID        INT NOT NULL
, IndexID         INT NOT NULL
, IndexColumns    NVARCHAR(MAX)
, IncludedColumns NVARCHAR(MAX)
, PRIMARY KEY (ObjectID, IndexID)
)
INSERT INTO #AggColumns
SELECT t.ObjectID
, t.IndexID
, IndexColumns = STUFF((
SELECT ', [' + c.ColumnName + ']'
FROM #IndexColumns i
JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID
WHERE i.ObjectID = t.ObjectID
AND i.IndexID = t.IndexID
AND i.IsIncluded = 0
ORDER BY i.OrderID
FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
, IncludedColumns = STUFF((
SELECT ', [' + c.ColumnName + ']'
FROM #IndexColumns i
JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID
WHERE i.ObjectID = t.ObjectID
AND i.IndexID = t.IndexID
AND i.IsIncluded = 1
ORDER BY i.OrderID
FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
FROM (
SELECT DISTINCT ObjectID, IndexID
FROM #Indexes
WHERE IndexType IN (1, 2)
) t
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)
)

从查询本身可以看出，临时表的使用非常频繁。 这样做是为了避免重新编译，并且在大型方案的情况下，可以在插入数据时并行生成计划，因为只能在一个线程中插入表变量。

执行上述脚本后，将出现一个带有索引表的窗口：

您还可以在此处显示其他详细信息，例如：

1. 数据库
2. 节数
3. 最后一次通话的日期和时间
4. 压缩
5. 文件组

等等
扬声器本身可以定制：

在修复列的单元格中，您可以选择优化期间将执行的操作。 此外，扫描完成后，将根据所选设置选择默认操作：

您必须选择所需的索引进行处理。

使用主菜单，您可以保存脚本（同一按钮启动索引优化过程本身）：

并以不同的格式保存表格（同一个按钮允许您打开用于分析和优化索引的详细设置）：

您还可以通过单击主菜单左侧放大镜旁边的第三个按钮来更新信息。

带有放大镜的按钮允许您选择所需的数据库以供考虑。

目前还没有全面的帮助系统。 因此，按下“？”按钮只会导致出现一个模式窗口，其中包含有关软件产品的基本信息：

除了上述所有内容外，主菜单还有一个搜索栏：

当开始索引优化过程时：

您还可以在窗口底部查看已执行操作的日志：

在索引分析和优化的详细设置窗口中，您可以配置更细微的选项：

申请要求：

1. 不仅可以选择性地更新索引的统计信息，还可以以不同的方式（完全更新或部分更新）
2. 不仅可以选择数据库，还可以选择不同的服务器（当有很多MS SQL Server实例时，这非常方便）
3. 为了获得更大的使用灵活性，建议将命令包装在库中并将它们输出到 PowerShell 命令，例如此处所做的：
4. dbatools.io/命令
5. 可以保存和更改整个应用程序的个人设置，如有必要，还可以保存和更改 MS SQL Server 的每个实例和每个数据库的个人设置
6. 从第 2 点和第 4 点来看，您希望按数据库创建组和按 MS SQL Server 实例创建组，其设置是相同的
7. 搜索重复索引（完整和不完整，略有不同或仅在包含的列中有所不同）
8. 由于 SQLIndexManager 仅用于 MS SQL Server DBMS，因此有必要在名称中反映这一点，例如，如下所示： SQLIndexManager for MS SQL Server
9. 将应用程序的所有非 GUI 部分移至单独的模块中，并在 .NET Core 2.1 中重写它们

在撰写本文时，愿望的第 6 项正在积极制定中，并且已经以搜索完整且相似的重复项的形式提供了支持：

来源

• 自适应指标优化方法
• SQLIndexManager 实用程序
• SQLIndexManager 实用程序的简要技术概述
• 关于 SQLIndexManager 实用程序的讨论
• SQL Index Manager – 用于 SQL Server 和 Azure 上索引维护的免费 G​​UI 工具
• SQL Server索引分析与优化

来源： habr.com