Kaip žinote, indeksai vaidina svarbų vaidmenį DBVS, suteikdami greitą reikiamų įrašų paiešką. Štai kodėl labai svarbu juos aptarnauti laiku. Apie analizę ir optimizavimą parašyta gana daug medžiagos, taip pat ir internete. Pavyzdžiui, ši tema neseniai buvo peržiūrėta .
Tam yra daug mokamų ir nemokamų sprendimų. Pavyzdžiui, yra paruoštas , remiantis adaptyviu indekso optimizavimo metodu.
Toliau pažvelkime į nemokamą įrankį , autorius .
Pagrindinį techninį skirtumą tarp SQLIndexManager ir daugelio kitų analogų pateikia pats autorius и .
Šiame straipsnyje mes iš išorės pažvelgsime į projektą ir šio programinės įrangos sprendimo veikimo galimybes.
Šio naudingumo aptarimas .
Laikui bėgant dauguma komentarų ir klaidų buvo ištaisyti.
Taigi, pereikime prie pačios „SQLIndexManager“ priemonės.
Programa parašyta C# .NET Framework 4.5 Visual Studio 2017 ir naudoja DevExpress formas:
ir atrodo taip:
Visos užklausos generuojamos šiuose failuose:
- rodyklė
- Užklausa
- QueryEngine
- Serverio informacija
Prisijungiant prie duomenų bazės ir siunčiant užklausas į DBVS, programa pasirašoma taip:
ApplicationName=”SQLIndexManager” Kai paleisite programą, atsidarys modalinis langas, kuriame bus galima pridėti ryšį:
Čia dar neveikia visas MS SQL Server egzempliorių, pasiekiamų vietiniais tinklais, sąrašo įkėlimas.
Taip pat galite pridėti ryšį naudodami kairįjį pagrindinio meniu mygtuką:
Tada bus paleistos šios DBVS užklausos:
- Informacijos apie DBVS gavimas
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Galimų duomenų bazių sąrašo su jų trumpomis savybėmis gavimas
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
Įvykdžius aukščiau nurodytus scenarijus, pasirodys langas su trumpa informacija apie pasirinkto MS SQL Server egzemplioriaus duomenų bazes:
Verta paminėti, kad išplėstinė informacija rodoma remiantis teisėmis. Jeigu ten , tada galite pasirinkti duomenis iš rodinio . Jei tokių teisių nėra, paprasčiausiai grąžinama mažiau duomenų, kad nesulėtėtų užklausos vykdymas.
Čia reikia pasirinkti dominančias duomenų bazes ir spustelėti mygtuką „Gerai“.
Tada kiekvienai pasirinktai duomenų bazei bus vykdomas šis scenarijus, kad būtų galima analizuoti indeksų būseną:
Indekso būklės analizė
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)
)
Kaip matyti iš pačių užklausų, laikinosios lentelės naudojamos gana dažnai. Tai daroma tam, kad nebūtų perkompiliacijų, o didelės schemos atveju planas gali būti generuojamas lygiagrečiai įterpiant duomenis, nes lentelės kintamuosius įterpti galima tik vienoje gijoje.
Įvykdžius aukščiau pateiktą scenarijų, pasirodys langas su rodyklės lentele:
Čia taip pat galite rodyti kitą išsamią informaciją, pvz.:
- duomenų bazėje
- sekcijų skaičius
- paskutinio skambučio data ir laikas
- suspaudimas
- failų grupė
ir k. d.
Patys garsiakalbiai gali būti pritaikyti:
Stulpelio Fix langeliuose galite pasirinkti, koks veiksmas bus atliktas optimizuojant. Be to, kai nuskaitymas baigiamas, pagal pasirinktus nustatymus pasirenkamas numatytasis veiksmas:
Turite pasirinkti norimus apdorojimo indeksus.
Naudodami pagrindinį meniu galite išsaugoti scenarijų (tas pats mygtukas pradeda patį indekso optimizavimo procesą):
ir išsaugoti lentelę skirtingais formatais (tas pats mygtukas leidžia atidaryti išsamius indeksų analizės ir optimizavimo nustatymus):
Informaciją taip pat galite atnaujinti spustelėdami trečią mygtuką kairėje pagrindiniame meniu šalia didinamojo stiklo.
Mygtukas su padidinamuoju stiklu leidžia pasirinkti norimas duomenų bazes svarstymui.
Šiuo metu nėra išsamios pagalbos sistemos. Todėl paspausdami mygtuką "?" tiesiog atsiras modalinis langas su pagrindine informacija apie programinės įrangos produktą:
Be to, kas aprašyta aukščiau, pagrindiniame meniu yra paieškos juosta:
Pradėdami indekso optimizavimo procesą:
Taip pat lango apačioje galite peržiūrėti atliktų veiksmų žurnalą:
Išsamiame indekso analizės ir optimizavimo nustatymų lange galite konfigūruoti subtilesnes parinktis:
Prašymai dėl paraiškos:
- leidžia pasirinktinai atnaujinti ne tik indeksų statistiką, bet ir įvairiais būdais (visiškai arba iš dalies atnaujinti)
- leidžia pasirinkti ne tik duomenų bazę, bet ir skirtingus serverius (tai labai patogu, kai yra daug MS SQL Server egzempliorių)
- Siekiant didesnio naudojimo lankstumo, siūloma sudėti komandas į bibliotekas ir išvesti jas į PowerShell komandas, kaip tai daroma, pavyzdžiui, čia:
- leidžia išsaugoti ir keisti asmeninius nustatymus tiek visai programai, tiek, jei reikia, kiekvienam MS SQL Server egzemplioriui ir kiekvienai duomenų bazei
- Iš 2 ir 4 punktų matyti, kad norite sukurti grupes pagal duomenų bazes ir grupes pagal MS SQL Server egzempliorius, kurių parametrai yra tokie patys
- ieškoti pasikartojančių indeksų (užbaigtų ir neišsamių, kurie šiek tiek skiriasi arba skiriasi tik įtrauktuose stulpeliuose)
- Kadangi SQLIndexManager naudojamas tik MS SQL Server DBVS, būtina tai atspindėti pavadinime, pavyzdžiui, taip: SQLIndexManager for MS SQL Server
- Perkelkite visas ne GUI programos dalis į atskirus modulius ir perrašykite jas .NET Core 2.1
Rašymo metu 6 pageidavimų punktas yra aktyviai plėtojamas ir jau yra palaikymas ieškant pilnų ir panašių dublikatų:
Informacijos šaltiniai
Šaltinis: www.habr.com
