Lykas jo witte, spylje yndeksen in wichtige rol yn in DBMS, en jouwe in rappe sykopdracht nei de fereaske records. Dêrom is it sa wichtich om se op 'e tiid te tsjinjen. Oer analyze en optimalisaasje is nochal in soad materiaal skreaun, ek op ynternet. Bygelyks, dit ûnderwerp waard koartlyn besjoen yn .
D'r binne in protte betelle en fergese oplossingen foar dit. Bygelyks, der is in ready-made , basearre op in adaptive yndeksoptimalisaasjemetoade.
Litte wy dan nei it fergese nut sjen , skreaun troch .
It wichtichste technyske ferskil tusken SQLIndexManager en in oantal oare analogen wurdt jûn troch de skriuwer sels и .
Yn dit artikel sille wy nei bûten sjen nei it projekt en de operasjonele mooglikheden fan dizze software-oplossing.
Diskusje oer dit nut .
Yn 'e rin fan' e tiid waarden de measte opmerkingen en bugs korrizjearre.
Dat, lit ús no trochgean nei it SQLIndexManager-hulpprogramma sels.
De applikaasje is skreaun yn C# .NET Framework 4.5 yn Visual Studio 2017 en brûkt DevExpress foar formulieren:
en sjocht der sa út:
Alle oanfragen wurde generearre yn de folgjende bestannen:
- Yndeks
- Query
- QueryEngine
- ServerInfo
By it ferbinen mei in databank en it ferstjoeren fan fragen nei de DBMS, wurdt de applikaasje as folget tekene:
ApplicationName=”SQLIndexManager”
As jo de applikaasje starte, sil in modaal finster iepenje om in ferbining ta te foegjen:
Hjir wurket it laden fan in folsleine list fan alle MS SQL Server-eksimplaren dy't tagonklik binne oer lokale netwurken noch net.
Jo kinne ek in ferbining tafoegje mei de knop meast links yn it haadmenu:
Folgjende sille de folgjende fragen nei de DBMS wurde lansearre:
- It krijen fan ynformaasje oer de DBMS
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - In list krije mei beskikbere databases mei har koarte eigenskippen
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
Nei it útfieren fan de boppesteande skripts sil in finster ferskine mei koarte ynformaasje oer de databases fan it selektearre eksimplaar fan MS SQL Server:
It is de muoite wurdich op te merken dat útwreide ynformaasje wurdt werjûn basearre op rjochten. As dêr , dan kinne jo gegevens selektearje út 'e werjefte . As d'r gjin sokke rjochten binne, dan wurde minder gegevens gewoan weromjûn om it fersyk net te fertrage.
Hjir moatte jo de databases fan belang selektearje en klikje op de knop "OK".
Dêrnei sil it folgjende skript wurde útfierd foar elke selekteare databank om de steat fan 'e yndeksen te analysearjen:
Index status analyze
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)
)
Lykas te sjen is út de fragen sels, wurde tydlike tabellen frij faak brûkt. Dit wurdt dien sadat der gjin recompilations, en yn it gefal fan in grut skema, it plan kin generearre parallel by it ynfoegjen fan gegevens, sûnt ynfoegje tabel fariabelen is mooglik allinnich yn ien tried.
Nei it útfieren fan it boppesteande skript sil in finster mei in yndeksetabel ferskine:
Jo kinne hjir ek oare detaillearre ynformaasje werjaan, lykas:
- databank
- oantal seksjes
- datum en tiid fan lêste oprop
- kompresje
- triemgroep
en sa fierder.
De sprekkers sels kinne oanpast wurde:
Yn 'e sellen fan' e Fix-kolom kinne jo selektearje hokker aksje sil wurde útfierd by optimisaasje. Ek as de scan foltôge, wurdt in standertaksje selektearre op basis fan de selekteare ynstellings:
Jo moatte de winske yndeksen selektearje foar ferwurking.
Mei it haadmenu kinne jo it skript bewarje (deselde knop begjint it yndeksoptimalisaasjeproses sels):
en bewarje de tabel yn ferskate formaten (deselde knop lit jo detaillearre ynstellings iepenje foar it analysearjen en optimalisearjen fan yndeksen):
Jo kinne de ynformaasje ek bywurkje troch te klikken op de tredde knop links yn it haadmenu neist it fergrutglês.
De knop mei in fergrutglês lit jo de winske databases selektearje foar konsideraasje.
Der is op it stuit gjin wiidweidich helpsysteem. Dêrom, op de knop "?" sil gewoan in modaal finster ferskine mei basisynformaasje oer it softwareprodukt:
Neist alles hjirboppe beskreaun hat it haadmenu in sykbalke:
By it starten fan it yndeksoptimalisaasjeproses:
Jo kinne ek in log sjen fan útfierde aksjes ûnderoan it finster:
Yn it detaillearre ynstellingsfinster foar yndeksanalyse en optimisaasje kinne jo mear subtile opsjes konfigurearje:
Oanfragen foar de applikaasje:
- meitsje it mooglik om statistyk selektyf te aktualisearjen net allinich foar yndeksen en ek op ferskate manieren (folslein bywurkje as foar in part)
- meitsje it mooglik om net allinich in databank te selektearjen, mar ek ferskate servers (dit is heul handich as d'r in protte eksimplaren fan MS SQL Server binne)
- Foar gruttere fleksibiliteit yn gebrûk wurdt it suggerearre om de kommando's yn biblioteken te wikkeljen en út te jaan nei PowerShell-kommando's, lykas hjir bygelyks dien wurdt:
- meitsje it mooglik om persoanlike ynstellings op te slaan en te feroarjen foar sawol de heule applikaasje as, as nedich, foar elke eksimplaar fan MS SQL Server en elke databank
- Ut de punten 2 en 4 folget dat jo groepen wolle oanmeitsje troch databases en groepen troch MS SQL Server-eksimplaren, wêrfoar't de ynstellings itselde binne
- sykje nei dûbele yndeksen (folslein en ûnfolslein, dy't in bytsje oars binne of allinich ferskille yn 'e opnommen kolommen)
- Sûnt SQLIndexManager wurdt brûkt allinnich foar MS SQL Server DBMS, is it nedich om te reflektearje dit yn de namme, bygelyks, as folget: SQLIndexManager foar MS SQL Server
- Ferpleats alle net-GUI-dielen fan 'e applikaasje yn aparte modules en skriuw se oer yn .NET Core 2.1
Op it stuit fan dit skriuwen wurdt item 6 fan de winsken aktyf ûntwikkele en is der al stipe yn de foarm fan it sykjen nei folsleine en ferlykbere duplikaten:
Boarnen
Boarne: www.habr.com