Pulsuz SQLIndexManager alətinin nəzərdən keçirilməsi

Bildiyiniz kimi, indekslər DBMS-də vacib rol oynayır, tələb olunan qeydlərin sürətli axtarışını təmin edir. Buna görə də onlara vaxtında xidmət göstərmək çox vacibdir. Təhlil və optimallaşdırma haqqında, o cümlədən İnternetdə kifayət qədər çox material yazılmışdır. Məsələn, bu mövzu yaxınlarda nəzərdən keçirildi bu nəşrin.

Bunun üçün çoxlu pullu və pulsuz həllər var. Məsələn, bir hazır var qərar, adaptiv indeks optimallaşdırma metoduna əsaslanır.

Sonra pulsuz yardım proqramına baxaq SQLIndexManager, müəllifidir AlanDenton.

SQLIndexManager ilə bir sıra digər analoqlar arasındakı əsas texniki fərqi müəllifin özü verir burada и burada.

Bu yazıda biz layihəyə və bu proqram həllinin əməliyyat imkanlarına kənardan nəzər salacağıq.

Bu yardım proqramını müzakirə edin burada.
Vaxt keçdikcə şərhlərin və səhvlərin əksəriyyəti düzəldildi.

Beləliklə, indi SQLIndexManager yardım proqramının özünə keçək.

Tətbiq Visual Studio 4.5-də C# .NET Framework 2017-də yazılmışdır və formalar üçün DevExpress-dən istifadə edir:

və belə görünür:

Bütün sorğular aşağıdakı fayllarda yaradılır:

1. indeks
2. Sual
3. QueryEngine
4. ServerInfo

Verilənlər bazasına qoşulduqda və DBMS-ə sorğular göndərərkən, ərizə aşağıdakı kimi imzalanır:

ApplicationName=”SQLIndexManager”

Tətbiqi işə saldığınız zaman əlaqə əlavə etmək üçün modal pəncərə açılacaq:

Burada, yerli şəbəkələr üzərindən əlçatan olan bütün MS SQL Server nümunələrinin tam siyahısını yükləmək hələ işləmir.

Siz həmçinin əsas menyuda ən sol düyməni istifadə edərək əlaqə əlavə edə bilərsiniz:

Sonra, DBMS-ə aşağıdakı sorğular işə salınacaq:

1. DBMS haqqında məlumat əldə etmək

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

2. Qısa xassələri ilə mövcud verilənlər bazalarının siyahısını əldə etmək

   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
   

Yuxarıdakı skriptləri yerinə yetirdikdən sonra, seçilmiş MS SQL Server nümunəsinin verilənlər bazası haqqında qısa məlumatı ehtiva edən bir pəncərə görünəcəkdir:

Qeyd etmək lazımdır ki, genişləndirilmiş məlumatlar hüquqlara əsaslanaraq göstərilir. Varsa sistem meneceri, sonra görünüşdən məlumatları seçə bilərsiniz sys.master_files. Belə hüquqlar yoxdursa, sorğunu yavaşlatmamaq üçün daha az məlumat sadəcə qaytarılır.

Burada maraqlı məlumat bazalarını seçməli və “OK” düyməsini sıxmalısınız.

Bundan sonra, indekslərin vəziyyətini təhlil etmək üçün hər bir seçilmiş verilənlər bazası üçün aşağıdakı skript icra ediləcək:

İndeks statusunun təhlili

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

Sorğuların özündən göründüyü kimi, müvəqqəti cədvəllər olduqca tez-tez istifadə olunur. Bu, təkrar kompilyasiya olmaması üçün edilir və böyük bir sxem halında, cədvəl dəyişənlərinin daxil edilməsi yalnız bir ipdə mümkün olduğundan, plan məlumatları daxil edərkən paralel olaraq yaradıla bilər.

Yuxarıdakı skripti icra etdikdən sonra indeks cədvəli olan bir pəncərə görünəcək:

Burada həmçinin digər ətraflı məlumatları da göstərə bilərsiniz, məsələn:

1. verilənlər bazası
2. bölmələrin sayı
3. son zəng tarixi və vaxtı
4. sıxılma
5. fayl qrupu

i t. d.
Dinamiklərin özləri fərdiləşdirilə bilər:

Düzəliş sütununun xanalarında optimallaşdırma zamanı hansı hərəkətin yerinə yetiriləcəyini seçə bilərsiniz. Həmçinin, skan tamamlandıqda, seçilmiş parametrlərə əsasən defolt əməliyyat seçilir:

Emal üçün istədiyiniz indeksləri seçməlisiniz.

Əsas menyudan istifadə edərək skripti saxlaya bilərsiniz (eyni düymə indeksin optimallaşdırılması prosesini özü başlayır):

və cədvəli müxtəlif formatlarda saxlayın (eyni düymə indeksləri təhlil etmək və optimallaşdırmaq üçün ətraflı parametrləri açmağa imkan verir):

Siz həmçinin böyüdücü şüşənin yanındakı əsas menyunun sol tərəfindəki üçüncü düyməni sıxaraq məlumatları yeniləyə bilərsiniz.

Böyüdücü şüşəli düymə baxılmaq üçün istədiyiniz verilənlər bazasını seçməyə imkan verir.

Hazırda hərtərəfli yardım sistemi yoxdur. Buna görə də “?” düyməsini sıxmaq sadəcə olaraq proqram məhsulu haqqında əsas məlumatları ehtiva edən modal pəncərənin görünməsinə səbəb olacaq:

Yuxarıda təsvir edilən hər şeyə əlavə olaraq, əsas menyuda axtarış çubuğu var:

İndeksin optimallaşdırılması prosesinə başladıqda:

Siz həmçinin pəncərənin altındakı yerinə yetirilən hərəkətlərin jurnalına baxa bilərsiniz:

İndeks təhlili və optimallaşdırma üçün ətraflı parametrlər pəncərəsində daha incə variantları konfiqurasiya edə bilərsiniz:

Müraciət üçün müraciətlər:

1. Statistikanı təkcə indekslər üçün deyil, həm də müxtəlif üsullarla (tam və ya qismən yenilə) seçmə şəkildə yeniləməyi mümkün etmək
2. təkcə verilənlər bazasını deyil, həm də müxtəlif serverləri seçməyə imkan verin (MS SQL Serverin bir çox nümunələri olduqda bu çox rahatdır)
3. İstifadədə daha çox çeviklik üçün əmrləri kitabxanalara bükmək və PowerShell əmrlərinə çıxarmaq tövsiyə olunur, məsələn, burada:
4. dbatools.io/commands
5. həm bütün proqram üçün, həm də zərurət yarandıqda, MS SQL Serverin hər bir nümunəsi və hər bir verilənlər bazası üçün şəxsi parametrləri saxlamağa və dəyişdirməyə imkan verir.
6. 2 və 4-cü bəndlərdən belə nəticə çıxır ki, siz verilənlər bazası üzrə qruplar və parametrləri eyni olan MS SQL Server nümunələri üzrə qruplar yaratmaq istəyirsiniz.
7. dublikat indeksləri axtarın (tam və natamam, bir az fərqli və ya yalnız daxil edilmiş sütunlarda fərqlənir)
8. SQLIndexManager yalnız MS SQL Server DBMS üçün istifadə edildiyi üçün bunu adda əks etdirmək lazımdır, məsələn, aşağıdakı kimi: MS SQL Server üçün SQLIndexManager
9. Tətbiqin bütün GUI olmayan hissələrini ayrı modullara köçürün və onları .NET Core 2.1-də yenidən yazın.

Yazı zamanı istəklərin 6-cı bəndi fəal şəkildə hazırlanır və tam və oxşar dublikatların axtarışı şəklində artıq dəstək var:

İnformasiya qaynaqları

• adaptiv indeksin optimallaşdırılması üsulu
• SQLIndexManager yardım proqramı
• SQLIndexManager yardım proqramının qısa texniki icmalı
• SQLIndexManager yardım proqramı üzrə müzakirə
• SQL İndeks Meneceri – SQL Server və Azure-da İndekslərə Baxım üçün Pulsuz GUI Aləti
• SQL Server İndeksinin Təhlili və Optimizasiyası

Mənbə: www.habr.com