Přehled bezplatného nástroje SQLIndexManager

Jak je známo, indexy hrají důležitou roli v databázových systémech, protože umožňují rychlé vyhledávání požadovaných záznamů. Proto je důležité je pravidelně udržovat. O analýze a optimalizaci bylo napsáno hodně materiálu, včetně toho na internetu. Například nedávno byl proveden přehled této problematiky v tento příspěvek.

Existuje mnoho jak placených, tak bezplatných řešení pro tento účel. Například existuje hotové rozhodnutí, založené na adaptivní metodě optimalizace indexů.

Dále se podíváme na bezplatnou utilitu SQLIndexManager, jejímž autorem je AlanDenton.

Hlavní technický rozdíl mezi SQLIndexManager a řadou dalších analogů poukazuje sám autor zde a zde.

V tomto článku se podíváme na projekt a na možnosti využití tohoto softwarového řešení.

Diskutují o této utilitě zde.
S časem byla většina poznámek a chyb opravena.

Tak tedy přejdeme nyní k samotné utilitě SQLIndexManager.

Aplikace je napsána v jazyce C# .NET Framework 4.5 ve Visual Studio 2017 a používá DevExpress pro formuláře:

a vypadá následovně:

Všechny dotazy jsou generovány v následujících souborech:

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

Při připojení k databázi a odesílání dotazů do databázového systému se aplikace přihlašuje následovně:

ApplicationName=”SQLIndexManager”

Při spuštění aplikace se otevře modální okno pro přidání připojení:

Zatím není možné načíst úplný seznam všech instancí MS SQL Server dostupných v lokálních sítích.

Také můžete přidat připojení pomocí krajní levé tlačítka v hlavním menu:

Následně se provedou následující dotazy do databázového systému:

1. Získání informací o databázovém systému

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

2. Získání seznamu dostupných databází s jejich stručnými vlastnostmi

   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 provedení výše uvedených skriptů se zobrazí okno obsahující stručné informace o databázích vybrané instance MS SQL Server:

Je třeba poznamenat, že rozšířené informace se zobrazují na základě práv. Pokud existují sysadmin, lze vybírat data z pohledu sys.master_files. Pokud taková práva nejsou, vrátí se jen méně dat, aby se nezpomalil dotaz.

Zde je nutné vybrat zajímavé databáze a stisknout tlačítko „OK“.

Dále bude proveden následující skript pro každou vybranou databázi k analýze stavu indexů:

Analýza stavu indexů

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

Jak je patrné z samotných dotazů, dočasné tabulky se často používají. To se děje, aby nedocházelo k rekompilacím, a v případě velkého schématu mohl být plán generován paralelně při vkládání dat, protože vkládání s tabulkovými proměnnými je možné pouze v jednom vlákně.

Po provedení výše uvedeného skriptu se zobrazí okno s tabulkou indexů:

Zde lze také zobrazit další podrobné informace, jako je:

1. databáze
2. počet sekcí
3. datum a čas posledního přístupu
4. komprese
5. souborová skupina

atd.
Sloupce lze přizpůsobit:

V buňkách sloupce Fix můžete vybrat, jaké akce budou provedeny při optimalizaci. Kromě toho je akce po dokončení skenování vybrána na základě zvolených nastavení:

Je třeba zvolit potřebné indexy k zpracování.

Hlavní nabídka umožňuje jak uložit skript (toto tlačítko také spouští samotný proces optimalizace indexů):

také uložit tabulku do různých formátů (toto tlačítko umožňuje otevřít podrobné nastavení pro analýzu a optimalizaci indexů):

Také lze informace aktualizovat kliknutím na třetí tlačítko vlevo v hlavní nabídce vedle lupy.

Tlačítko s lupou umožňuje vybrat potřebné databáze k posouzení.

V současné době neexistuje plnohodnotný systém nápovědy. Proto kliknutí na tlačítko „?“ pouze způsobí zobrazení modálního okna obsahujícího základní informace o softwarovém produktu:

Kromě všeho výše uvedeného je v hlavní nabídce také vyhledávací řádek:

Při spuštění procesu optimalizace indexů:

Také ve spodní části okna lze zobrazit protokol prováděných akcí:

V okně podrobných nastavení analýzy a optimalizace indexů lze nastavit jemnější možnosti:

Přání k aplikaci:

1. umožnit selektivně obnovit statistiky nejen pro indexy, ale i různými způsoby (úplně obnovit nebo částečně)
2. umožnit vybírat nejen databáze, ale i různé servery (to je velmi pohodlné, když je mnoho instance MS SQL Server)
3. pro větší flexibilitu v používání se navrhuje zabalit příkazy do knihoven a vyvést je do příkazů PowerShell, jak je to například zde:
4. dbatools.io/commands
5. umožnit ukládání a změnu osobních nastavení jak pro celou aplikaci, tak v případě potřeby pro každou instanci MS SQL Server a každou databázi
6. z bodu 2 a 4 vyplývá žádost vytvořit skupiny podle databází a skupiny podle instancí MS SQL Server, pro které jsou nastavení identická
7. provést vyhledávání duplicitních indexů (úplných a neúplných, které se liší jen málo, nebo se liší pouze podle zahrnutých sloupců)
8. jelikož SQLIndexManager je určen pouze pro SGBD MS SQL Server, je třeba to reflektovat v názvu, například následujícím způsobem: SQLIndexManager for MS SQL Server
9. všechny části aplikace, které nemají GUI, přesunout do samostatných modulů a přepsat na .NET Core 2.1

V době psaní článku se bod 6 z požadavků aktivně vyvíjí a již existuje podpora v podobě vyhledávání úplných a podobných duplicit:

Zdroje

• adaptivní metoda optimalizace indexů
• nástroj SQLIndexManager
• stručný technický přehled nástroje SQLIndexManager
• diskuze k nástroji SQLIndexManager
• SQL Index Manager – Bezplatný GUI nástroj pro údržbu indexů na SQL Serveru a Azure
• Analýza a optimalizace indexů SQL Serveru

Zdroj: habr.com