Recenzie a instrumentului gratuit SQLIndexManager

După cum se știe, indecșii joacă un rol important în SGBD, oferind o căutare rapidă a înregistrărilor necesare. De aceea, este atât de important să fie întreținute la timp. S-au scris mult material despre analiză și optimizare, inclusiv pe internet. De exemplu, recent a fost realizată o revizuire a acestui subiect în din blog..

Există multe soluții, atât plătite, cât și gratuite pentru asta. De exemplu, există o aplicație gata de utilizare soluție, bazată pe o metodă adaptivă de optimizare a indecșilor.

Mai departe vom analiza utilitarul gratuit SQLIndexManager, a cărui autor este AlanDenton.

Principala diferență tehnică între SQLIndexManager și altele similare este indicată de autorul însuși. aici și aici.

În acest articol, vom analiza proiectul și posibilitățile de utilizare a acestei soluții software.

Discutăm despre acest utilitar aici.
De-a lungul timpului, majoritatea observațiilor și erorilor au fost corectate.

Deci, acum să trecem la utilitarul SQLIndexManager.

Aplicația este scrisă în C# .NET Framework 4.5 în Visual Studio 2017 și utilizează DevExpress pentru formulare:

și arată astfel:

Toate interogările sunt formate în următoarele fișiere:

1. Index
2. Query
3. QueryEngine
4. ServerInfo

Atunci când se conectează la baza de date și trimite interogări la SGBD, aplicația se abonează astfel:

ApplicationName="SQLIndexManager"

La lansarea aplicației, va apărea o fereastră modală pentru adăugarea unei conexiuni:

În prezent, nu funcționează încă încărcarea listei complete a tuturor instanțelor MS SQL Server disponibile în rețelele locale.

De asemenea, o conexiune poate fi adăugată folosind butonul extrem stâng din meniul principal:

Apoi, următoarele interogări vor fi lansate către SGBD:

1. Obținerea informațiilor despre SGBD

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

2. Obținerea listei bazelor de date disponibile cu proprietățile lor scurte

   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
   

După executarea scripturilor de mai sus, va apărea o fereastră conținând informații concise despre bazele de date ale instanței alese MS SQL Server:

Este important de menționat că informațiile extinse sunt afișate în funcție de permisiuni. Dacă există sysadmin, atunci se pot selecta datele din vizualizare sys.master_files. Dacă astfel de permisiuni nu există, se returnează pur și simplu mai puține date pentru a nu încetini cererea.

Aici este necesar să selectați bazele de date dorite și să apăsați butonul „OK”.

Apoi, va fi executat următorul script pentru fiecare bază de date selectată pentru a analiza starea indexurilor:

Analiza stării indexurilor

declare @Fragmentare float=15;
declare @DimensiuneMinimIndex bigint=768;
declare @DimensiuneMaximIndex bigint=1048576;
declare @DimensiunePreDescriere bigint=32768;

SET NOCOUNT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF

IF OBJECT_ID('tempdb.dbo.#AlocareUnitati') IS NOT NULL
    DROP TABLE #AlocareUnitati

CREATE TABLE #AlocareUnitati (
      IDContainer   BIGINT PRIMARY KEY
    , PaginiRezervate BIGINT NOT NULL
    , PaginiUtilizate     BIGINT NOT NULL
)

INSERT INTO #AlocareUnitati (IDContainer, PaginiRezervate, PaginiUtilizate)
SELECT [container_id]
     , SUM([total_pages])
     , SUM([used_pages])
FROM sys.allocation_units WITH(NOLOCK)
GROUP BY [container_id]
HAVING SUM([total_pages]) BETWEEN @DimensiuneMinimIndex AND @DimensiuneMaximIndex

IF OBJECT_ID('tempdb.dbo.#ListaExcludere') IS NOT NULL
    DROP TABLE #ListaExcludere

CREATE TABLE #ListaExcludere (ID INT PRIMARY KEY)

INSERT INTO #ListaExcludere
SELECT [object_id]
FROM sys.objects WITH(NOLOCK)
WHERE [type] IN ('V', 'U')
    AND ( [is_ms_shipped] = 1 )

IF OBJECT_ID('tempdb.dbo.#Partitii') IS NOT NULL
    DROP TABLE #Partitii

SELECT [object_id]
     , [index_id]
     , [partition_id]
     , [partition_number]
     , [rows]
     , [data_compression]
INTO #Partitii
FROM sys.partitions WITH(NOLOCK)
WHERE [object_id] > 255
    AND [rows] > 0
    AND [object_id] NOT IN (SELECT * FROM #ListaExcludere)

IF OBJECT_ID('tempdb.dbo.#Indexuri') IS NOT NULL
    DROP TABLE #Indexuri

CREATE TABLE #Indexuri (
      IDObiect         INT NOT NULL
    , IDIndex          INT NOT NULL
    , NumeIndex        SYSNAME NULL
    , NumărPagini       BIGINT NOT NULL
    , NumărPaginiNeutilizate BIGINT NOT NULL
    , NumărPartiție  INT NOT NULL
    , NumărRânduri        BIGINT NOT NULL
    , TipIndex        TINYINT NOT NULL
    , PermiteBlocarePagini BIT NOT NULL
    , IDDataSpace      INT NOT NULL
    , ComprimareDate  TINYINT NOT NULL
    , EsteUnic         BIT NOT NULL
    , EstePK             BIT NOT NULL
    , ValoareFillFactor  INT NOT NULL
    , EsteFiltrat       BIT NOT NULL
    , PRIMARY KEY (IDObiect, IDIndex, NumărPartiție)
)

INSERT INTO #Indexuri
SELECT IDObiect         = i.[object_id]
     , IDIndex          = i.index_id
     , NumeIndex        = i.[name]
     , NumărPagini       = a.PaginiRezervate
     , NumărPaginiNeutilizate = CASE WHEN ABS(a.PaginiRezervate - a.PaginiUtilizate) > 32 THEN a.PaginiRezervate - a.PaginiUtilizate ELSE 0 END
     , NumărPartiție  = p.[partition_number]
     , NumărRânduri        = ISNULL(p.[rows], 0)
     , TipIndex        = i.[type]
     , PermiteBlocarePagini = i.[allow_page_locks]
     , IDDataSpace      = i.[data_space_id]
     , ComprimareDate  = p.[data_compression]
     , EsteUnic         = i.[is_unique]
     , EstePK             = i.[is_primary_key]
     , ValoareFillFactor  = i.[fill_factor]
     , EsteFiltrat       = i.[has_filter]
FROM #AlocareUnitati a
JOIN #Partitii p ON a.IDContainer = 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 #Indexuri i
    LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.IDDataSpace = dds.[partition_scheme_id] AND i.NumărPartiție = dds.[destination_id]
    WHERE ISNULL(dds.[data_space_id], i.IDDataSpace) 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.#Fragmentare') IS NOT NULL
    DROP TABLE #Fragmentare

CREATE TABLE #Fragmentare (
      IDObiect         INT NOT NULL
    , IDIndex          INT NOT NULL
    , NumărPartiție  INT NOT NULL
    , Fragmentare    FLOAT NOT NULL
    , PRIMARY KEY (IDObiect, IDIndex, NumărPartiție)
)

INSERT INTO #Fragmentare (IDObiect, IDIndex, NumărPartiție, Fragmentare)
SELECT i.IDObiect
     , i.IDIndex
     , i.NumărPartiție
     , r.[avg_fragmentation_in_percent]
FROM #Indexuri i
CROSS APPLY sys.dm_db_index_physical_stats(@DBID, i.IDObiect, i.IDIndex, i.NumărPartiție, 'LIMITED') r
WHERE i.NumărPagini = @Fragmentare
        OR
            i.NumărPagini > @DimensiunePreDescriere
        OR
            i.TipIndex IN (5, 6)
    )

După cum reiese din solicitările în sine, tabelele temporare sunt folosite destul de frecvent. Acest lucru se face pentru a evita recompilarea, iar în cazul unui schema mare, planul poate fi generat paralel în timpul inserării datelor, deoarece inserția cu variabilele tabelare este posibilă doar într-un singur flux.

După executarea scriptului menționat anterior, va apărea o fereastră cu tabela de indecși:

De asemenea, aici se pot afișa și alte informații detaliate, cum ar fi:

1. baza de date
2. numărul de secțiuni
3. data și ora ultimei accesări
4. compresie
5. grup de fișiere

etc.
Coloanele în sine pot fi configurate:

În celulele coloanei Fix, se poate alege ce acțiune va fi efectuată în timpul optimizării. De asemenea, la finalizarea analizei, acțiunea implicită este selectată pe baza setărilor alese:

Este necesar să selectați indecșii doriti pentru procesare.

Prin intermediul meniului principal, se poate salva scriptul (aceeași buton pornește procesul de optimizare a indecșilor):

de asemenea, se poate salva tabela în diferite formate (aceasta buton vă permite să deschideți setările detaliate pentru analiza și optimizarea indecșilor):

De asemenea, informația poate fi actualizată apăsând pe al treilea buton din stânga din meniul principal, lângă lupă.

Butonul cu lupă permite selectarea bazelor de date dorite pentru examinare.

În prezent, nu există un sistem de referință complet. Prin urmare, apăsarea butonului „?” va provoca doar apariția unei feronții modale conținând informații de bază despre produsul software:

În plus față de toate cele menționate anterior, în meniul principal există o bară de căutare:

Atunci când se lansează procesul de optimizare a indecșilor:

De asemenea, în partea de jos a feronții se poate vizualiza jurnalul acțiunilor efectuate:

În fereastra de setări detaliate pentru analiza și optimizarea indecșilor, se pot configura opțiuni mai fine:

Sugestii pentru aplicație:

1. să se facă posibilă actualizarea selectivă a statisticilor nu doar pentru indecși și prin diferite metode (actualizare completă sau parțială)
2. să se facă posibilă selectarea nu doar a Bazei de Date, ci și a diferitelor servere (acest lucru este foarte convenabil atunci când există multe instanțe MS SQL Server)
3. pentru o mai mare flexibilitate în utilizare, se propune înfășurarea comenzilor în biblioteci și afișarea în comenzile PowerShell, așa cum este realizat, de exemplu, aici:
4. dbatools.io/commands
5. să permită salvarea și modificarea setărilor personale atât pentru întreaga aplicație, cât și, dacă este necesar, pentru fiecare instanță MS SQL Server și fiecare bază de date
6. din pct. 2 și 4 rezultă dorința de a crea grupuri pe baze de date și grupuri pe instanțe MS SQL Server, pentru care setările sunt aceleași
7. să efectueze căutarea dublurilor de indecși (completi și incompleti, care fie diferă puțin, fie se deosebesc doar prin coloanele incluse)
8. deoarece SQLIndexManager este utilizat doar pentru SGBD MS SQL Server, este necesar să se reflecte acest lucru în nume, de exemplu, astfel: SQLIndexManager pentru MS SQL Server
9. să se extragă toate părțile aplicației non-GUI în module separate și să fie rescrise pe .NET Core 2.1

La momentul redactării acestui articol, pct. 6 din cerințe este în prezent activ dezvoltat și există deja suport sub formă de căutare a dublurilor complete și similare:

Surse

• metodă adaptivă de optimizare a indecșilor
• utilitarul SQLIndexManager
• prezentare tehnică scurtă a utilitarului SQLIndexManager
• discuție despre utilitarul SQLIndexManager
• SQL Index Manager – Instrument GUI gratuit pentru întreținerea indecșilor pe SQL Server și Azure
• Analiza și optimizarea indecșilor SQL Server

Sursa: habr.com