Reviżjoni tal-għodda b'xejn SQLIndexManager

Kif tafu, l-indiċi għandhom rwol importanti f'DBMS, billi jipprovdu tfittxija rapida għar-rekords meħtieġa. Huwa għalhekk li huwa daqshekk importanti li sservihom fil-ħin. Inkiteb ħafna materjal dwar l-analiżi u l-ottimizzazzjoni, inkluż fuq l-Internet. Pereżempju, dan is-suġġett ġie rivedut reċentement fi din il-pubblikazzjoni.

Hemm ħafna soluzzjonijiet imħallsa u b'xejn għal dan. Per eżempju, hemm lest deċiżjoni, ibbażat fuq metodu ta 'ottimizzazzjoni ta' indiċi adattivi.

Sussegwentement, ejja nħarsu lejn l-utilità ħielsa SQLIndexManager, awtur minn AlanDenton.

Id-differenza teknika ewlenija bejn SQLIndexManager u numru ta 'analogi oħra hija mogħtija mill-awtur innifsu hawn и hawn.

F'dan l-artikolu, aħna ser nagħtu ħarsa barra lejn il-proġett u l-kapaċitajiet operattivi ta 'din is-soluzzjoni tas-softwer.

Niddiskutu din l-utilità hawn.
Maż-żmien, ħafna mill-kummenti u l-bugs ġew ikkoreġuti.

Allura, ejja issa nimxu fuq l-utilità SQLIndexManager innifsu.

L-applikazzjoni hija miktuba f'C# .NET Framework 4.5 f'Visual Studio 2017 u tuża DevExpress għall-formoli:

u jidher hekk:

It-talbiet kollha huma ġġenerati fil-fajls li ġejjin:

1. indiċi
2. Mistoqsija
3. QueryEngine
4. ServerInfo

Meta tikkonnettja ma 'database u tibgħat mistoqsijiet lid-DBMS, l-applikazzjoni tiġi ffirmata kif ġej:

ApplicationName=”SQLIndexManager”

Meta tniedi l-applikazzjoni, tinfetaħ tieqa modali biex iżżid konnessjoni:

Hawnhekk, it-tagħbija ta' lista kompluta tal-istanzi kollha ta' MS SQL Server aċċessibbli fuq netwerks lokali għadha ma taħdimx.

Tista' wkoll iżżid konnessjoni billi tuża l-buttuna l-aktar fuq ix-xellug fil-menu prinċipali:

Sussegwentement, se jitniedu l-mistoqsijiet li ġejjin lid-DBMS:

1. Il-ksib ta' informazzjoni dwar id-DBMS

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

2. Jkollna lista ta 'databases disponibbli bil-proprjetajiet fil-qosor tagħhom

   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
   

Wara li tesegwixxi l-iskripts ta’ hawn fuq, se tidher tieqa li fiha informazzjoni qasira dwar id-databases tal-istanza magħżula ta’ MS SQL Server:

Ta’ min jinnota li l-informazzjoni estiża tintwera bbażata fuq id-drittijiet. Jekk hemm sysadmin, allura tista 'tagħżel data mill-veduta sys.master_files. Jekk ma jkunx hemm tali drittijiet, allura inqas data tiġi sempliċement ritornata sabiex ma titnaqqasx it-talba.

Hawnhekk għandek bżonn tagħżel id-databases ta 'interess u kklikkja fuq il-buttuna "OK".

Sussegwentement, l-iskrittura li ġejja se tiġi esegwita għal kull database magħżula biex tanalizza l-istat tal-indiċi:

Analiżi tal-istatus tal-indiċi

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

Kif jidher mill-mistoqsijiet nfushom, it-tabelli temporanji jintużaw spiss. Dan isir sabiex ma jkun hemm l-ebda rikompilazzjoni, u fil-każ ta 'skema kbira, il-pjan jista' jiġi ġġenerat b'mod parallel meta tiddaħħal id-dejta, peress li d-dħul ta 'varjabbli tat-tabella huwa possibbli biss f'ħajt wieħed.

Wara li tesegwixxi l-iskript ta 'hawn fuq, se tidher tieqa b'tabella ta' indiċi:

Tista' wkoll turi informazzjoni dettaljata oħra hawn, bħal:

1. database
2. numru ta' taqsimiet
3. data u ħin tal-aħħar sejħa
4. għafas
5. grupp tal-fajls

eċċ.
Il-kelliema nfushom jistgħu jiġu personalizzati:

Fiċ-ċelloli tal-kolonna Waħħal, tista 'tagħżel liema azzjoni se titwettaq waqt l-ottimizzazzjoni. Ukoll, meta titlesta l-iskannjar, tintgħażel azzjoni awtomatika bbażata fuq is-settings magħżula:

Trid tagħżel l-indiċi mixtieqa għall-ipproċessar.

Bl-użu tal-menu prinċipali, tista 'tiffranka l-iskrittura (l-istess buttuna tibda l-proċess ta' ottimizzazzjoni tal-indiċi stess):

u ssejvja t-tabella f'formati differenti (l-istess buttuna tippermettilek tiftaħ settings dettaljati għall-analiżi u l-ottimizzazzjoni tal-indiċi):

Tista' wkoll taġġorna l-informazzjoni billi tikklikkja fuq it-tielet buttuna fuq ix-xellug fil-menu prinċipali ħdejn il-lenti.

Il-buttuna b'lenti tippermettilek tagħżel id-databases mixtieqa għall-konsiderazzjoni.

Bħalissa m'hemm l-ebda sistema ta' għajnuna komprensiva. Għalhekk, tagħfas il-buttuna "?" sempliċement tikkawża tieqa modali li tidher li fiha informazzjoni bażika dwar il-prodott tas-softwer:

Minbarra dak kollu deskritt hawn fuq, il-menu prinċipali għandu bar tat-tiftix:

Meta tibda l-proċess tal-ottimizzazzjoni tal-indiċi:

Tista' wkoll tara log tal-azzjonijiet imwettqa fil-qiegħ tat-tieqa:

Fit-tieqa dettaljata tas-settings għall-analiżi u l-ottimizzazzjoni tal-indiċi, tista’ tikkonfigura għażliet aktar sottili:

Talbiet għall-applikazzjoni:

1. jagħmilha possibbli li l-istatistika tiġi aġġornata b'mod selettiv mhux biss għall-indiċi u wkoll b'modi differenti (aġġornament sħiħ jew parzjalment)
2. tagħmilha possibbli mhux biss li tagħżel database, iżda wkoll servers differenti (dan huwa konvenjenti ħafna meta jkun hemm ħafna każijiet ta 'MS SQL Server)
3. Għal flessibbiltà akbar fl-użu, huwa ssuġġerit li l-kmandi jiġu wrap fil-libreriji u joħorġuhom lill-kmandi PowerShell, kif isir, pereżempju, hawn:
4. dbatools.io/commands
5. tagħmilha possibbli li tissejvja u tbiddel is-settings personali kemm għall-applikazzjoni kollha kif ukoll, jekk meħtieġ, għal kull istanza ta’ MS SQL Server u kull database
6. Mill-punti 2 u 4, jirriżulta li trid toħloq gruppi minn databases u gruppi minn istanzi MS SQL Server, li għalihom is-settings huma l-istess
7. fittex indiċijiet duplikati (kompleti u mhux kompluti, li jew huma kemmxejn differenti jew ivarjaw biss fil-kolonni inklużi)
8. Peress li SQLIndexManager jintuża biss għal MS SQL Server DBMS, huwa meħtieġ li dan jiġi rifless fl-isem, pereżempju, kif ġej: SQLIndexManager għal MS SQL Server
9. Ċaqlaq il-partijiet kollha tal-applikazzjoni li mhumiex GUI f'moduli separati u erġgħu iktebhom f'.NET Core 2.1

Fiż-żmien tal-kitba, il-punt 6 tax-xewqat qed jiġi żviluppat b'mod attiv u diġà hemm appoġġ fil-forma ta 'tiftix għal duplikati kompluti u simili:

Sorsi

• metodu ta 'ottimizzazzjoni ta' indiċi adattivi
• Utilità SQLIndexManager
• ħarsa ġenerali teknika qasira tal-utilità SQLIndexManager
• diskussjoni dwar l-utilità SQLIndexManager
• SQL Index Manager - Għodda GUI b'xejn għall-Manutenzjoni tal-Indiċi fuq SQL Server u Azure
• Analiżi u Ottimizzazzjoni tal-Indiċi SQL Server

Sors: www.habr.com