免費工具 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 上索引維護的免費 GUI 工具
• SQL Server索引分析與最佳化

來源： www.habr.com