Prehľad bezplatného nástroja SQLIndexManager

Ako je známe, indexy hrajú dôležitú úlohu v SÚBDS, poskytujú rýchle vyhľadávanie potrebných záznamov. Preto je veľmi dôležité ich pravidelne udržiavať. O analýze a optimalizácii bolo napísaného dosť materiálu, vrátane toho na internete. Napríklad, nedávno bola publikovaná recenzia tejto témy v tejto publikácii.

Existuje množstvo platených aj bezplatných riešení. Napríklad, existuje hotové riešenia., založené na adaptívnej metóde optimalizácie indexov.

Ďalej preskúmame bezplatnú utilitu SQLIndexManager, ktorej autorom je AlanDenton.

Hlavný technický rozdiel medzi SQLIndexManagerom a inými analógmi uvádza sám autor tu a tu.

V tomto článku sa z pohľadu pozrieme na projekt a na možnosti využívania tohto softvérového riešenia.

Diskutujú o tejto utilite tu.
Časom bolo opravených väčšina poznámok a chýb.

Tak prejdime teraz k samotnej utilite SQLIndexManager.

Aplikácia je napísaná v jazyku C# .NET Framework 4.5 vo Visual Studio 2017 a používa DevExpress pre formuláre:

a vyzerá nasledovne:

Všetky dotazy sú generované v nasledujúcich súboroch:

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

Pri pripojení k databáze a odosielaní dotazov do SÚBDS sa aplikácia prihlasuje nasledovne:

ApplicationName="SQLIndexManager"

Pri spustení aplikácie sa otvorí modálne okno na pridanie pripojenia:

Tu zatiaľ nefunguje načítanie úplného zoznamu všetkých inštancií MS SQL Server dostupných v lokálnych sieťach.

Pripojenie možno pridať aj pomocou extrémne ľavej tlačidla na hlavnom menu:

Potom sa spustia nasledujúce dotazy do SÚBDS:

1. Získanie informácií o SÚBDS

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

2. Získanie zoznamu dostupných databáz s ich stručnými vlastnosťami

   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
   

Po vykonaní vyššie uvedených skriptov sa objaví okno obsahujúce stručné informácie o databázach vybraného inštancie MS SQL Server:

Je potrebné poznamenať, že rozšírené informácie sa zobrazujú na základe práv. Ak sú prítomné sysadmin, je možné vybrať údaje z pohľadu sys.master_files. Ak takéto práva nie sú, jednoducho sa vráti menej údajov, aby sa nezpomalenil dopyt.

Tu je potrebné vybrať zaujímavé databázy a kliknúť na tlačidlo „OK“.

Následne sa vykoná nasledujúci skript pre každú vybranú databázu na analýzu stavu indexov:

Analýza stavu indexov

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 = @Fragmentation
        OR
            i.PagesCount > @PreDescribeSize
        OR
            i.IndexType IN (5, 6)
    )

Ako je zrejmé z samotných požiadaviek, často sa používajú dočasné tabuľky. To sa robí, aby nedochádzalo k rekompiláciám, a v prípade veľkej schémy mohol plán generovať paralelne pri vkladaní údajov, pretože vloženie s tabuľkovými premennými je možné len v jednom vlákne.

Po vykonaní vyššie uvedeného skriptu sa zobrazí okno s tabuľkou indexov:

Tu je možné zobraziť aj ďalšie podrobné informácie, ako napríklad:

1. databáza
2. počet sekcií
3. dátum a čas posledného prístupu
4. kompresia
5. súborová skupina

a pod.
Samotné stĺpce je možné nastaviť:

V bunkách stĺpca Fix je možné zvoliť, akú akciu vykonať pri optimalizácii. Po dokončení skenovania sa akcia podľa predvoleného nastavenia vyberá na základe vybraných nastavení:

Je potrebné vybrať požadované indexy na spracovanie.

Pomocou hlavného menu je možné uložiť skript (toto tlačidlo spúšťa aj samotný proces optimalizácie indexov):

ako aj uložiť tabuľku do rôznych formátov (toto tlačidlo umožňuje otvoriť podrobné nastavenia pre analýzu a optimalizáciu indexov):

Informácie je možné aktualizovať aj kliknutím na tretie tlačidlo vľavo v hlavnom menu vedľa lupy.

Tlačidlo s lupou umožňuje vybrať požadované databázy na preskúmanie.

Na aktuálny moment neexistuje plnohodnotný referenčný systém. Preto stlačením tlačidla “?” sa jednoducho otvorí modálne okno obsahujúce základné informácie o softvérovom produkte:

Okrem všetkého vyššie uvedeného je v hlavnom menu aj vyhľadávací panel:

Pri spustení procesu optimalizácie indexov:

Taktiež v spodnej časti okna je možné prezerať protokol vykonávaných akcií:

V okne podrobných nastavení analýzy a optimalizácie indexov je možné nastaviť jemnejšie možnosti:

Žiadosti k aplikácii:

1. umožniť výberové aktualizovanie štatistík nielen pre indexy a rôznymi spôsobmi (úplne aktualizovať alebo čiastočne)
2. umožniť nielen výber DB, ale aj rôznych serverov (to je veľmi pohodlné, keď je veľa inštancií MS SQL Server)
3. pre väčšiu flexibilitu v používaní sa navrhuje obaliť príkazy do knižníc a vyviesť do príkazov PowerShell, ako je to napríklad tu:
4. dbatools.io/commands
5. umožniť uchovávať a meniť osobné nastavenia, a to ako pre celú aplikáciu, tak aj v prípade potreby pre každú inštanciu MS SQL Server a databázu
6. z bodov 2 a 4 vyplýva požiadavka vytvoriť skupiny podľa databáz a skupiny podľa inštancií MS SQL Server, pre ktoré sú nastavenia rovnaké
7. uskutočniť vyhľadávanie duplicitných indexov (úplných a neúplných, ktoré sa len málo líšia, alebo sa líšia len podľa zahrnutých stĺpcov)
8. keďže SQLIndexManager sa používa iba pre RDBMS MS SQL Server, je potrebné to odraziť v názve, napríklad takto: SQLIndexManager for MS SQL Server
9. všetky časti aplikácie, ktoré nie sú GUI, presunúť do samostatných modulov a prepísať na .NET Core 2.1

K okamihu písania tohto článku sa bod 6 z požiadaviek aktívne vyvíja a už existuje podpora vo forme vyhľadávania úplných a podobných duplicit:

Zdroje

• adaptívna metóda optimalizácie indexov
• nástroj SQLIndexManager
• stručný technický prehľad nástroja SQLIndexManager
• diskusia k nástroju SQLIndexManager
• SQL Index Manager – Bezplatný GUI nástroj na údržbu indexov v SQL Server a Azure
• Analýza a optimalizácia indexov SQL Server

Zdroj: habr.com