Vekolîna amûra belaş SQLIndexManager

Wekî ku hûn dizanin, index di DBMS-ê de rolek girîng dileyzin, lêgerînek bilez ji tomarên pêwîst re peyda dike. Ji ber vê yekê pir girîng e ku meriv di wextê xwe de ji wan re xizmet bike. Di derbarê analîz û xweşbîniyê de, di nav de li ser Înternetê, gelek materyal hatine nivîsandin. Ji bo nimûne, ev mijar di demên dawî de hate nirxandin vê weşanê.

Ji bo vê gelek çareseriyên pere û belaş hene. Ji bo nimûne, amade ye biryar, li ser bingeha rêbazek optimîzasyona pêvek a adaptive.

Dûv re, ka em li kargêriya belaş binihêrin SQLIndexManager, ji hêla AlanDenton.

Cûdahiya teknîkî ya sereke di navbera SQLIndexManager û hejmarek analogên din de ji hêla nivîskar bixwe ve tê dayîn vir и vir.

Di vê gotarê de, em ê li proje û kapasîteyên xebitandinê yên vê çareseriya nermalavê binihêrin.

Gotûbêja vê kêrhatî vir.
Bi demê re, piraniya şîrove û xeletiyan rast kirin.

Ji ber vê yekê, em naha biçin ser karûbarê SQLIndexManager bixwe.

Serlêdan bi C# .NET Framework 4.5 di Visual Studio 2017 de hatî nivîsandin û ji bo formên DevExpress bikar tîne:

û wiha xuya dike:

Hemî daxwaz di pelên jêrîn de têne çêkirin:

1. Naverok
2. Pirs
3. QueryEngine
4. ServerInfo

Dema ku bi databasek ve girêdayî ye û pirsan ji DBMS re dişîne, serîlêdan bi vî rengî tête îmze kirin:

ApplicationName=”SQLIndexManager”

Dema ku hûn serîlêdanê dest pê bikin, pencereyek modal dê vebe ku têkiliyek zêde bike:

Li vir, barkirina navnîşek bêkêmasî ya hemî mînakên MS SQL Server-a ku li ser torên herêmî têne gihîştin hîn jî nexebite.

Her weha hûn dikarin pêwendiyek bi karanîna bişkoja herî çepê ya li ser menuya sereke zêde bikin:

Dûv re, dê pirsên jêrîn ji DBMS re bêne destpêkirin:

1. Agahdariya li ser DBMS-ê digirin

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

2. Danîna navnîşek databasên berdest bi taybetmendiyên wan ên kurt

   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
   

Piştî cîbecîkirina skrîptên jorîn, dê pencereyek ku di derheqê databasên mînaka hilbijartî ya MS SQL Server de agahdariya kurt vedihewîne xuya bike:

Hêjayî gotinê ye ku agahiyên berfireh li gorî mafan tên nîşandan. Ger hebe sysadmin, hingê hûn dikarin daneyên ji dîtinê hilbijêrin sys.master_files. Ger mafên weha tunebin, wê hingê kêm dane bi hêsanî têne vegerandin da ku daxwazê ​​hêdî nekin.

Li vir hûn hewce ne ku databasên berjewendiyê hilbijêrin û bişkoka "OK" bikirtînin.

Dûv re, skrîpta jêrîn dê ji bo her databasa hilbijartî were darve kirin da ku rewşa îndeksan analîz bike:

Analîzkirina rewşa Indeksê

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

Wekî ku ji pirsan bixwe tê dîtin, tabloyên demkî pir caran têne bikar anîn. Ev tê kirin da ku ji nû ve berhevokî nebin, û di rewşek nexşeyek mezin de, dema ku daneyan têxin plan dikare paralel were çêkirin, ji ber ku têxistina guhêrbarên tabloyê tenê di yek mijarê de gengaz e.

Piştî pêkanîna skrîpta jorîn, pencereyek bi tabloyek indexê dê xuya bibe:

Her weha hûn dikarin agahdariyên din ên berfireh li vir nîşan bidin, wek:

1. database
2. hejmara beşan
3. roj û dema banga dawî
4. xistin
5. koma pelan

û vî awayî.
Axaftvan bixwe dikarin bêne xweş kirin:

Di hucreyên stûna Fix de, hûn dikarin hilbijêrin ka dê di dema xweşbîniyê de çi çalakî were kirin. Di heman demê de, dema ku şopandin qediya, li gorî mîhengên hilbijartî çalakiyek xwerû tê hilbijartin:

Divê hûn ji bo pêvajoyê indexên xwestin hilbijêrin.

Bi karanîna menuya sereke, hûn dikarin skrîptê hilînin (eynî bişkojka pêvajoya xweşbînkirina îndeksê bixwe dest pê dike):

û tabloyê di formên cihêreng de hilînin (eynî bişkoj dihêle hûn mîhengên hûrgulî ji bo analîzkirin û xweşbînkirina indexan vekin):

Her weha hûn dikarin bi tikandina bişkoja sêyem a li milê çepê di menuya sereke ya li kêleka cama mezinkirinê de agahdarî nûve bikin.

Bişkojka bi şûşek mezinker dihêle hûn databasên xwestî ji bo berçav hilbijêrin.

Niha sîstemeke alîkariyê ya berfireh nîne. Ji ber vê yekê, pêl bişkoka "?" dê bi tenê bibe sedem ku pencereyek modal ku agahdariya bingehîn li ser hilbera nermalavê vedihewîne xuya bike:

Ji bilî her tiştê ku li jor hatî destnîşan kirin, menuya sereke barek lêgerînê heye:

Dema ku pêvajoya xweşbînkirina indexê dest pê dike:

Her weha hûn dikarin têketinek çalakiyên ku li binê pencereyê hatine kirin bibînin:

Di pencereya mîhengên hûrgulî de ji bo analîz û xweşbînkirina pêvekê, hûn dikarin vebijarkên naziktir mîheng bikin:

Daxwazên ji bo serîlêdanê:

1. gengaz dike ku ne tenê ji bo îndeksan û di heman demê de bi awayên cihêreng statîstîkên bijartî nûve bike (bi tevahî nûvekirin an jî qismî)
2. ne tenê bijartina databasek, lê di heman demê de pêşkêşkerên cihêreng jî gengaz dike (ev pir hêsan e dema ku gelek mînakên MS SQL Server hene)
3. Ji bo nermbûna zêde di karanînê de, tê pêşniyar kirin ku emrên di pirtûkxaneyan de bipêçin û wan ji fermanên PowerShell derxînin, wekî mînak li vir tê kirin:
4. dbatools.io/commands
5. hem ji bo tevahiya serîlêdanê û hem jî, ger hewce be, ji bo her mînakek MS SQL Server û her databasê hilanînê û guheztina mîhengên kesane mimkun dike.
6. Ji xalên 2 û 4, ev tê vê wateyê ku hûn dixwazin ji hêla databasan û koman ve ji hêla mînakên MS SQL Server ve koman biafirînin, ku mîhengên wan yek in.
7. li îndeksên dubare bigerin (temam û netemam, ku hinekî cûda ne an jî tenê di stûnên tê de cûda dibin)
8. Ji ber ku SQLIndexManager tenê ji bo MS SQL Server DBMS-ê tê bikar anîn, pêdivî ye ku vê yekê di navê de, mînakî, wekî jêrîn nîşan bide: SQLIndexManager ji bo MS SQL Server
9. Hemî beşên ne-GUI yên serîlêdanê veguherînin modulên cihêreng û wan di .NET Core 2.1 de ji nû ve binivîsin

Di dema nivîsandinê de, xala 6-ê ya xwestekan bi rengek çalak tê pêşve xistin û jixwe di forma lêgerîna dubareyên bêkêmasî û wekhev de piştgirî heye:

Çavkaniyên

• rêbaza optimîzasyona index adaptîv
• Alîkariya SQLIndexManager
• kurteya teknîkî ya kargêriya SQLIndexManager
• nîqaş li ser kargêriya SQLIndexManager
• Rêvebirê Indeksa SQL - Amûra GUI-ya Belaş ji bo Maintenance Indeks li ser SQL Server û Azure
• SQL Server Indeksa Analysis and Optimization

Source: www.habr.com