Përmbledhja e mjetit falas SQLIndexManager

Siç dihet, indekset luajnë një rol të rëndësishëm në DBMS, duke ofruar kërkimin e shpejtë në regjistrat e nevojshëm. Prandaj, është kaq e rëndësishme t'i mbash ato në rregull. Për analizën dhe optimizimin është shkruar shumë material, duke përfshirë edhe në Internet. Për shembull, së fundmi është bërë një përmbledhje e kësaj teme në këtë publikim.

Ekzistojnë shumë zgjidhje si të paguara ashtu edhe falas për këtë. Për shembull, ekziston një zgjidhje, e cila bazohet në metodën adaptuese të optimizimit të indekseve.

Më tej do të shqyrtojmë utilitarin falas SQLIndexManager, autori i të cilit është AlanDenton.

Dallimi kryesor teknik mes SQLIndexManager dhe disa analoge të tjera e përshkruan vet autori këtu dhe këtu.

Në këtë artikull do të shikojmë nga një këndvështrim tërheqës mbi projektin dhe mundësitë e shfrytëzimit të këtij zgjidhjeje software.

Diskutohen për këtë utilitar këtu.
Me kalimin e kohës, shumica e vërejtjeve dhe defekteve janë rregulluar.

Tani le të kalojmë te vetë utilitari SQLIndexManager.

Aplikacioni është shkruar në gjuhën C# .NET Framework 4.5 në Visual Studio 2017 dhe përdor DevExpress për format:

dhe duket si në vazhdim:

Të gjitha kërkesat formohen në skedarët e mëposhtëm:

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

Kur lidhesh me bazën e të dhënave dhe dërgon kërkesa në DBMS, aplikacioni regjistron ndryshe:

ApplicationName=”SQLIndexManager”

Kur të nisë aplikacioni, do të hapet një dritare modale për të shtuar lidhjen:

Tani nuk punon tërheqja e listës së plotë të të gjithë instancave të MS SQL Server, të aksesueshme përmes rrjeteve lokale.

Gjithashtu, mund të shtoni lidhjen me anë të butonit në anën e majtë të menusë kryesore:

Më pas do të ekzekutohen kërkesat e mëposhtme në DBMS:

1. Marrja e informacionit mbi DBMS

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

2. Marrja e listës së bazave të të dhënave në dispozicion me pronat e tyre përkatëse

   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
   

Pas pastrimit të skenarëve të mësipërm, do të shfaqet një dritare me informacion të shkurtër rreth databazave të instancës përkatëse MS SQL Server:

Vlen të përmendet se informacioni i zgjeruar shfaqet në përputhje me të drejtat. Nëse ka sysadmin, atëherë mund të zgjidhni të dhënat nga pamja sys.master_files. Nëse nuk ka të tilla të drejta, atëherë kthehen më pak të dhëna për të mos ngadalësuar kërkesën.

Këtu nevojitet të zgjidhni databazat që ju interesojnë dhe të klikoni në butonin “OK”.

Më pas do të ekzekutohet skenari i ardhshëm për secilën databazë të zgjedhur për analizën e gjendjes së indekseve:

Analiza e gjendjes së indekseve

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

SET NOCOUNT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF

NSE OBJ_ID('tempdb.dbo.#AllocationUnits') IS NOT NULL
    DROPI TABELË #AllocationUnits

KRIJO TABELË #AllocationUnits (
      ContainerID   BIGINT PRIMARY KEY
    , ReservedPages BIGINT NOT NULL
    , UsedPages     BIGINT NOT NULL
)

FUTURE KRIJO TABELË #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

NSE OBJ_ID('tempdb.dbo.#ExcludeList') IS NOT NULL
    DROPI TABELË #ExcludeList

KRIJO TABELË #ExcludeList (ID INT PRIMARY KEY)

FUTURE KRIJO TABELË #ExcludeList
SELECT [object_id]
FROM sys.objects WITH(NOLOCK)
WHERE [type] IN ('V', 'U')
    AND ( [is_ms_shipped] = 1 )

NSE OBJ_ID('tempdb.dbo.#Partitions') IS NOT NULL
    DROPI TABELË #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)

NSE OBJ_ID('tempdb.dbo.#Indexes') IS NOT NULL
    DROPI TABELË #Indexes

KRIJO TABELË #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)
)

FUTURE KRIJO TABELË #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

DEKLARO @files TABELË (ID INT PRIMARY KEY)
FUTURE KRIJO @files
SELECT DISTINCT [data_space_id]
FROM sys.database_files WITH(NOLOCK)
WHERE [state] != 0
    AND [type] = 0

NSE @@ROWCOUNT > 0 BEGIN

    FSHI 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


DEKLARO @DBID   INT
      , @DBNAME SYSNAME

SET @DBNAME = DB_NAME()
SELECT @DBID = [database_id]
FROM sys.databases WITH(NOLOCK)
WHERE [name] = @DBNAME

NSE OBJ_ID('tempdb.dbo.#Fragmentation') IS NOT NULL
    DROPI TABELË #Fragmentation

KRIJO TABELË #Fragmentation (
      ObjectID         INT NOT NULL
    , IndexID          INT NOT NULL
    , PartitionNumber  INT NOT NULL
    , Fragmentation    FLOAT NOT NULL
    , PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)

FUTURE KRIJO TABELË #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)
    )

Siç shihet nga vetë kërkesat, tabelat përkohshme përdoren mjaft shpesh. Kjo bëhet për të shmangur rikompilimin, dhe në rastin e një skeme të madhe, plani mund të gjenerohet paralelisht gjatë futjes së të dhënave, pasi futja me variablat tabelarë është e mundur vetëm në një rrjedhë.

Pas ekzekutimit të skriptit të mësipërm, do të shfaqet një dritare me tabelën e indekseve:

Gjithashtu këtu mund të nxirret informacion tjetër të detajuar, si:

1. baza e të dhënave
2. numri i seksioneve
3. data dhe koha e fundit e aksesit
4. kompresimi
5. grupi i skedarëve

etj.
Kolonat mund të konfigurohen:

Në qelizat e kolonës Fix mund të zgjidhet cila veprim do të kryhet gjatë optimizimit. Gjithashtu, pas përfundimit të skanimit, veprimi i paracaktuar zgjidhet në bazë të cilësimeve të zgjedhura:

Nevojitet të zgjidhen indekset e duhura për përpunim.

Me anë të menusë kryesore mund të ruani skriptin (kjo janë po ashtu butoni që nis procesin e optimizimit të indekseve):

po ashtu mund të ruani tabelën në formate të ndryshme (kjo janë po ashtu butoni që lejon të hapni cilësimet e detajuara për analizimin dhe optimizimin e indekseve):

Informacioni mund të përditësohet gjithashtu duke klikuar në butonin e tretë nga e majta në menunë kryesore pranë lupës.

Butoni me lupë lejon të zgjidhni bazat e të dhënave të nevojshme për shqyrtim.

Aktualisht nuk ka një sistem të plotë ndihmëse. Prandaj, klikimi në butonin “?” do të sjellë një dritare modale që përmban informacionin kryesor mbi produktin softuerik:

Përveç gjithçkaje të përmendur më parë, në menunë kryesore ka një fushë kërkimi:

Në nisjen e procesit të optimizimit të indekseve:

Gjithashtu në fund të dritares mund të shihni log-un e veprimeve të kryera:

Në dritaren e cilësimeve të detajuara për analizën dhe optimizimin e indekseve, ju mund të konfiguroni mundësi më të hollësishme:

Sugjerime për aplikacionin:

1. të bëhet e mundur për të përditësuar selektivisht statistikat jo vetëm për indekset, por edhe në mënyra të ndryshme (përditësim të plotë ose të pjesshëm)
2. të bëhet e mundur jo vetëm zgjedhja e bazave të të dhënave, por edhe servereve të ndryshëm (kjo është shumë e dobishme kur ka shumë instanca të MS SQL Server)
3. për fleksibilitet më të madh në përdorim, propozohet të rrethohen komandat në biblioteka dhe të dalin në komandat PowerShell, ashtu siç është bërë, për shembull, këtu:
4. dbatools.io/commands
5. të bëni të mundur ruajtjen dhe ndryshimin e cilësimeve personale për të gjithë aplikacionin, si dhe kur është e nevojshme për çdo instancë të MS SQL Server dhe çdo bazë të dhënash
6. nga p.2 dhe 4 del dëshira për të krijuar grupe për bazat e të dhënave dhe grupe për instancat e MS SQL Server, për të cilat cilësimet janë të njëjta
7. të bëni kërkimin e kopjeve të dyfishta të indekseve (të plota dhe të pjesshme, që ose dallohet pak ose vetëm në kolonat e përfshira)
8. për shkak se SQLIndexManager përdoret vetëm për DBMS MS SQL Server, është e nevojshme ta pasqyrojmë këtë në emër, për shembull, si: SQLIndexManager për MS SQL Server
9. të nxirrni të gjitha pjesët e aplikacionit pa GUI në module të veçanta dhe t'i rishkruani ato në .NET Core 2.1

Në momentin e shkrimit të këtij artikulli, pika 6 e dëshirave është në zhvillim aktiv dhe tashmë ka mbështetje në formën e kërkimit të kopjeve të plota dhe të ngjashme:

Burimet

• metoda adaptuese për optimizimin e indekseve
• utilita SQLIndexManager
• përmbledhje teknike e utilitës SQLIndexManager
• diskutimi rreth utilitës SQLIndexManager
• SQL Index Manager – Mjeti Falas GUI për Mirëmbajtjen e Indekseve në SQL Server dhe Azure
• Analiza dhe Optimizimi i Indekseve të SQL Server

Burimi: habr.com