🥇Oversikt over det gratis verktøyet SQLIndexManager

Som kjent spiller indekser en viktig rolle i DBMS, ettersom de gir rask tilgang til nødvendige poster. Derfor er det viktig å vedlikeholde dem i tide. Det er skrevet mye om analyse og optimalisering, inkludert på nettet. For eksempel ble det nylig gjort en oversikt over dette emnet i denne publikasjonen.

Det finnes mange både betalte og gratis løsninger for dette. For eksempel finnes det en ferdig beslutningen, basert på en adaptiv metode for indeksoptimalisering.

Vi skal nå se nærmere på det gratis verktøyet SQLIndexManager, hvis forfatter er AlanDenton.

Den viktigste tekniske forskjellen mellom SQLIndexManager og flere andre tilsvarende verktøy forklares av selve forfatteren her og her.

I denne artikkelen skal vi se på prosjektet og mulighetene for bruk av denne programvareløsningen.

Verktøyet diskuteres her.
Over tid har de fleste feil og kommentarer blitt rettet.

La oss nå gå videre til selve verktøyet SQLIndexManager.

Applikasjonen er skrevet i C# .NET Framework 4.5 i Visual Studio 2017 og bruker DevExpress for skjemaene:

og ser slik ut:

Alle forespørslene genereres i følgende filer:

Index
Query
QueryEngine
ServerInfo

Når du kobler til databasen og sender forespørslene til DBMS, registreres applikasjonen som følger:

ApplicationName=”SQLIndexManager”

Ved oppstart av applikasjonen vil et modalt vindu for å opprette tilkobling åpne seg:

Her fungerer ikke innlastingen av den komplette listen over alle MS SQL Server-instansene som er tilgjengelige over lokale nettverk enda.

Det er også mulig å legge til en tilkobling ved hjelp av den ytterste venstre knappen i hovedmenyen:

Deretter vil følgende forespørsel kjøres mot DBMS:

Hente informasjon om DBMS

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

Hente listen over tilgjengelige databaser med deres korte egenskaper

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

Etter å ha kjørt skriptene nevnt ovenfor, vil et vindu vises med en kort informasjon om databasene til den valgte MS SQL Server-instansen:

Det er viktig å merke seg at den utvidede informasjonen vises basert på rettighetene. Hvis man har sysadmin, kan man velge data fra visningen sys.master_files. Hvis slike rettigheter ikke finnes, returneres det bare færre data for ikke å forsinke forespørselen.

Her må man velge de ønskede databasene og klikke på knappen “OK”.

Deretter vil det bli kjørt følgende skript for hver valgt database for å analysere tilstanden til indeksene:

Analyse av indeksens tilstand

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

Som viser seg av selve forespørslene, brukes midlertidige tabeller ganske ofte. Dette er gjort for å unngå rekompileringer, og i tilfelle av en stor skjema kan en plan genereres parallelt ved innsending av data, fordi innsending med tabellvariabler bare kan skje i én tråd.

Etter å ha kjørt det ovennevnte skriptet, vil et vindu med tabellen over indekser dukke opp:

Her kan man også vise annen detaljert informasjon, slik som:

database
antall seksjoner
dato og tidspunkt for siste tilgang
komprimering
filer gruppe

og så videre.
Selve kolonnene kan tilpasses:

I cellene i kolonnen Fix kan du velge hvilken handling som skal utføres ved optimalisering. Også ved fullføring av skanningen velges standardhandling basert på valgte innstillinger:

Det er nødvendig å velge de aktuelle indeksene for behandling.

Via hovedmenyen kan du både lagre skriptet (denne knappen starter også prosessen med å optimalisere indeksene):

samt lagre tabellen i forskjellige formater (denne knappen åpner også detaljerte innstillinger for analyse og optimalisering av indekser):

Informasjonen kan også oppdateres ved å klikke på den tredje knappen til venstre i hovedmenyen, ved siden av forstørrelsesglasset.

Knappen med forstørrelsesglass lar deg velge de nødvendige databasene for vurdering.

For øyeblikket finnes det ingen fullstendig hjelpefunksjon. Derfor vil trykk på knappen "?" bare føre til at et modalt vindu vises med grunnleggende informasjon om programvaren:

I tillegg til alt det ovennevnte, finnes det en søkefelt i hovedmenyen:

Når prosessen med å optimalisere indekser startes:

Nederst i vinduet kan du også se loggen over utførte handlinger:

I vinduet for detaljerte innstillinger for analyse og optimalisering av indekser kan du justere mer presise alternativer:

Ønsker til applikasjonen:

gjøre det mulig å selektivt oppdatere statistikker ikke bare for indekser, men også på forskjellige måter (oppdatere fullt eller delvis)
gjøre det mulig ikke bare å velge DB, men også forskjellige servere (dette er veldig praktisk når det er mange forekomster av MS SQL Server)
for større fleksibilitet i bruken foreslås det å pakke kommandoer inn i biblioteker, og vise dem i PowerShell kommandoer, slik som det er gjort her:
dbatools.io/commands
gjere det mulig å lagre og endre personlige innstillinger både for hele applikasjonen og, etter behov, for hver instans av MS SQL Server og hver database.
Fra punktene 2 og 4 følger det et ønske om å lage grupper for databaser og grupper for instanser av MS SQL Server, der innstillingene er like.
lage søkefunksjon for dupliserte indekser (både komplette og ufullstendige, som enten er lite forskjellige eller kun skiller seg ut med kolonner som er inkludert).
siden SQLIndexManager kun brukes for databaser som MS SQL Server, må dette reflekteres i navnet, for eksempel som: SQLIndexManager for MS SQL Server.
alle deler av applikasjonen som ikke er GUI skal trekkes ut i separate moduler og skrives om til .NET Core 2.1.

På tidspunktet for denne artikkelen er punkt 6 av ønskene aktivt under utvikling og det er allerede støtte for søk av komplette og lignende duplikater:

Kilder

Kilde: habr.com