🥇Përmbledhje e mjetit falas SQLIndexManager

Si është e njohur, indekset luajnë një rol të rëndësishëm në DBMS, duke ofruar kërkimin e shpejtë në rekordet e nevojshme. Prandaj, është kaq e rëndësishme t'i mirëmbani ato në mënyrë të duhur. Ka shumë materiale për analizën dhe optimizimin e indekseve, përfshirë dhe në internet. Për shembull, së fundmi u bë një shqyrtim i kësaj teme në këtë publikim.

Ekzistojnë shumë zgjidhje si të paguara ashtu edhe falas për këtë. Për shembull, ka një të gatshme vendim, e bazuar në metodën adaptuese të optimizimit të indekseve.

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

Ndryshimi më i rëndësishëm teknik midis SQLIndexManager dhe disa analogëve të tjerë tregohet nga vetë autori. këtu dhe këtu.

Në këtë artikull do të shohim projektin dhe mundësitë e shfrytëzimit të këtij zgjidhjeje software-i.

Diskutohet për këtë utilitar këtu.
Me kalimin e kohës, shumica e vërejtjeve dhe gabimeve u korrigjuan.

Pra, tani do të kalojmë në vetë utilitarin 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 më poshtë:

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

Index
Query
QueryEngine
ServerInfo

Kur lidheni me bazën e të dhënave dhe dërgoni kërkesa në DBMS, aplikacioni regjistrohet në këtë mënyrë:

ApplicationName="SQLIndexManager"

Kur hapni aplikacionin, do të hapet një dritare modale për të shtuar lidhjen:

Aktualisht nuk funksionon ngarkimi i listës së plotë të të gjitha instancave MS SQL Server, të disponueshme përmes rrjeteve lokale.

Po ashtu, mund të shtoni lidhjen me ndihmën e butonit në anën më të majtë në menunë kryesore:

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

Marrja e informacionit të DBMS

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

Marrja e listës së bazave të të dhënave të disponueshme me pronat e tyre përmbledhë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 ekzekutimit të skripteve të përmendura më sipër, do të shfaqet një dritare që përmban informacion të shkurtër mbi bazat e të dhënave të instancës së zgjedhur MS SQL Server:

Është e rëndësishme të theksohet se informacioni i zgjeruar shfaqet në varësi të të drejtave. Nëse ka sysadmin, atëherë mund të zgjidhni të dhënat nga pamja sys.master_files. Nëse nuk ka të drejta të tilla, atëherë thjesht kthehen më pak të dhëna për të mos ngadalësuar kërkesën.

Këtu duhet të zgjidhni bazat e të dhënave që ju interesojnë dhe të klikoni në butonin “OK”.

Më pas do të ekzekutohet skripti në vijim për çdo bazë të dhënash të zgjedhur për të analizuar gjendjen e 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

NËSE OBJECT_ID('tempdb.dbo.#AllocationUnits') ËSHTË NË NULL
    DROP TABLE #AllocationUnits

KRIJO 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)
GRUPO NGA [container_id]
HAVING SUM([total_pages]) BETWEEN @MinIndexSize AND @MaxIndexSize

NËSE OBJECT_ID('tempdb.dbo.#ExcludeList') ËSHTË NË NULL
    DROP TABLE #ExcludeList

KRIJO TABLE #ExcludeList (ID INT PRIMARY KEY)

INSERT INTO #ExcludeList
SELECT [object_id]
FROM sys.objects WITH(NOLOCK)
KUJDESI [type] NË ('V', 'U')
    DHE ( [is_ms_shipped] = 1 )

NËSE OBJECT_ID('tempdb.dbo.#Partitions') ËSHTË NË NULL
    DROP TABLE #Partitions

SELECT [object_id]
     , [index_id]
     , [partition_id]
     , [partition_number]
     , [rows]
     , [data_compression]
NË #Partitions
FROM sys.partitions WITH(NOLOCK)
KUJDESI [object_id] > 255
    DHE [rows] > 0
    DHE [object_id] NUK ËSHTË NË (SELECT * FROM #ExcludeList)

NËSE OBJECT_ID('tempdb.dbo.#Indexes') ËSHTË NË NULL
    DROP TABLE #Indexes

KRIJO 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] 
KUJDESI i.[type] NË (0, 1, 2, 5, 6)
    DHE i.[object_id] > 255

DEKLARON @files TABLE (ID INT PRIMARY KEY)
INSERT INTO @files
SELECT DISTINCT [data_space_id]
FROM sys.database_files WITH(NOLOCK)
KUJDESI [state] != 0
    DHE [type] = 0

NËSE @@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]
    KUJDESI ISNULL(dds.[data_space_id], i.DataSpaceID) NË (SELECT * FROM @files)

END


DEKLARON @DBID   INT
      , @DBNAME SYSNAME

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

NËSE OBJECT_ID('tempdb.dbo.#Fragmentation') ËSHTË NË NULL
    DROP TABLE #Fragmentation

KRIJO 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
KUJDESI i.PagesCount = @Fragmentation
        OSE
            i.PagesCount > @PreDescribeSize
        OSE
            i.IndexType NË (5, 6)
    )

Si e duket nga vetë kërkesat, përdoren mjaft shpesh tabela të përkohshme. Kjo bëhet për të evituar rikompilimet, dhe në rastin e një skeme të madhe, plani mund të gjenerohet parazitarisht gjatë inserimit të të dhënave, pasi inserimi me variablave tabelarë është i mundur vetëm në një rrjedhë.

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

Po ashtu, këtu mund të tregoni edhe informacion të detajuar tjetër, si:

baza e të dhënave
numri i seksioneve
data dhe ora e fundit të aksesit
kompreson
grupi i skedarëve

etj.
Kolonat vetë mund të konfigurohen:

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

Duhet të zgjidhni indekset e nevojshme për përpunim.

Me ndihmën e menusë kryesore, mund të ruani script-in (kjo buton gjithashtu fillon procesin e optimizimit të indekseve):

po ashtu edhe të ruani tabelën në formate të ndryshme (kjo buton lejon gjithashtu të hapni cilësimet e detajuara për analizën dhe optimizimin e indekseve):

Po ashtu, informacioni mund të përditësohet duke klikuar në butonin e tretë nga majtas në menunë kryesore pranë lupës.

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

Momentalisht nuk ka një sistem të plotë ndihmës. Prandaj, klikuar në butonin “?” do të thotë thjesht shfaqja e një dritare modale, e cila përmban informacionin kryesor mbi produktin softuerik:

Përveç gjithçkaje të mësipërme, në menunë kryesore ka një vijë kërkimi:

Kur filloni procesin e optimizimit të indekseve:

Po ashtu, në fund të dritares mund të shikoni log-un e veprimeve të kryer:

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

Propozime për aplikacionin:

të bëhet e mundur të përditësohen statistikat përveç vetëm për indekset dhe gjithashtu në mënyra të ndryshme (të përditësohen plotësisht ose pjesërisht)
të bëhet e mundur jo vetëm të zgjidhen Baza e Të Dhënave, por edhe serverë të ndryshëm (kjo është shumë e përshtatshme kur ka shumë instance të MS SQL Server)
për fleksibilitet më të madh në përdorim, sugjerohet të mbështillen komandat në biblioteka dhe të shqipërohen në komandat PowerShell, siç është bërë, për shembull, këtu:
dbatools.io/commands
të mundësojë ruajtjen dhe ndryshimin e cilësimeve personale si për të gjithë aplikacionin, ashtu edhe për secilin instancë të MS SQL Server dhe çdo bazë të dhënash në rast nevoje
nga p.2 dhe 4 rrjedh dëshira për të krijuar grupe sipas bazave të të dhënave dhe grupe sipas instancave të MS SQL Server, për të cilat cilësimet janë të njëjta
të bëjë një kërkim për dublikatat e indekseve (të plota dhe të pjesshme, që janë ose pak të ndryshme ose ndryshojnë vetëm me kolonat e përfshira)
duke pasur parasysh se SQLIndexManager përdoret vetëm për DBMS MS SQL Server, është e nevojshme ta pasqyrojmë këtë në emrin, për shembull, si më poshtë: SQLIndexManager for MS SQL Server
të gjitha pjesët e aplikacionit jo GUI të nxirren në module të veçanta dhe të rishkruhen në .NET Core 2.1

Në momentin e shkruarjes së artikullit, p.6 nga dëshirat është aktivisht në zhvillim dhe tashmë ka mbështetje në formën e kërkimit të dublikatave të plota dhe të ngjashme:

Burimet

Burimi: habr.com