Pregled brezplačnega orodja SQLIndexManager

Kot veste, imajo indeksi pomembno vlogo v DBMS, saj omogočajo hitro iskanje po zahtevanih zapisih. Zato je tako pomembno, da jih servisiramo pravočasno. O analizi in optimizaciji je bilo napisanega kar nekaj gradiva, tudi na internetu. Na primer, ta tema je bila nedavno pregledana v ta publikacija.

Za to obstaja veliko plačljivih in brezplačnih rešitev. Na primer, obstaja že pripravljena Odločitev, ki temelji na metodi optimizacije prilagodljivega indeksa.

Nato si poglejmo brezplačen pripomoček SQLIndexManager, avtor AlanDenton.

Glavno tehnično razliko med SQLIndexManagerjem in številnimi drugimi analogi je navedel avtor sam tukaj и tukaj.

V tem članku si bomo ogledali projekt in operativne zmogljivosti te programske rešitve od zunaj.

Razprava o tem pripomočku tukaj.
Sčasoma je bila večina komentarjev in napak popravljenih.

Torej, pojdimo zdaj na sam pripomoček SQLIndexManager.

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

in izgleda takole:

Vse zahteve so ustvarjene v naslednjih datotekah:

1. Kazalo
2. Poizvedba
3. QueryEngine
4. ServerInfo

Pri povezovanju z bazo podatkov in pošiljanju poizvedb v DBMS se aplikacija podpiše na naslednji način:

ApplicationName=”SQLIndexManager”

Ko zaženete aplikacijo, se odpre modalno okno za dodajanje povezave:

Tukaj nalaganje celotnega seznama vseh primerkov MS SQL Server, dostopnih prek lokalnih omrežij, še ne deluje.

Povezavo lahko dodate tudi z uporabo skrajnega levega gumba v glavnem meniju:

Nato se bodo sprožile naslednje poizvedbe v DBMS:

1. Pridobivanje informacij o DBMS

   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 zgornjih skriptov se prikaže okno s kratkimi informacijami o bazah podatkov izbranega primerka MS SQL Server:

Omeniti velja, da so razširjene informacije prikazane na podlagi pravic. Če tam sistemski administrator, potem lahko izberete podatke iz pogleda sys.master_files. Če teh pravic ni, se preprosto vrne manj podatkov, da se zahteva ne upočasni.

Tukaj morate izbrati baze podatkov, ki vas zanimajo, in klikniti gumb »V redu«.

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

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 <= @PreDescribeSize
AND r.[index_level] = 0
AND r.[alloc_unit_type_desc] = 'IN_ROW_DATA'
AND i.IndexType IN (0, 1, 2)
IF OBJECT_ID('tempdb.dbo.#Columns') IS NOT NULL
DROP TABLE #Columns
CREATE TABLE #Columns (
ObjectID     INT NOT NULL
, ColumnID     INT NOT NULL
, ColumnName   SYSNAME NULL
, SystemTypeID TINYINT NULL
, IsSparse     BIT
, IsColumnSet  BIT
, MaxLen       INT
, PRIMARY KEY (ObjectID, ColumnID)
)
INSERT INTO #Columns
SELECT ObjectID     = [object_id]
, ColumnID     = [column_id]
, ColumnName   = [name]
, SystemTypeID = [system_type_id]
, IsSparse     = [is_sparse]
, IsColumnSet  = [is_column_set]
, MaxLen       = [max_length]
FROM sys.columns WITH(NOLOCK)
WHERE [object_id] IN (SELECT DISTINCT i.ObjectID FROM #Indexes i)
IF OBJECT_ID('tempdb.dbo.#IndexColumns') IS NOT NULL
DROP TABLE #IndexColumns
CREATE TABLE #IndexColumns (
ObjectID   INT NOT NULL
, IndexID    INT NOT NULL
, OrderID    INT NOT NULL
, ColumnID   INT NOT NULL
, IsIncluded BIT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, ColumnID)
)
INSERT INTO #IndexColumns
SELECT ObjectID   = [object_id]
, IndexID    = [index_id]
, OrderID    = CASE WHEN [is_included_column] = 0 THEN [key_ordinal] ELSE [index_column_id] END
, ColumnID   = [column_id]
, IsIncluded = ISNULL([is_included_column], 0)
FROM sys.index_columns ic WITH(NOLOCK)
WHERE EXISTS(
SELECT *
FROM #Indexes i
WHERE i.ObjectID = ic.[object_id]
AND i.IndexID = ic.[index_id]
AND i.IndexType IN (1, 2)
)
IF OBJECT_ID('tempdb.dbo.#Lob') IS NOT NULL
DROP TABLE #Lob
CREATE TABLE #Lob (
ObjectID    INT NOT NULL
, IndexID     INT NOT NULL
, IsLobLegacy BIT
, IsLob       BIT
, PRIMARY KEY (ObjectID, IndexID)
)
INSERT INTO #Lob (ObjectID, IndexID, IsLobLegacy, IsLob)
SELECT c.ObjectID
, IndexID     = ISNULL(i.IndexID, 1)
, IsLobLegacy = MAX(CASE WHEN c.SystemTypeID IN (34, 35, 99) THEN 1 END)
, IsLob       = 0
FROM #Columns c
LEFT JOIN #IndexColumns i ON c.ObjectID = i.ObjectID AND c.ColumnID = i.ColumnID
WHERE c.SystemTypeID IN (34, 35, 99)
GROUP BY c.ObjectID
, i.IndexID
IF OBJECT_ID('tempdb.dbo.#Sparse') IS NOT NULL
DROP TABLE #Sparse
CREATE TABLE #Sparse (ObjectID INT PRIMARY KEY)
INSERT INTO #Sparse
SELECT DISTINCT ObjectID
FROM #Columns
WHERE IsSparse = 1
OR IsColumnSet = 1
IF OBJECT_ID('tempdb.dbo.#AggColumns') IS NOT NULL
DROP TABLE #AggColumns
CREATE TABLE #AggColumns (
ObjectID        INT NOT NULL
, IndexID         INT NOT NULL
, IndexColumns    NVARCHAR(MAX)
, IncludedColumns NVARCHAR(MAX)
, PRIMARY KEY (ObjectID, IndexID)
)
INSERT INTO #AggColumns
SELECT t.ObjectID
, t.IndexID
, IndexColumns = STUFF((
SELECT ', [' + c.ColumnName + ']'
FROM #IndexColumns i
JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID
WHERE i.ObjectID = t.ObjectID
AND i.IndexID = t.IndexID
AND i.IsIncluded = 0
ORDER BY i.OrderID
FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
, IncludedColumns = STUFF((
SELECT ', [' + c.ColumnName + ']'
FROM #IndexColumns i
JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID
WHERE i.ObjectID = t.ObjectID
AND i.IndexID = t.IndexID
AND i.IsIncluded = 1
ORDER BY i.OrderID
FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
FROM (
SELECT DISTINCT ObjectID, IndexID
FROM #Indexes
WHERE IndexType IN (1, 2)
) t
SELECT i.ObjectID
, i.IndexID
, i.IndexName
, ObjectName       = o.[name]
, SchemaName       = s.[name]
, i.PagesCount
, i.UnusedPagesCount
, i.PartitionNumber
, i.RowsCount
, i.IndexType
, i.IsAllowPageLocks
, u.TotalWrites
, u.TotalReads
, u.TotalSeeks
, u.TotalScans
, u.TotalLookups
, u.LastUsage
, i.DataCompression
, f.Fragmentation
, IndexStats       = STATS_DATE(i.ObjectID, i.IndexID)
, IsLobLegacy      = ISNULL(lob.IsLobLegacy, 0)
, IsLob            = ISNULL(lob.IsLob, 0)
, IsSparse         = CAST(CASE WHEN p.ObjectID IS NULL THEN 0 ELSE 1 END AS BIT)
, IsPartitioned    = CAST(CASE WHEN dds.[data_space_id] IS NOT NULL THEN 1 ELSE 0 END AS BIT)
, FileGroupName    = fg.[name]
, i.IsUnique
, i.IsPK
, i.FillFactorValue
, i.IsFiltered
, a.IndexColumns
, a.IncludedColumns
FROM #Indexes i
JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = i.ObjectID
JOIN sys.schemas s WITH(NOLOCK) ON s.[schema_id] = o.[schema_id]
LEFT JOIN #AggColumns a ON a.ObjectID = i.ObjectID AND a.IndexID = i.IndexID
LEFT JOIN #Sparse p ON p.ObjectID = i.ObjectID
LEFT JOIN #Fragmentation f ON f.ObjectID = i.ObjectID AND f.IndexID = i.IndexID AND f.PartitionNumber = i.PartitionNumber
LEFT JOIN (
SELECT ObjectID      = [object_id]
, IndexID       = [index_id]
, TotalWrites   = NULLIF([user_updates], 0)
, TotalReads    = NULLIF([user_seeks] + [user_scans] + [user_lookups], 0)
, TotalSeeks    = NULLIF([user_seeks], 0)
, TotalScans    = NULLIF([user_scans], 0)
, TotalLookups  = NULLIF([user_lookups], 0)
, LastUsage     = (
SELECT MAX(dt)
FROM (
VALUES ([last_user_seek])
, ([last_user_scan])
, ([last_user_lookup])
, ([last_user_update])
) t(dt)
)
FROM sys.dm_db_index_usage_stats WITH(NOLOCK)
WHERE [database_id] = @DBID
) u ON i.ObjectID = u.ObjectID AND i.IndexID = u.IndexID
LEFT JOIN #Lob lob ON lob.ObjectID = i.ObjectID AND lob.IndexID = i.IndexID
LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id]
JOIN sys.filegroups fg WITH(NOLOCK) ON ISNULL(dds.[data_space_id], i.DataSpaceID) = fg.[data_space_id] 
WHERE o.[type] IN ('V', 'U')
AND (
f.Fragmentation >= @Fragmentation
OR
i.PagesCount > @PreDescribeSize
OR
i.IndexType IN (5, 6)
)

Kot je razvidno iz samih poizvedb, se začasne tabele uporabljajo precej pogosto. To je narejeno tako, da ni ponovnega prevajanja, v primeru velike sheme pa se načrt lahko generira vzporedno pri vstavljanju podatkov, saj je vstavljanje spremenljivk tabele možno samo v eni niti.

Po izvedbi zgornjega skripta se prikaže okno z indeksno tabelo:

Tukaj lahko prikažete tudi druge podrobne informacije, kot so:

1. baze podatkov
2. število odsekov
3. datum in čas zadnjega klica
4. stisnite
5. datotečna skupina

in t. d.
Same zvočnike je mogoče prilagoditi:

V celicah stolpca Popravi lahko izberete, katero dejanje bo izvedeno med optimizacijo. Ko je skeniranje končano, je na podlagi izbranih nastavitev izbrano tudi privzeto dejanje:

Za obdelavo morate izbrati želene indekse.

Z uporabo glavnega menija lahko shranite skript (isti gumb zažene sam proces optimizacije indeksa):

in shranite tabelo v različnih formatih (isti gumb omogoča odpiranje podrobnih nastavitev za analizo in optimizacijo indeksov):

Podatke lahko posodobite tudi s klikom na tretji gumb na levi v glavnem meniju poleg povečevalnega stekla.

Gumb s povečevalnim steklom omogoča izbiro želenih baz podatkov za obravnavo.

Trenutno ni celovitega sistema pomoči. Zato s pritiskom na gumb “?” preprosto povzroči, da se prikaže modalno okno, ki vsebuje osnovne informacije o izdelku programske opreme:

Poleg vsega zgoraj opisanega ima glavni meni iskalno vrstico:

Ko začnete postopek optimizacije indeksa:

Na dnu okna si lahko ogledate tudi dnevnik izvedenih dejanj:

V oknu s podrobnimi nastavitvami za analizo in optimizacijo indeksa lahko konfigurirate subtilnejše možnosti:

Zahteve za prijavo:

1. omogočajo selektivno posodabljanje statistike ne le za indekse, ampak tudi na različne načine (popolnoma ali delno posodobitev)
2. omogočajo ne samo izbiro baze podatkov, ampak tudi različne strežnike (to je zelo priročno, če obstaja veliko primerkov MS SQL Server)
3. Za večjo prilagodljivost pri uporabi je predlagano, da ukaze zavijete v knjižnice in jih izpišete v ukaze PowerShell, kot je to storjeno na primer tukaj:
4. dbatools.io/ukazi
5. omogočajo shranjevanje in spreminjanje osebnih nastavitev za celotno aplikacijo in po potrebi za vsako instanco MS SQL Server in vsako bazo podatkov
6. Iz 2. in 4. točke izhaja, da želite ustvariti skupine po bazah podatkov in skupine po instancah MS SQL Server, za katere so nastavitve enake
7. iskanje podvojenih indeksov (popolnih in nepopolnih, ki se nekoliko razlikujejo ali se razlikujejo samo v vključenih stolpcih)
8. Ker se SQLIndexManager uporablja samo za MS SQL Server DBMS, je treba to odražati v imenu, na primer takole: SQLIndexManager za MS SQL Server
9. Premaknite vse dele aplikacije, ki niso GUI, v ločene module in jih prepišite v .NET Core 2.1

V času pisanja se 6. točka želja aktivno razvija in že obstaja podpora v obliki iskanja popolnih in podobnih dvojnikov:

viri

• metoda optimizacije prilagodljivega indeksa
• Pripomoček SQLIndexManager
• kratek tehnični pregled pripomočka SQLIndexManager
• razprava o pripomočku SQLIndexManager
• SQL Index Manager – brezplačno orodje GUI za vzdrževanje indeksov v SQL Server in Azure
• Analiza in optimizacija indeksa SQL Server

Vir: www.habr.com