Pregled besplatnog alata SQLIndexManager

Kao što je poznato, indeksi igraju važnu ulogu u DBMS-u, omogućavajući brzu pretragu potrebnih zapisa. Zbog toga je važno da ih redovno održavate. O analizi i optimizaciji napisano je mnogo materijala, uključujući i na internetu. Na primer, nedavno je urađena analiza ove teme u ovoj objavi.

Postoji mnogo rešenja, i plaćenih i besplatnih, za ovo. Na primer, postoji pripremljeno odluku, zasnovano na adaptivnoj metodi optimizacije indeksa.

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

Glavna tehnička razlika između SQLIndexManager-a i nekih drugih sličnih alata dolazi od samog autora. ovde i ovde.

U ovom članku ćemo pogledati projekat i mogućnosti korišćenja ovog softverskog rešenja.

Ova alatka se raspravlja ovde.
Vremenom su ispravljene većina zapažanja i grešaka.

Dakle, pređimo sada na sam alat SQLIndexManager.

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

Pregled besplatnog alata SQLIndexManager

i izgleda ovako:

Pregled besplatnog alata SQLIndexManager

Svi upiti se kreiraju u sledećim datotekama:

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

Pregled besplatnog alata SQLIndexManager

Priključivanjem na bazu podataka i slanjem upita prema DBMS-u, aplikacija se registrova na sledeći način:

ApplicationName="SQLIndexManager"

Kada pokrenete aplikaciju, otvoriće se modalni prozor za dodavanje veze:
Pregled besplatnog alata SQLIndexManager

Trenutno ne funkcioniše učitavanje celokupnog spiska svih instanci MS SQL Server-a dostupnih putem lokalnih mreža.

Takođe, vezu možete dodati putem krajnje leve dugmadi u glavnom meniju:

Pregled besplatnog alata SQLIndexManager

Dalje će se izvršiti sledeći upiti prema DBMS-u:

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

  2. Dobijanje liste dostupnih baza podataka sa njihovim kratkim karakteristikama
    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 gore navedenih skripti, pojaviće se prozor koji sadrži kratke informacije o bazama podataka izabranog instancu MS SQL Server-a:

Pregled besplatnog alata SQLIndexManager

Važno je napomenuti da se dodatne informacije prikazuju na osnovu prava. Ako postoje sysadmin, možete birati podatke iz prikaza sys.master_files. Ako takva prava ne postoje, vraća se manje podataka kako se ne bi usporavao zahtev.

Ovde je potrebno odabrati interesantne baze podataka i pritisnuti dugme "OK".

Zatim će se izvesti sledeći skript 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 upita, privremene tablice se često koriste. To se radi da ne bi došlo do rekompilacija i kako bi se, u slučaju velike sheme, plan mogao generirati paralelno prilikom umetanja podataka, jer umetanje s tabličnim varijablama može biti 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 se može prikazati i druge detaljne informacije, kao što su:

  1. baza podataka
  2. broj sekcija
  3. datum i vrijeme posljednjeg pristupa
  4. kompresija
  5. fajl grupa

itd.
Same kolone se mogu prilagoditi:

Pregled besplatnog alata SQLIndexManager

U ćelijama kolone Fix možete odabrati koja će radnja biti izvršena prilikom optimizacije. Također, na kraju skeniranja radnja po defaultu se bira na osnovu odabranih postavki:

Pregled besplatnog alata SQLIndexManager

Potrebno je odabrati potrebne indekse za obradu.

Pomoću glavnog menija možete sačuvati skript (ova ista dugmad pokreće proces optimizacije indeksa):

Pregled besplatnog alata SQLIndexManager

kao i sačuvati tabelu u različitim formatima (ova ista dugmad omogućava otvaranje detaljnih postavki za analizu i optimizaciju indeksa):

Pregled besplatnog alata SQLIndexManager

Također, informacije se mogu ažurirati pritiskom na treće dugme lijevo u glavnom meniju pored lupe.

Dugme s lupom omogućava odabir potrebnih baza podataka za razmatranje.

Trenutno ne postoji potpuna referentna sistem. Stoga će pritiskom na dugme “?” jednostavno doći do modnog prozora koji sadrži osnovne informacije o programskom proizvodu:

Pregled besplatnog alata SQLIndexManager

Osim svega gore navedenog, u glavnom meniju postoji pretraživačka traka:

Pregled besplatnog alata SQLIndexManager

Prilikom pokretanja procesa 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 možete prilagoditi finije opcije:

Pregled besplatnog alata SQLIndexManager

Želje za aplikacijom:

  1. omogućiti selektivno ažuriranje statistike ne samo za indekse već i na različite načine (potpuno ažurirati ili delimično)
  2. omogućiti ne samo izbor Baza podataka, već i različitih servera (to je vrlo korisno kada postoji mnogo instanci MS SQL Servera)
  3. za veću fleksibilnost u korištenju predlaže se obavijanje komandi u biblioteke i izlaženje u PowerShell komande, kao što je to urađeno, na primjer, ovdje:
  4. dbatools.io/commands
  5. omogućiti čuvanje i promenu ličnih podešavanja kako za celu aplikaciju, tako i za svaki primerak MS SQL Server-a i svaku bazu podataka pojedinačno
  6. iz tačaka 2 i 4 proizlazi želja da se naprave grupe po bazama podataka i grupe po primerima MS SQL Server-a, za koje su podešavanja identična
  7. napraviti pretragu duplikata indeksa (potpunih i nepotpunih, koji se samo malo razlikuju ili se razlikuju samo po uključenim kolonama)
  8. pošto se SQLIndexManager koristi samo za SGBD MS SQL Server, to se mora odraziti u nazivu, na primer, ovako: SQLIndexManager za MS SQL Server
  9. sve delove aplikacije koji nisu GUI izdvojiti u posebne module i prepisati na .NET Core 2.1

u trenutku pisanja ovog članka, tačka 6 iz želja aktivno se razvija i već postoji podrška u obliku pretrage potpunih i sličnih duplikata:

Pregled besplatnog alata SQLIndexManager

Izvori

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster