Pregled besplatnog alata SQLIndexManager

Kao što je poznato, indeksi igraju važnu ulogu u DBMS-u, omogućujući brzo pretraživanje potrebnih zapisa. Zbog toga je važno da ih pravodobno održavamo. O analizi i optimizaciji napisano je mnogo materijala, uključujući i na Internetu. Na primjer, nedavno je napravljena retrospektiva ove teme u ovoj publikaciji.

Postoji mnoštvo kako plaćenih, tako i besplatnih rješenja za to. Na primjer, postoji gotovo rješenje, zasnovano na adaptivnoj metodi optimizacije indeksa.

Dalje ćemo razmotriti besplatnu alatku SQLIndexManager, čiji je autor AlanDenton.

Osnovna tehnička razlika između SQLIndexManager i niza drugih analoga dolazi iz same autorove perspektive. ovdje i ovdje.

U ovom članku ćemo pogledati projekt i mogućnosti korištenja ovog softverskog rješenja.

Ova alatka se raspravlja o ovdje.
S vremenom su najveći dio komentara i bugova ispravljeni.

Dakle, prijeđimo sada na sam alat SQLIndexManager.

Aplikacija je napisana u jeziku C# .NET Framework 4.5 u Visual Studio 2017 i koristi DevExpress za obrasce:

Pregled besplatnog alata SQLIndexManager

i izgleda ovako:

Pregled besplatnog alata SQLIndexManager

Svi upiti se formiraju u sljedećim datotekama:

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

Pregled besplatnog alata SQLIndexManager

Prilikom povezivanja s bazom podataka i slanja upita DBMS-u, aplikacija se prijavljuje na sljedeći način:

ApplicationName="SQLIndexManager"

Prilikom pokretanja aplikacije otvorit će se modalni prozor za dodavanje veze:
Pregled besplatnog alata SQLIndexManager

Ovdje još uvijek ne radi učitavanje cijelog popisa svih instanci MS SQL Server-a dostupnih putem lokalnih mreža.

Vezu također možete dodati pomoću krajnje lijeve tipke na glavnom izborniku:

Pregled besplatnog alata SQLIndexManager

Nakon toga pokrenut će se sljedeći upiti prema DBMS-u:

  1. Dobivanje informacija o DBMS-u
    SELECT ProductLevel = SERVERPROPERTY('ProductLevel')
         , Edition = SERVERPROPERTY('Edition')
         , ServerVersion = SERVERPROPERTY('ProductVersion')
         , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT)
    

  2. Dobivanje popisa dostupnih baza podataka s njihovim kratkim svojstvima
    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
    

Nakon izvršavanja gorenavedenih skripti, otvorit će se prozor s kratkim informacijama o bazama podataka odabrane instance MS SQL Server-a:

Pregled besplatnog alata SQLIndexManager

Važno je napomenuti da se proširene informacije prikazuju prema pravima. Ako postoji sysadmin, možete odabrati podatke iz prikaza sys.master_files. Ako takva prava ne postoje, vraća se manje podataka kako ne bi usporilo upit.

Ovdje je potrebno odabrati zanimljive baze podataka i pritisnuti gumb "OK".

Sljedeći skript bit će izvršen za svaku odabranu bazu podataka radi analize stanja indeksa:

Analiza stanja indeksa

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

Kao što se može vidjeti iz samih zahtjeva, privremene tablice se često koriste. To je učinjeno kako bi se izbjegle recompilacije i, u slučaju velike sheme, plan bi se mogao generirati paralelno prilikom umetanja podataka, jer je umetanje s tabličnim varijablama moguće samo u jednom toku.

Nakon izvršavanja gornjeg skripta pojavit će se prozor s tablicom indeksa:

Pregled besplatnog alata SQLIndexManager

Također ovdje možete prikazati i druge detaljne informacije, poput:

  1. baze podataka
  2. broj sekcija
  3. datum i vreme zadnjeg pristupa
  4. kompresija
  5. datotečna grupa

itd.
Stupce je moguće konfigurirati:

Pregled besplatnog alata SQLIndexManager

U ćelijama kolone Fix možete odabrati koja će akcija biti izvršena prilikom optimizacije. Također, nakon završetka skeniranja akcija po defaultu odabire se na temelju odabranih postavki:

Pregled besplatnog alata SQLIndexManager

Potrebno je odabrati potrebne indekse za obradu.

Pomoću glavnog izbornika možete sačuvati skriptu (ova tipka također pokreće proces optimizacije indeksa):

Pregled besplatnog alata SQLIndexManager

kao i sačuvati tablicu u različitim formatima (ova tipka omogućuje otvaranje detaljnih postavki za analizu i optimizaciju indeksa):

Pregled besplatnog alata SQLIndexManager

Također, informacije možete ažurirati pritiskom na treću tipku s lijeve strane u glavnom izborniku pored povećala.

Tipka s povećalom omogućuje odabir potrebnih baza podataka za razmatranje.

Trenutno ne postoji potpuna referentna pomoć. Stoga, pritisak na tipku “?” jednostavno će otvoriti modalni prozor koji sadrži osnovne informacije o proizvodu:

Pregled besplatnog alata SQLIndexManager

Osim svega gore opisanog, u glavnom izborniku nalazi se linija za pretraživanje:

Pregled besplatnog alata SQLIndexManager

Kada se pokrene proces optimizacije indeksa:

Pregled besplatnog alata SQLIndexManager

Također, na dnu prozora možete pregledati log izvršenih radnji:

Pregled besplatnog alata SQLIndexManager

U prozoru detaljnih postavki analize i optimizacije indeksa moguće je postaviti finije opcije:

Pregled besplatnog alata SQLIndexManager

Želje za aplikacijom:

  1. omogućiti selektivno ažuriranje statistika ne samo za indekse, već i na različite načine (puni ili djelomični ažuriranje)
  2. omogućiti ne samo odabir BDP-a, već i različitih poslužitelja (to je vrlo zgodno kada postoji mnogo instanci MS SQL Servera)
  3. za veću fleksibilnost u korištenju preporučuje se omotati naredbe u biblioteke i izvesti u PowerShell naredbe, kao što je to učinjeno, na primjer, ovdje:
  4. dbatools.io/commands
  5. omogućiti pohranu i promjenu osobnih postavki, kako za cijelu aplikaciju, tako i, po potrebi, za svaku instancu MS SQL Servera i svaku bazu podataka
  6. iz točke 2 i 4 proizlazi želja da se naprave grupe po bazama podataka i grupe po instancama MS SQL Servera za koje su postavke jednake
  7. provesti pretraživanje duplikata indeksa (potpunih i nepotpunih, koji se ili neznatno razlikuju ili se razlikuju samo po uključenim stupcima)
  8. budući da se SQLIndexManager koristi samo za DBMS MS SQL Server, to treba odraziti u nazivu, na primjer, na sljedeći način: SQLIndexManager za MS SQL Server
  9. sve dijelove aplikacije koji nisu GUI premjestiti u zasebne module i preraditi na .NET Core 2.1

U vrijeme pisanja ovog članka, točka 6 iz želja je aktivno u razvoju i već postoji podrška u obliku pretraživanja punih i sličnih duplikata:

Pregled besplatnog alata SQLIndexManager

Izvori

Izvor: habr.com

Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere 🔥 Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere | ProHoster