🥇Overzicht van het gratis hulpprogramma SQLIndexManager

Zoals bekend spelen indexen een belangrijke rol in databasesystemen, omdat ze een snelle zoekfunctie naar de benodigde records bieden. Daarom is het zo belangrijk om ze tijdig te onderhouden. Er is al veel geschreven over analyse en optimalisatie, ook online. Onlangs was er een overzicht van dit onderwerp in deze publicatie.

Er zijn veel betaalde en gratis oplossingen hiervoor. Bijvoorbeeld, er is een kant-en-klaar oplossing, gebaseerd op een adaptieve methode voor indexoptimalisatie.

Laten we nu de gratis tool SQLIndexManager, waarvan de auteur is AlanDenton.

Het belangrijkste technische verschil tussen SQLIndexManager en verschillende andere alternatieven wordt door de auteur zelf aangegeven. hier en hier.

In dit artikel kijken we vanuit een ander perspectief naar het project en de mogelijkheden van deze softwareoplossing.

Deze tool wordt besproken hier.
In de loop der tijd zijn de meeste opmerkingen en bugs opgelost.

Laten we nu verder gaan met de tool SQLIndexManager.

De applicatie is geschreven in C# .NET Framework 4.5 in Visual Studio 2017 en maakt gebruik van DevExpress voor de formulieren:

en ziet er als volgt uit:

Alle verzoeken worden gegenereerd in de volgende bestanden:

Index
Query
QueryEngine
ServerInfo

Bij het verbinden met de database en het verzenden van verzoeken naar het databasesysteem, wordt de applicatie als volgt geregistreerd:

ApplicationName="SQLIndexManager"

Bij het starten van de applicatie verschijnt er een modaal venster voor het toevoegen van een verbinding:

Helaas werkt de volledige lijst van alle MS SQL Server-instanties, die beschikbaar zijn via lokale netwerken, nog niet.

Een verbinding kan ook worden toegevoegd met de meest linkse knop in het hoofdmenu:

Vervolgens worden de volgende verzoeken naar het databasesysteem uitgevoerd:

Verkrijgen van informatie over het databasesysteem

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

Verkrijgen van een lijst van beschikbare databases met hun korte eigenschappen

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

Na het uitvoeren van de bovenstaande scripts verschijnt er een venster met een kort overzicht van de databases van de geselecteerde MS SQL Server-instantie:

Het is vermeldenswaard dat uitgebreide informatie wordt weergegeven op basis van de rechten. Als er sysadmin, kan er data uit de weergave worden geselecteerd sys.master_files. Als deze rechten er niet zijn, wordt er gewoon minder data teruggegeven om de query niet te vertragen.

Hier moeten de gewenste databases worden geselecteerd en moet de knop "OK" worden ingedrukt.

Daarna wordt de volgende script voor elke geselecteerde database uitgevoerd om de status van de indexen te analyseren:

Indexstatusanalyse

declare @Fragmentatie 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.#AllocatieEenheden') IS NOT NULL
    DROP TABLE #AllocatieEenheden

CREATE TABLE #AllocatieEenheden (
      ContainerID   BIGINT PRIMARY KEY
    , GereserveerdePagina's BIGINT NOT NULL
    , GebruiktePagina's     BIGINT NOT NULL
)

INSERT INTO #AllocatieEenheden (ContainerID, GereserveerdePagina's, GebruiktePagina's)
SELECT [container_id]
     , SUM([totaal_paginas])
     , SUM([gebruikte_paginas])
FROM sys.allocatie_eenheden WITH(NOLOCK)
GROUP BY [container_id]
HAVING SUM([totaal_paginas]) BETWEEN @MinIndexSize AND @MaxIndexSize

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

CREATE TABLE #Uitsluitingslijst (ID INT PRIMARY KEY)

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

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

SELECT [object_id]
     , [index_id]
     , [partition_id]
     , [partition_nummer]
     , [rijen]
     , [data_compressie]
INTO #Partities
FROM sys.partities WITH(NOLOCK)
WHERE [object_id] > 255
    AND [rijen] > 0
    AND [object_id] NOT IN (SELECT * FROM #Uitsluitingslijst)

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

CREATE TABLE #Indexen (
      ObjectID         INT NOT NULL
    , IndexID          INT NOT NULL
    , IndexNaam        SYSNAME NULL
    , PaginaAantal     BIGINT NOT NULL
    , OngebruiktePaginaAantal BIGINT NOT NULL
    , PartitionNummer  INT NOT NULL
    , RijenAantal      BIGINT NOT NULL
    , IndexType        TINYINT NOT NULL
    , IsPaginaVergrendelingenToegestaan BIT NOT NULL
    , DataSpaceID      INT NOT NULL
    , DataCompressie    TINYINT NOT NULL
    , IsUniek         BIT NOT NULL
    , IsPK             BIT NOT NULL
    , VulFactorWaarde  INT NOT NULL
    , IsGefilterd       BIT NOT NULL
    , PRIMARY KEY (ObjectID, IndexID, PartitionNummer)
)

INSERT INTO #Indexen
SELECT ObjectID         = i.[object_id]
     , IndexID          = i.index_id
     , IndexNaam        = i.[name]
     , PaginaAantal     = a.GereserveerdePagina's
     , OngebruiktePaginaAantal = CASE WHEN ABS(a.GereserveerdePagina's - a.GebruiktePagina's) > 32 THEN a.GereserveerdePagina's - a.GebruiktePagina's ELSE 0 END
     , PartitionNummer  = p.[partition_nummer]
     , RijenAantal      = ISNULL(p.[rijen], 0)
     , IndexType        = i.[type]
     , IsPaginaVergrendelingenToegestaan = i.[allow_page_locks]
     , DataSpaceID      = i.[data_space_id]
     , DataCompressie    = p.[data_compressie]
     , IsUniek         = i.[is_unique]
     , IsPK             = i.[is_primary_key]
     , VulFactorWaarde  = i.[fill_factor]
     , IsGefilterd       = i.[has_filter]
FROM #AllocatieEenheden a
JOIN #Partities p ON a.ContainerID = p.[partition_id]
JOIN sys.indexen 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 @bestanden TABLE (ID INT PRIMARY KEY)
INSERT INTO @bestanden
SELECT DISTINCT [data_space_id]
FROM sys.database_bestanden WITH(NOLOCK)
WHERE [state] != 0
    AND [type] = 0

IF @@ROWCOUNT > 0 BEGIN

    DELETE FROM i
    FROM #Indexen i
    LEFT JOIN sys.bestemming_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNummer = dds.[destination_id]
    WHERE ISNULL(dds.[data_space_id], i.DataSpaceID) IN (SELECT * FROM @bestanden)

END


DECLARE @DBID   INT
      , @DBNAAM SYSNAME

SET @DBNAAM = DB_NAME()
SELECT @DBID = [database_id]
FROM sys.databases WITH(NOLOCK)
WHERE [name] = @DBNAAM

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

CREATE TABLE #Fragmentatie (
      ObjectID         INT NOT NULL
    , IndexID          INT NOT NULL
    , PartitionNummer  INT NOT NULL
    , Fragmentatie     FLOAT NOT NULL
    , PRIMARY KEY (ObjectID, IndexID, PartitionNummer)
)

INSERT INTO #Fragmentatie (ObjectID, IndexID, PartitionNummer, Fragmentatie)
SELECT i.ObjectID
     , i.IndexID
     , i.PartitionNummer
     , r.[gemiddelde_fragmentatie_in_procent]
FROM #Indexen i
CROSS APPLY sys.dm_db_index_fysieke_stats(@DBID, i.ObjectID, i.IndexID, i.PartitionNummer, 'LIMITED') r
WHERE i.PaginaAantal = @Fragmentatie
        OR
            i.PaginaAantal > @PreDescribeSize
        OR
            i.IndexType IN (5, 6)
    )

Zoals blijkt uit de verzoeken zelf, worden tijdelijke tabellen relatief vaak gebruikt. Dit is gedaan om recompilaties te voorkomen, en in het geval van een groot schema kan het plan parallel worden gegenereerd tijdens het invoegen van gegevens, aangezien invoegen met tabelvariabelen slechts in één stroom mogelijk is.

Na het uitvoeren van het bovenstaande script verschijnt er een venster met een tabel van indexen:

Ook kan hier andere gedetailleerde informatie worden weergegeven, zoals:

database
aantal secties
datum en tijd van de laatste toegang
compressie
bestandsgroep

enz.
De kolommen zelf kunnen worden geconfigureerd:

In de cellen van de kolom Fix kan je kiezen welke actie zal worden uitgevoerd tijdens de optimalisatie. Ook wordt de standaardacties na voltooiing van de scan gekozen op basis van de geselecteerde instellingen:

Je moet de benodigde indexen voor verwerking selecteren.

Via het hoofdmenu kun je zowel het script opslaan (deze knop start ook het optimalisatieproces van indexen):

alsook de tabel in verschillende formaten opslaan (deze knop opent ook gedetailleerde instellingen voor analyse en optimalisatie van indexen):

Ook kan de informatie worden bijgewerkt door op de derde knop links in het hoofdmenu naast de vergrootglas te drukken.

De knop met de vergrootglas maakt het mogelijk de benodigde databases voor beoordeling te selecteren.

Momenteel is er geen volwaardige hulpsysteem. Daarom zal het drukken op de knop “?” gewoon een modaal venster openen met de basisinformatie over het softwareproduct:

Naast alles hierboven beschreven, is er een zoekbalk in het hoofdmenu:

Bij het starten van het proces voor de optimalisatie van indexen:

Ook onderaan het venster kan je het log van uitgevoerde acties bekijken:

In het venster met gedetailleerde instellingen voor analyse en optimalisatie van indexen kunnen fijnere opties worden ingesteld:

Wensen voor de applicatie:

de mogelijkheid bieden om statistieken selectief bij te werken, niet alleen voor indexen en op verschillende manieren (volledig bijwerken of gedeeltelijk)
de mogelijkheid bieden om niet alleen databases te selecteren, maar ook verschillende servers (dit is zeer handig wanneer er veel instanties van MS SQL Server zijn)
voor grotere flexibiliteit in gebruik wordt voorgesteld om opdrachten in bibliotheken te wikkelen en deze naar PowerShell-opdrachten te exporteren, zoals hier bijvoorbeeld is gedaan:
dbatools.io/commands
mogelijk maken om persoonlijke instellingen op te slaan en te wijzigen, zowel voor de gehele applicatie als, indien nodig, voor elke instantie van MS SQL Server en elke database
uit punt 2 en 4 komt de wens voort om groepen voor databases en groepen voor instanties van MS SQL Server te maken, waarvoor de instellingen hetzelfde zijn
zoekdubs de indices (volledige en gedeeltelijke, die slechts licht verschillen of alleen verschillen op basis van opgenomen kolommen)
aangezien SQLIndexManager alleen voor de database MS SQL Server wordt gebruikt, moet dit in de naam worden weerspiegeld, bijvoorbeeld als volgt: SQLIndexManager voor MS SQL Server
verplaats alle niet-GUI-onderdelen van de applicatie naar aparte modules en herschrijf deze in .NET Core 2.1

Op het moment van schrijven van het artikel wordt punt 6 uit de wensen actief ontwikkeld en is er al ondersteuning in de vorm van het zoeken naar volledige en soortgelijke duplicaten:

Bronnen

Bron: habr.com

Overzicht van de gratis tool SQLIndexManager

Bronnen