Oversigt over det gratis værktøj SQLIndexManager

Som bekendt spiller indekser en vigtig rolle i databaseadministrationssystemer (DBMS) ved at give hurtig adgang til de nødvendige posteringer. Derfor er det så vigtigt at vedligeholde dem rettidigt. Der er skrevet en del materiale om analyse og optimering, herunder online. For eksempel blev der for nylig lavet en anmeldelse af dette emne i i bloggen..

Der findes mange både betalte og gratis løsninger til dette. For eksempel er der en færdig løsning, baseret på en adaptiv metode til optimering af indekser.

Lad os se nærmere på det gratuite værktøj SQLIndexManager, hvis forfatter er AlanDenton.

Den primære tekniske forskel mellem SQLIndexManager og en række andre tilsvarende værktøjer præsenteres af selve forfatteren. her og her.

I denne artikel vil vi også se nærmere på projektet og mulighederne for anvendelse af denne softwareløsning.

Der diskuteres dette værktøj her.
Over tid er størstedelen af bemærkningerne og fejlene blevet rettet.

Lad os nu gå videre til selve værktøjet SQLIndexManager.

Applikationen er skrevet i C# .NET Framework 4.5 i Visual Studio 2017 og bruger DevExpress til formularer:

og ser således ud:

Alle forespørgsler dannes i følgende filer:

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

Ved tilslutning til databasen og ved afsendelse af forespørgsler til DBMS'ens system tilmelder applikationen sig som følger:

ApplicationName="SQLIndexManager"

Når applikationen startes, åbnes et modalvindue til tilføjelse af forbindelse:

Her fungerer opdatering af den komplette liste over alle instanser af MS SQL Server, der er tilgængelige via lokale netværk, stadig ikke.

Man kan også tilføje en forbindelse via den venstre knap i hovedmenuen:

Derefter udføres følgende forespørgsler til DBMS:

1. Hentning af information om DBMS

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

2. Hentning af listen over tilgængelige databaser med deres korte egenskaber

   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
   

Efter udførelsen af de ovennævnte scripts vil der vises et vindue med kort information om databaserne i den valgte instans af MS SQL Server:

Det er værd at bemærke, at udvidede oplysninger vises ud fra rettigheder. Hvis der er sysadmin, kan man vælge data fra visningen sys.master_files. Hvis der ikke er sådanne rettigheder, returneres der blot færre data for ikke at forsinke forespørgslen.

Her skal der vælges de ønskede databaser og trykkes på knappen “OK”.

Dernæst vil følgende script blive udført for hver valgte database for at analysere indeksernes tilstand:

Analyse af indeksernes 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 man kan se fra selve forespørgslerne, bruges midlertidige tabeller ofte. Dette gøres for at undgå rekompileringer, og i tilfælde af en stor skema kan planen genereres parallelt ved indsættelse af data, da indsættelse med tabelvariabler kun er mulig i én tråd.

Efter udførelsen af ovenstående script, vil et vindue med en indextabel vises:

Her kan der også vises andre detaljerede oplysninger, såsom:

1. database
2. antal sektioner
3. dato og tid for sidste adgang
4. komprimering
5. filer gruppe

osv.
Kolonnerne kan selv justeres:

I cellerne i kolonnen Fix kan du vælge, hvilken handling der skal udføres ved optimering. Desuden vælges standardhandlingen ved afslutningen af scanning baseret på de valgte indstillinger:

Det er nødvendigt at vælge de relevante indekser til behandling.

Gennem hovedmenuen kan du både gemme scriptet (den samme knap starter også optimeringsprocessen for indextabeller):

og gemme tabellen i forskellige formater (den samme knap åbner også de detaljerede indstillinger for analyse og optimering af indekser):

Oplysningerne kan også opdateres ved at trykke på den tredje knap til venstre i hovedmenuen ved siden af forstørrelsesglasset.

Knappen med forstørrelsesglasset giver mulighed for at vælge de relevante databaser til gennemgang.

Der er ikke et fuldgyldigt hjælpemiddelsystem i øjeblikket. Derfor vil tryk på knappen "?" blot fremkalde et modalt vindue, der indeholder grundlæggende oplysninger om softwareproduktet:

Udover alt det ovenstående er der en søgelinje i hovedmenuen:

Når optimeringsprocessen for indekserne startes:

Der kan også ses en log over udførte handlinger i bunden af vinduet:

I vinduet med de detaljerede indstillinger for analyse og optimering af indekser kan der justeres mere fine indstillinger:

Ønsker til applikationen:

1. Gøre det muligt at opdatere statistikker selektivt, ikke kun for indekser og også på forskellige måder (opdatere helt eller delvist)
2. Gøre det muligt ikke kun at vælge databaser, men også forskellige servere (det er meget praktisk, når der er mange instanser af MS SQL Server)
3. For større fleksibilitet i brugen foreslås det at pakke kommandoer i biblioteker og præsentere dem i PowerShell-kommandoer, som det er gjort her, for eksempel:
4. dbatools.io/commands
5. muliggøre at gemme og ændre personlige indstillinger både for hele applikationen og om nødvendigt for hver instans af MS SQL Server og hver database
6. afsnit 2 og 4 fører til ønsket om at oprette grupper for databaser og grupper for instanser af MS SQL Server, for hvilke indstillingerne er ens
7. udarbejde en søgning efter dublerede indekser (både komplette og ufuldstændige, som enten kun adskiller sig en smule eller kun adskiller sig i forhold til de inkluderede kolonner)
8. da SQLIndexManager kun bruges til MS SQL Server, er det nødvendigt at afspejle dette i navnet, for eksempel som følgende: SQLIndexManager for MS SQL Server
9. alle dele af applikationen uden GUI skal flyttes til separate moduler og genskrives på .NET Core 2.1

På tidspunktet for skrivningen af denne artikel er punkt 6 fra ønskerne aktivt under udvikling og har allerede support i form af søgning efter komplette og lignende dubletter:

Kilder

• adaptiv metode til optimering af indekser
• værktøjet SQLIndexManager
• kort teknisk oversigt over værktøjet SQLIndexManager
• diskussion vedrørende værktøjet SQLIndexManager
• SQL Index Manager – Gratis GUI-værktøj til indeksvedligeholdelse på SQL Server og Azure
• SQL Server indeksanalyse og optimering

Kilde: habr.com