Panoramica dello strumento gratuito SQLIndexManager

Come è noto, gli indici svolgono un ruolo importante nei DBMS, fornendo una ricerca rapida ai record desiderati. È quindi fondamentale mantenerli in buone condizioni. Sono stati scritti molti materiali sull'analisi e l'ottimizzazione, anche su Internet. Ad esempio, recentemente è stata fatta una panoramica su questo argomento in questa pubblicazione.

Esistono molte soluzioni sia a pagamento che gratuite per questo scopo. Ad esempio, c'è risultato, basata su un metodo adattivo di ottimizzazione degli indici.

Passiamo ora a un'utilità gratuita chiamata SQLIndexManager, la cui autore è AlanDenton.

La principale differenza tecnica tra SQLIndexManager e altri simili è spiegata dallo stesso autore. qui e qui.

In questo articolo, daremo uno sguardo al progetto e alle possibilità di utilizzo di questa soluzione software.

Si discute di questa utilità qui.
Nel tempo, la maggior parte dei commenti e dei bug sono stati risolti.

Quindi, passiamo ora all'utilità SQLIndexManager.

L'applicazione è scritta in C# .NET Framework 4.5 in Visual Studio 2017 e utilizza DevExpress per i form:

e appare come segue:

Tutte le query vengono generate nei seguenti file:

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

Quando ci si connette al database e si inviano query al DBMS, l'applicazione si registra nel seguente modo:

ApplicationName="SQLIndexManager"

All'avvio dell'applicazione si aprirà una finestra modale per aggiungere una connessione:

Attualmente, il caricamento della lista completa di tutte le istanze di MS SQL Server disponibili nelle reti locali non funziona ancora.

È inoltre possibile aggiungere una connessione utilizzando il pulsante più a sinistra nel menu principale:

Successivamente verranno eseguite le seguenti query al DBMS:

1. Ottenere informazioni sul DBMS

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

2. Ottenere l'elenco dei database disponibili con le loro proprietà brevi

   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
   

Dopo aver eseguito gli script sopra indicati, apparirà una finestra contenente informazioni brevi sui database dell'istanza selezionata di MS SQL Server:

È importante notare che le informazioni estese vengono mostrate in base ai permessi. Se ci sono sysadmin, è possibile selezionare i dati dalla vista sys.master_files. In assenza di tali permessi, verranno semplicemente restituiti meno dati per non rallentare la query.

Qui è necessario selezionare i database di interesse e premere il pulsante “OK”.

Successivamente verrà eseguito il seguente script per ciascun database selezionato per analizzare lo stato degli indici:

Analisi dello stato degli indici

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

Come si può vedere dalle stesse query, le tabelle temporanee vengono utilizzate abbastanza frequentemente. Questo viene fatto per evitare ricompilazioni e, in caso di uno schema grande, il piano può essere generato in parallelo durante l'inserimento dei dati, perché l'inserimento con variabili tabellari è possibile solo in un singolo thread.

Dopo l'esecuzione dello script sopra menzionato, apparirà una finestra con la tabella degli indici:

Qui è possibile visualizzare anche altre informazioni dettagliate, come:

1. database
2. numero di sezioni
3. data e ora dell'ultimo accesso
4. compressione
5. gruppo di file

ecc.
Le colonne stesse possono essere configurate:

Nelle celle della colonna Fix è possibile scegliere quale azione verrà eseguita durante l'ottimizzazione. Inoltre, al termine della scansione, l'azione predefinita viene selezionata in base alle impostazioni scelte:

È necessario selezionare gli indici desiderati per l'elaborazione.

Utilizzando il menu principale, è possibile sia salvare lo script (questo stesso pulsante avvia il processo di ottimizzazione degli indici):

sia salvare la tabella in diversi formati (questo stesso pulsante consente di aprire le impostazioni dettagliate per l'analisi e l'ottimizzazione degli indici):

Inoltre, le informazioni possono essere aggiornate facendo clic sul terzo pulsante a sinistra nel menu principale accanto alla lente d'ingrandimento.

Il pulsante con la lente d'ingrandimento consente di selezionare i database desiderati da esaminare.

Attualmente non esiste un sistema di aiuto completo. Pertanto, fare clic sul pulsante “?” aprirà semplicemente una finestra modale contenente le informazioni di base sul prodotto software:

Oltre a quanto descritto sopra, nel menu principale è presente una barra di ricerca:

Durante l'avvio del processo di ottimizzazione degli indici:

Inoltre, nella parte inferiore della finestra è possibile visualizzare il log delle azioni eseguite:

Nella finestra delle impostazioni dettagliate per l'analisi e l'ottimizzazione degli indici è possibile configurare opzioni più fini:

Suggerimenti per l'applicazione:

1. rendere possibile l'aggiornamento selettivo delle statistiche non solo per gli indici e in diversi modi (aggiornare completamente o parzialmente)
2. rendere possibile non solo scegliere i database, ma anche i diversi server (è molto comodo quando ci sono molte istanze di MS SQL Server)
3. per una maggiore flessibilità d'uso si propone di racchiudere i comandi in librerie e di esporli ai comandi PowerShell, come sono stati implementati, ad esempio, qui:
4. dbatools.io/commands
5. rendere possibile salvare e modificare le impostazioni personali sia per l'intera applicazione che, se necessario, per ogni istanza di MS SQL Server e ciascun database
6. dai punti 2 e 4 deriva il desiderio di creare gruppi per database e gruppi per istanze di MS SQL Server, per le quali le impostazioni sono identiche
7. implementare la ricerca di duplicati di indici (completi e parziali, che si differenziano poco o solo per colonne incluse)
8. poiché SQLIndexManager è utilizzato esclusivamente per DBMS MS SQL Server, è necessario rifletterlo nel nome, ad esempio, nel seguente modo: SQLIndexManager per MS SQL Server
9. estrarre tutte le parti dell'applicazione non GUI in moduli separati e riscriverli in .NET Core 2.1

Al momento della scrittura dell'articolo, il punto 6 dei desideri è attivamente sviluppato e c'è già supporto per la ricerca di duplicati completi e simili:

Fonti

• metodo adattivo di ottimizzazione degli indici
• utilità SQLIndexManager
• breve panoramica tecnica dell'utilità SQLIndexManager
• discussione sull'utilità SQLIndexManager
• SQL Index Manager – Strumento GUI gratuito per la manutenzione degli indici su SQL Server e Azure
• Analisi e ottimizzazione degli indici di SQL Server

Fonte: habr.com