După cum știți, indexurile joacă un rol important într-un SGBD, oferind o căutare rapidă a înregistrărilor necesare. De aceea este atât de important să le deserviți în timp util. S-a scris destul de mult material despre analiză și optimizare, inclusiv pe Internet. De exemplu, acest subiect a fost revizuit recent în .
Există multe soluții plătite și gratuite pentru aceasta. De exemplu, există un gata făcut , bazat pe o metodă de optimizare adaptivă a indicilor.
În continuare, să ne uităm la utilitarul gratuit , scris de .
Principala diferență tehnică dintre SQLIndexManager și o serie de alți analogi este dată de autor însuși и .
În acest articol, vom arunca o privire din exterior asupra proiectului și a capabilităților operaționale ale acestei soluții software.
Discutarea acestui utilitar .
De-a lungul timpului, majoritatea comentariilor și erorilor au fost corectate.
Deci, să trecem acum la utilitarul SQLIndexManager în sine.
Aplicația este scrisă în C# .NET Framework 4.5 în Visual Studio 2017 și folosește DevExpress pentru formulare:
și arată astfel:
Toate cererile sunt generate în următoarele fișiere:
- index
- Întrebare
- QueryEngine
- ServerInfo
Când vă conectați la o bază de date și trimiteți interogări către SGBD, aplicația este semnată după cum urmează:
ApplicationName=”SQLIndexManager”
Când lansați aplicația, se va deschide o fereastră modală pentru a adăuga o conexiune:
Aici, încărcarea unei liste complete a tuturor instanțelor MS SQL Server accesibile prin rețelele locale nu funcționează încă.
De asemenea, puteți adăuga o conexiune folosind butonul din stânga din meniul principal:
În continuare, vor fi lansate următoarele interogări către SGBD:
- Obținerea de informații despre SGBD
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Obținerea unei liste de baze de date disponibile cu proprietățile lor scurte
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
După executarea scripturilor de mai sus, va apărea o fereastră care conține informații scurte despre bazele de date ale instanței selectate a MS SQL Server:
Este de remarcat faptul că informațiile extinse sunt afișate pe baza drepturilor. În cazul în care există , apoi puteți selecta date din vizualizare . Dacă nu există astfel de drepturi, atunci pur și simplu se returnează mai puține date pentru a nu încetini solicitarea.
Aici trebuie să selectați bazele de date de interes și să faceți clic pe butonul „OK”.
În continuare, următorul script va fi executat pentru fiecare bază de date selectată pentru a analiza starea indecșilor:
Analiza stării indexului
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)
)
După cum se poate vedea din interogările în sine, tabelele temporare sunt folosite destul de des. Acest lucru se face astfel încât să nu existe recompilări, iar în cazul unei scheme mari, planul poate fi generat în paralel la inserarea datelor, deoarece inserarea variabilelor de tabel este posibilă doar într-un singur fir.
După executarea scriptului de mai sus, va apărea o fereastră cu un tabel de index:
De asemenea, puteți afișa și alte informații detaliate aici, cum ar fi:
- Bază de date
- număr de secțiuni
- data și ora ultimului apel
- comprimare
- grup de fișiere
etc
Difuzoarele în sine pot fi personalizate:
În celulele coloanei Fix, puteți selecta ce acțiune va fi efectuată în timpul optimizării. De asemenea, când scanarea se finalizează, o acțiune implicită este selectată pe baza setărilor selectate:
Trebuie să selectați indecșii doriti pentru procesare.
Folosind meniul principal, puteți salva scriptul (același buton începe procesul de optimizare a indexului în sine):
și salvați tabelul în formate diferite (același buton vă permite să deschideți setări detaliate pentru analiza și optimizarea indicilor):
De asemenea, puteți actualiza informațiile făcând clic pe al treilea buton din stânga din meniul principal de lângă lupă.
Butonul cu lupă vă permite să selectați bazele de date dorite pentru a fi luate în considerare.
În prezent, nu există un sistem de ajutor complet. Prin urmare, apăsând butonul „?” va face pur și simplu să apară o fereastră modală care conține informații de bază despre produsul software:
Pe lângă tot ceea ce este descris mai sus, meniul principal are o bară de căutare:
Când începeți procesul de optimizare a indexului:
De asemenea, puteți vizualiza un jurnal al acțiunilor efectuate în partea de jos a ferestrei:
În fereastra de setări detaliate pentru analiza și optimizarea indexului, puteți configura opțiuni mai subtile:
Cereri pentru cerere:
- face posibilă actualizarea selectivă a statisticilor nu numai pentru indici și, de asemenea, în diferite moduri (actualizare completă sau parțială)
- face posibilă nu numai selectarea unei baze de date, ci și a diferitelor servere (acest lucru este foarte convenabil când există multe instanțe de MS SQL Server)
- Pentru o mai mare flexibilitate în utilizare, se recomandă să împachetați comenzile în biblioteci și să le trimiteți la comenzile PowerShell, așa cum se face, de exemplu, aici:
- fac posibilă salvarea și modificarea setărilor personale atât pentru întreaga aplicație, cât și, dacă este necesar, pentru fiecare instanță a MS SQL Server și fiecare bază de date
- Din punctele 2 și 4 rezultă că doriți să creați grupuri după baze de date și grupuri după instanțe MS SQL Server, pentru care setările sunt aceleași
- căutați indecși duplicați (compleți și incompleti, care fie sunt ușor diferiți, fie diferă doar în coloanele incluse)
- Deoarece SQLIndexManager este utilizat numai pentru MS SQL Server DBMS, este necesar să se reflecte acest lucru în nume, de exemplu, după cum urmează: SQLIndexManager pentru MS SQL Server
- Mutați toate părțile non-GUI ale aplicației în module separate și rescrieți-le în .NET Core 2.1
La momentul scrierii, punctul 6 al dorințelor este dezvoltat în mod activ și există deja sprijin sub forma căutării de duplicate complete și similare:
surse
Sursa: www.habr.com