Обзор бесплатного инструмента SQLIndexManager

Kot je znano, indeksi igrajo pomembno vlogo v RDBMS, saj omogočajo hitro iskanje potrebnih zapisov. Zato je tako pomembno, da jih redno vzdržujemo. O analizi in optimizaciji je napisanih precej materialov, vključno z internetom. Na primer, nedavno je bil narejen pregled te teme v te publikacije.

Obstaja veliko plačljivih in brezplačnih rešitev za to. Na primer, obstaja pripravljena odločitev, ki temelji na prilagodljivi metodi optimizacije indeksov.

Nadalje bomo pregledali brezplačno orodje SQLIndexManager, katere avtor je AlanDenton.

Glavna tehnična razlika med SQLIndexManager in številnimi drugimi analogi izhaja od samega avtorja. ovde in ovde.

V tem članku se bomo posvetili projektu in možnosti uporabe tega programske rešitve.

O tem orodju razpravljajo ovde.
Sčasoma je bila večina pripomb in napak odpravljena.

Torej, preidimo zdaj k samemu orodju SQLIndexManager.

Aplikacija je napisana v jeziku C# .NET Framework 4.5 v Visual Studio 2017 in uporablja DevExpress za oblike:

in izgleda takole:

Vsi poizvedbe se oblikujejo v naslednjih datotekah:

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

Pri povezovanju z bazo podatkov in pošiljanju poizvedb RDBMS aplikacija podpira naslednje:

ApplicationName=”SQLIndexManager”

Ob zagonu aplikacije se bo odprlo modalno okno za dodajanje povezave:

Trenutno ne deluje naložitev celotnega seznama vseh instanc MS SQL Server, dostopnih prek lokalnih omrežij.

Povezavo lahko dodate tudi s skrajno levo tipko v glavnem meniju:

Nato se bodo zagnale naslednje poizvedbe RDBMS:

1. Pridobivanje informacij o RDBMS

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

2. Pridobivanje seznama razpoložljivih baz podatkov z njihovimi kratkimi lastnostmi

   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
   

Po izvedbi zgoraj navedenih skriptov se bo odprlo okno, ki vsebuje kratke informacije o bazah podatkov izbranega primera MS SQL Server:

Omeniti je treba, da se razširjene informacije prikazujejo glede na pravice. Če obstajajo sysadmin, lahko izberete podatke iz pogleda sys.master_files. Če takih pravic ni, se vrnejo le manjši podatki, da se ne upočasni poizvedba.

Tukaj je potrebno izbrati zanimive podatkovne baze in pritisniti gumb “OK”.

Nato bo za vsako izbrano podatkovno bazo izveden naslednji skript za analizo stanja indeksov:

Analiza stanja indeksov

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

Kot je razvidno iz samih poizvedb, se začasne tabele pogosto uporabljajo. To je storjeno, da se izognemo rekompilacijam, in v primeru velike sheme lahko načrt generira hkrati med vstavljanjem podatkov, saj je vstavljanje s tabelarnimi spremenljivkami možno le v enem toku.

Po izvedbi zgoraj omenjenega skripta se bo pojavilo okno s tabelo indeksov:

Tukaj je tudi mogoče prikazati druge podrobne informacije, kot so:

1. baza podatkov
2. število sekcij
3. datum in čas zadnjega dostopa
4. itd.) od 10% do 100%.
5. datotečna skupina

itd.
Same stolpce je mogoče prilagoditi:

V celicah stolpca Fix lahko izberete, katero dejanje bo opravljeno pri optimizaciji. Prav tako se po zaključku skeniranja privzeto dejanje izbere na podlagi izbranih nastavitev:

Potrebno je izbrati potrebne indekse za obdelavo.

Z glavnim menijem lahko shranite skript (ta ista gumb zažene tudi postopek optimizacije indeksov):

prav tako lahko shranite tabelo v različne formate (ta ista gumb omogoča dostop do podrobnih nastavitev za analizo in optimizacijo indeksov):

Informacije lahko tudi osvežite s klikom na tretji gumb levo v glavnem meniju poleg povečevalnega stekla.

Gumb z povečevalnim steklom vam omogoča izbiro potrebnih baz podatkov za obravnavo.

Trenutno ni popolne pomoči. Zato bo klik na gumb “?” preprosto prikazal modalno okno, ki vsebuje osnovne informacije o programski opremi:

Poleg zgoraj opisanega je v glavnem meniju tudi iskalna vrstica:

Pri zagonu postopka optimizacije indeksov:

Prav tako lahko na dnu okna pregledate dnevnik opravljenih dejanj:

V oknu podrobnih nastavitev analize in optimizacije indeksov lahko nastavite bolj fine možnosti:

Priporočila za aplikacijo:

1. omogočiti selektivno posodabljanje statistik ne le za indekse, temveč tudi na različne načine (popolno ali delno posodabljanje)
2. omogočiti ne le izbiro DB, temveč tudi različne strežnike (to je zelo priročno, ko je veliko primerkov MS SQL Serverja)
3. za večjo prilagodljivost pri uporabi se predlaga ovijanje ukazov v knjižnice in izpeljati v ukaze PowerShell, kot je to storjeno tukaj:
4. dbatools.io/commands
5. omogočiti shranjevanje in spreminjanje osebnih nastavitev tako za celotno aplikacijo kot po potrebi za vsako instanco MS SQL Server in vsako bazo podatkov
6. iz točk 2 in 4 izhaja želja ustvariti skupine po bazah podatkov in skupine po instancah MS SQL Server, za katere so nastavitve enake
7. izvesti iskanje podvojenih indeksov (polnih in nepopolnih, ki se bodisi malo razlikujejo bodisi razlikujejo le po vključenih stolpcih)
8. ker se SQLIndexManager uporablja izključno za SGBD MS SQL Server, je potrebno to odražati v imenu, na primer na naslednji način: SQLIndexManager for MS SQL Server
9. vse ne-GUI dele aplikacije premakniti v ločene module in prepisati na .NET Core 2.1

Ob trenutku pisanja članka se točka 6 iz želja aktivno razvija in že ima podporo v obliki iskanja polnih in podobnih podvojenih:

Izvori

• adaptivna metoda optimizacije indeksov
• orodje SQLIndexManager
• krajši tehnični pregled orodja SQLIndexManager
• razprava o orodju SQLIndexManager
• SQL Index Manager – Brezplačno GUI orodje za vzdrževanje indeksov na SQL Server in Azure
• Analiza in optimizacija indeksov SQL Server

Izvor: habr.com