Översikt av det kostnadsfria verktyget SQLIndexManager

Som vi vet spelar index en viktig roll i databashanteringssystem (DBMS) genom att möjliggöra snabb sökning av nödvändiga poster. Därför är det viktigt att underhålla dem i tid. Det har skrivits mycket om analys och optimering, både i litteraturen och på internet. Till exempel gjordes nyligen en översyn av detta ämne i denna publikation.

Det finns många både betalda och gratis lösningar för detta. Till exempel finns det en färdig beslutet, baserad på en adaptiv metod för indexoptimering.

Låt oss nu titta på det kostnadsfria verktyget SQLIndexManager, av AlanDenton.

Huvudskillnaden i teknik mellan SQLIndexManager och andra liknande verktyg anges av själva författaren. här och här.

I denna artikel kommer vi att se på projektet och dess möjligheter ur ett annat perspektiv.

Denna applikation diskuteras här.
Under tiden har de flesta av kommentarerna och buggarna blivit åtgärdade.

Låt oss nu gå vidare till själva verktyget SQLIndexManager.

Applikationen är skriven i C# .NET Framework 4.5 i Visual Studio 2017 och använder DevExpress för formulär:

och ser ut som följer:

Alla frågor genereras i följande filer:

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

Vid anslutning till databasen och skickande av frågor till DBMS kopplar applikationen upp sig med följande:

ApplicationName="SQLIndexManager"

Vid start av applikationen öppnas ett modalt fönster för att lägga till en anslutning:

Här fungerar inte inladdningen av hela listan över alla MS SQL Server-exemplar som är tillgängliga över lokala nätverk ännu.

Man kan också lägga till en anslutning med hjälp av den vänstra knappen i huvudmenyn:

Därefter kommer följande frågor att köras mot DBMS:

1. Hämta information om DBMS

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

2. Hämta lista över tillgängliga databaser med korta 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
   

Efter att ovanstående skript har körts, kommer ett fönster att visas med kort information om databaserna i det valda MS SQL Server-exemplaret:

Det är värt att notera att den utökade informationen visas baserat på behörigheter. Om det finns sysadmin, kan man välja data från vy sys.master_files. Om sådana rättigheter inte finns, returneras bara mindre data för att inte fördröja frågan.

Här måste man välja de intressanta databaserna och klicka på knappen "OK".

Därefter kommer följande skript att köras för varje vald databas för att analysera indexens tillstånd:

Analys av indexens tillstånd

deklarera @Fragmentation float=15;
deklarera @MinIndexSize bigint=768;
deklarera @MaxIndexSize bigint=1048576;
deklarera @PreDescribeSize bigint=32768;

SÄTT NOCOUNT PÅ
SÄTT ARITHABORT PÅ
SÄTT NUMERIC_ROUNDABORT AV

OM OBJECT_ID('tempdb.dbo.#AllocationUnits') ÄR INTE NULL
    DROPA TABLE #AllocationUnits

SKAPA TABLE #AllocationUnits (
      ContainerID   BIGINT PRIMÄR NYCKEL
    , ReservedPages BIGINT INTE NULL
    , UsedPages     BIGINT INTE NULL
)

INFÖR #AllocationUnits (ContainerID, ReservedPages, UsedPages)
VÄLJ [container_id]
     , SUM([total_pages])
     , SUM([used_pages])
FRÅN sys.allocation_units MED(NOLOCK)
GRUPPA EFTER [container_id]
HAVING SUM([total_pages]) MELLAN @MinIndexSize OCH @MaxIndexSize

OM OBJECT_ID('tempdb.dbo.#ExcludeList') ÄR INTE NULL
    DROPA TABLE #ExcludeList

SKAPA TABLE #ExcludeList (ID INT PRIMÄR NYCKEL)

INFÖR #ExcludeList
VÄLJ [object_id]
FRÅN sys.objects MED(NOLOCK)
VAR [typ] IN ('V', 'U')
    OCH ( [is_ms_shipped] = 1 )

OM OBJECT_ID('tempdb.dbo.#Partitions') ÄR INTE NULL
    DROPA TABLE #Partitions

VÄLJ [object_id]
     , [index_id]
     , [partition_id]
     , [partition_number]
     , [rows]
     , [data_compression]
INTO #Partitions
FRÅN sys.partitions MED(NOLOCK)
VAR [object_id] > 255
    OCH [rows] > 0
    OCH [object_id] IN (VÄLJ * FRÅN #ExcludeList)

OM OBJECT_ID('tempdb.dbo.#Indexes') ÄR INTE NULL
    DROPA TABLE #Indexes

SKAPA TABLE #Indexes (
      ObjectID         INT INTE NULL
    , IndexID          INT INTE NULL
    , IndexName        SYSNAME NULL
    , PagesCount       BIGINT INTE NULL
    , UnusedPagesCount BIGINT INTE NULL
    , PartitionNumber  INT INTE NULL
    , RowsCount        BIGINT INTE NULL
    , IndexType        TINYINT INTE NULL
    , IsAllowPageLocks BIT INTE NULL
    , DataSpaceID      INT INTE NULL
    , DataCompression  TINYINT INTE NULL
    , IsUnique         BIT INTE NULL
    , IsPK             BIT INTE NULL
    , FillFactorValue  INT INTE NULL
    , IsFiltered       BIT INTE NULL
    , PRIMÄR NYCKEL (ObjectID, IndexID, PartitionNumber)
)

INFÖR #Indexes
VÄLJ ObjectID         = i.[object_id]
     , IndexID          = i.index_id
     , IndexName        = i.[name]
     , PagesCount       = a.ReservedPages
     , UnusedPagesCount = FALL NÄR ABS(a.ReservedPages - a.UsedPages) > 32 DÅ a.ReservedPages - a.UsedPages ANNARS 0 SLUT
     , 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]
FRÅN #AllocationUnits a
GÅ MED #Partitions p PÅ a.ContainerID = p.[partition_id]
GÅ MED sys.indexes i MED(NOLOCK) PÅ i.[object_id] = p.[object_id] OCH p.[index_id] = i.[index_id] 
VAR i.[type] IN (0, 1, 2, 5, 6)
    OCH i.[object_id] > 255

DEKLARERA @files TABLE (ID INT PRIMÄR NYCKEL)
INFÖR @files
VÄLJ DISTINKT [data_space_id]
FRÅN sys.database_files MED(NOLOCK)
VAR [state] != 0
    OCH [type] = 0

OM @@ROWCOUNT > 0 BÖRJA

    RADERA FRÅN i
    FRÅN #Indexes i
    VÄNSTER GÅ MED sys.destination_data_spaces dds MED(NOLOCK) PÅ i.DataSpaceID = dds.[partition_scheme_id] OCH i.PartitionNumber = dds.[destination_id]
    VAR ISNULL(dds.[data_space_id], i.DataSpaceID) IN (VÄLJ * FRÅN @files)

SLUT


DEKLARERA @DBID   INT
      , @DBNAME SYSNAME

SÄTT @DBNAME = DB_NAME()
VÄLJ @DBID = [database_id]
FRÅN sys.databases MED(NOLOCK)
VAR [name] = @DBNAME

OM OBJECT_ID('tempdb.dbo.#Fragmentation') ÄR INTE NULL
    DROPA TABLE #Fragmentation

SKAPA TABLE #Fragmentation (
      ObjectID         INT INTE NULL
    , IndexID          INT INTE NULL
    , PartitionNumber  INT INTE NULL
    , Fragmentation    FLOAT INTE NULL
    , PRIMÄR NYCKEL (ObjectID, IndexID, PartitionNumber)
)

INFÖR #Fragmentation (ObjectID, IndexID, PartitionNumber, Fragmentation)
VÄLJ i.ObjectID
     , i.IndexID
     , i.PartitionNumber
     , r.[avg_fragmentation_in_percent]
FRÅN #Indexes i
CROSS APPLY sys.dm_db_index_physical_stats(@DBID, i.ObjectID, i.IndexID, i.PartitionNumber, 'LIMITED') r
VAR i.PagesCount = @Fragmentation
        ELLER
            i.PagesCount > @PreDescribeSize
        ELLER
            i.IndexType IN (5, 6)
    )

Som det framgår av själva förfrågningarna används tillfälliga tabeller ganska ofta. Detta görs för att undvika re-kompileringar, och i händelse av stora scheman kan planen genereras parallellt vid datainsättning, eftersom insättning med tabellvariabler endast är möjlig i en process.

Efter att ha kört det ovanstående skriptet kommer ett fönster med en index-tabell att visas:

Här kan även annan detaljerad information visas, såsom:

1. databas
2. antal sektioner
3. datum och tid för senaste åtgärd
4. kompression
5. filgrupp

och så vidare.
Själva kolumnerna kan anpassas:

I cellerna i kolumnen Fix kan du välja vilken åtgärd som ska utföras vid optimering. Dessutom väljs standardåtgärden efter avslutad skanning baserat på valda inställningar:

Det är nödvändigt att välja de index som ska bearbetas.

Med hjälp av huvudmenyn kan du både spara skriptet (denna knapp startar också själva process för indexoptimering):

eller spara tabellen i olika format (denna knapp ger också möjlighet att öppna detaljerade inställningar för analys och optimering av index):

Informationen kan också uppdateras genom att klicka på den tredje knappen till vänster i huvudmenyn bredvid förstoringsglaset.

Knappen med förstoringsglaset låter dig välja de databaser som ska granskas.

För närvarande finns det inget fullständigt hjälp-system. Därför kommer ett klick på knappen “?” bara att visa ett modalfönster som innehåller grundinformation om programprodukten:

Utöver allt ovanstående finns det en sökrad i huvudmenyn:

Vid start av indexoptimeringsprocessen:

Du kan också se loggen över utförda åtgärder längst ner i fönstret:

I fönstret för detaljerade inställningar av analys och optimering av index kan du justera mer fina alternativ:

Önskemål om applikationen:

1. göra det möjligt att selektivt uppdatera statistik inte bara för index utan också på olika sätt (uppdatera helt eller delvis)
2. göra det möjligt att välja inte bara databaser utan också olika servrar (detta är mycket praktiskt när man har många instanser av MS SQL Server)
3. för större flexibilitet i användningen föreslås det att kapsla in kommandon i bibliotek och presentera dem som PowerShell-kommandon, som det görs här:
4. dbatools.io/commands
5. möjliggöra att spara och ändra personliga inställningar både för hela applikationen och om det behövs för varje instans av MS SQL Server och varje databas
6. från punkterna 2 och 4 följer önskemålet att skapa grupper för databaser och grupper för instanser av MS SQL Server, för vilka inställningarna är desamma
7. utföra en sökning efter dubbletter av index (hela och ofullständiga, som antingen skiljer sig lite eller skiljer sig endast åt avseende inkluderade kolumner)
8. eftersom SQLIndexManager endast används för databashanteringssystemet MS SQL Server, bör detta återspeglas i namnet, till exempel på följande sätt: SQLIndexManager för MS SQL Server
9. alla delar av applikationen som inte är GUI bör flyttas till separata moduler och skrivas om i .NET Core 2.1

Vid tidpunkten för skrivandet av artikeln arbetas punkt 6 i önskemålen aktivt med och det finns redan stöd i form av sökning efter hela och liknande dubbletter:

Källor

• adaptiv metod för att optimera index
• verktyget SQLIndexManager
• kort teknisk översikt av verktyget SQLIndexManager
• diskussion om verktyget SQLIndexManager
• SQL Index Manager – Gratis GUI-verktyg för indexunderhåll på SQL Server och Azure
• SQL Server Indexanalys och optimering

Källa: habr.com