Đánh giá về công cụ miễn phí SQLIndexManager

Như bạn đã biết, các chỉ mục đóng vai trò quan trọng trong DBMS, cung cấp khả năng tìm kiếm nhanh các bản ghi được yêu cầu. Đó là lý do tại sao việc phục vụ chúng kịp thời lại rất quan trọng. Khá nhiều tài liệu đã được viết về phân tích và tối ưu hóa, kể cả trên Internet. Ví dụ: chủ đề này gần đây đã được xem xét trong ấn phẩm này.

Có nhiều giải pháp trả phí và miễn phí cho việc này. Ví dụ, có một cái được làm sẵn quyết định, dựa trên phương pháp tối ưu hóa chỉ số thích ứng.

Tiếp theo, hãy xem tiện ích miễn phí Trình quản lý chỉ mục SQL, tác giả AlanDenton.

Sự khác biệt kỹ thuật chính giữa SQLIndexManager và một số chất tương tự khác được đưa ra bởi chính tác giả đây и đây.

Trong bài viết này, chúng ta sẽ xem xét bên ngoài dự án và khả năng hoạt động của giải pháp phần mềm này.

Thảo luận về tiện ích này đây.
Theo thời gian, hầu hết các nhận xét và lỗi đã được sửa.

Vì vậy, bây giờ chúng ta hãy chuyển sang tiện ích SQLIndexManager.

Ứng dụng được viết bằng C# .NET Framework 4.5 trong Visual Studio 2017 và sử dụng DevExpress cho các biểu mẫu:

và trông như thế này:

Tất cả các yêu cầu được tạo trong các tệp sau:

1. Chỉ số
2. Query
3. Công cụ truy vấn
4. Thông tin máy chủ

Khi kết nối với cơ sở dữ liệu và gửi truy vấn tới DBMS, ứng dụng được ký như sau:

ApplicationName=”SQLIndexManager”

Khi bạn khởi chạy ứng dụng, một cửa sổ phương thức sẽ mở ra để thêm kết nối:

Ở đây, việc tải danh sách đầy đủ tất cả các phiên bản MS SQL Server có thể truy cập qua mạng cục bộ vẫn chưa hoạt động.

Bạn cũng có thể thêm kết nối bằng nút ngoài cùng bên trái trên menu chính:

Tiếp theo, các truy vấn sau tới DBMS sẽ được khởi chạy:

1. Lấy thông tin về DBMS

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

2. Lấy danh sách các cơ sở dữ liệu có sẵn với các thuộc tính ngắn gọn của chúng

   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
   

Sau khi thực thi các đoạn script trên, một cửa sổ sẽ xuất hiện chứa thông tin ngắn gọn về cơ sở dữ liệu của phiên bản MS SQL Server đã chọn:

Điều đáng chú ý là thông tin mở rộng được hiển thị dựa trên quyền. Nếu có sysadmin, sau đó bạn có thể chọn dữ liệu từ chế độ xem sys.master_files. Nếu không có các quyền đó thì sẽ có ít dữ liệu được trả về hơn để không làm chậm yêu cầu.

Ở đây bạn cần chọn cơ sở dữ liệu quan tâm và nhấp vào nút “OK”.

Tiếp theo, tập lệnh sau sẽ được thực thi cho từng cơ sở dữ liệu được chọn để phân tích trạng thái của các chỉ mục:

Phân tích trạng thái chỉ mục

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)
)

Như có thể thấy từ chính các truy vấn, các bảng tạm thời được sử dụng khá thường xuyên. Điều này được thực hiện để không phải biên dịch lại và trong trường hợp sơ đồ lớn, sơ đồ có thể được tạo song song khi chèn dữ liệu, vì chỉ có thể chèn các biến bảng trong một luồng.

Sau khi thực thi đoạn script trên, một cửa sổ có bảng chỉ mục sẽ xuất hiện:

Bạn cũng có thể hiển thị các thông tin chi tiết khác tại đây, chẳng hạn như:

1. cơ sở dữ liệu
2. số phần
3. ngày và giờ của cuộc gọi cuối cùng
4. nén
5. nhóm tập tin

d và t..
Bản thân loa có thể được tùy chỉnh:

Trong các ô của cột Sửa, bạn có thể chọn hành động nào sẽ được thực hiện trong quá trình tối ưu hóa. Ngoài ra, khi quá trình quét hoàn tất, hành động mặc định sẽ được chọn dựa trên cài đặt đã chọn:

Bạn phải chọn các chỉ mục mong muốn để xử lý.

Sử dụng menu chính, bạn có thể lưu tập lệnh (nút tương tự sẽ tự bắt đầu quá trình tối ưu hóa chỉ mục):

và lưu bảng ở các định dạng khác nhau (cùng một nút cho phép bạn mở cài đặt chi tiết để phân tích và tối ưu hóa chỉ mục):

Bạn cũng có thể cập nhật thông tin bằng cách nhấp vào nút thứ ba bên trái trong menu chính bên cạnh kính lúp.

Nút có kính lúp cho phép bạn chọn cơ sở dữ liệu mong muốn để xem xét.

Hiện tại không có hệ thống trợ giúp toàn diện. Vì vậy, nhấn nút “?” sẽ chỉ làm xuất hiện một cửa sổ phương thức chứa thông tin cơ bản về sản phẩm phần mềm:

Ngoài mọi thứ được mô tả ở trên, menu chính còn có thanh tìm kiếm:

Khi bắt đầu quá trình tối ưu hóa chỉ mục:

Bạn cũng có thể xem nhật ký các hành động đã thực hiện ở cuối cửa sổ:

Trong cửa sổ cài đặt chi tiết để phân tích và tối ưu hóa chỉ mục, bạn có thể định cấu hình các tùy chọn tinh tế hơn:

Yêu cầu cho ứng dụng:

1. cho phép cập nhật có chọn lọc số liệu thống kê không chỉ cho các chỉ mục và theo nhiều cách khác nhau (cập nhật đầy đủ hoặc một phần)
2. giúp không chỉ có thể chọn cơ sở dữ liệu mà còn có thể chọn các máy chủ khác nhau (điều này rất thuận tiện khi có nhiều phiên bản MS SQL Server)
3. Để sử dụng linh hoạt hơn, bạn nên gói các lệnh trong thư viện và xuất chúng thành các lệnh PowerShell, chẳng hạn như đã thực hiện ở đây:
4. dbatools.io/lệnh
5. cho phép lưu và thay đổi cài đặt cá nhân cho toàn bộ ứng dụng và, nếu cần, cho từng phiên bản MS SQL Server và từng cơ sở dữ liệu
6. Từ điểm 2 và 4, bạn muốn tạo nhóm theo cơ sở dữ liệu và nhóm bằng phiên bản MS SQL Server, có cài đặt giống nhau
7. tìm kiếm các chỉ mục trùng lặp (đầy đủ và không đầy đủ, hơi khác một chút hoặc chỉ khác nhau ở các cột được bao gồm)
8. Vì SQLIndexManager chỉ được sử dụng cho MS SQL Server DBMS nên cần phải phản ánh điều này trong tên, chẳng hạn như sau: SQLIndexManager for MS SQL Server
9. Di chuyển tất cả các phần không phải GUI của ứng dụng thành các mô-đun riêng biệt và viết lại chúng trong .NET Core 2.1

Tại thời điểm viết bài, mục 6 của mong muốn đang được tích cực phát triển và đã có hỗ trợ dưới hình thức tìm kiếm các bản sao hoàn chỉnh và tương tự:

nguồn

• phương pháp tối ưu hóa chỉ số thích ứng
• Tiện ích SQLIndexManager
• tổng quan kỹ thuật ngắn gọn về tiện ích SQLIndexManager
• thảo luận về tiện ích SQLIndexManager
• Trình quản lý chỉ mục SQL - Công cụ GUI miễn phí để bảo trì chỉ mục trên SQL Server và Azure
• Phân tích và tối ưu hóa chỉ mục máy chủ SQL

Nguồn: www.habr.com