Comu sapete, l'indici ghjucanu un rolu impurtante in un DBMS, chì furnisce una ricerca rapida à i registri richiesti. Hè per quessa chì hè cusì impurtante di serviziu à elli in una manera puntuale. Moltu materiale hè statu scrittu annantu à l'analisi è ottimisazione, ancu in Internet. Per esempiu, stu tema hè statu rivista recentemente in sta publicazione.
Ci sò parechje soluzioni pagate è libere per questu. Per esempiu, ci hè un ready-made решение, basatu annantu à un metudu di ottimisazione di l'indici adattativu.
Dopu, fighjemu l'utilità libera SQLIndexManager, autore di Alan Denton.
A principal diferenza tecnica trà SQLIndexManager è una quantità di altri analoghi hè datu da l'autore stessu ccà и ccà.
In questu articulu, avemu da piglià un ochju fora di u prugettu è e capacità operative di sta suluzione software.
Discutendu sta utilità ccà.
À u tempu, a maiò parte di i cumenti è i bug sò stati corretti.
Allora, andemu avà à l'utilità SQLIndexManager stessu.
L'applicazione hè scritta in C# .NET Framework 4.5 in Visual Studio 2017 è usa DevExpress per e forme:
è pare cusì:
Tutte e dumande sò generate in i seguenti schedari:
Index
Query
QueryEngine
ServerInfo
Quandu si cunnetta à una basa di dati è invià e dumande à u DBMS, l'applicazione hè firmata cusì:
ApplicationName=”SQLIndexManager”
Quandu lanciate l'applicazione, si apre una finestra modale per aghjunghje una cunnessione:
Quì, carica una lista cumpleta di tutte e istanze di MS SQL Server accessibule nantu à e rete lucali ùn funziona ancu.
Pudete ancu aghjunghje una cunnessione cù u buttone più à manca in u menu principale:
In seguitu, e seguenti dumande à u DBMS seranu lanciate:
Ottene una lista di basa di dati dispunibuli cù e so proprietà brevi
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
Dopu avè eseguitu i scripts sopra, apparirà una finestra chì cuntene brevi informazioni nantu à e basa di dati di l'istanza scelta di MS SQL Server:
Vale a pena nutà chì l'infurmazione estesa hè mostrata nantu à i diritti. S'ellu ci hè sysadmin, allura vi ponu selezziunà dati da a vista sys.master_files. Se ùn ci sò micca tali diritti, allora menu dati hè solu vultatu per ùn rallentà a dumanda.
Quì avete bisognu di selezziunà a basa di dati di interessu è cliccate nant'à u buttone "OK".
Dopu, u script seguente serà eseguitu per ogni basa di dati selezziunata per analizà u statu di l'indici:
Analisi di u statutu di l'indice
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)
)
Comu pò esse vistu da e dumande stessu, i tavule tempurane sò usati abbastanza spessu. Questu hè fattu in modu chì ùn ci hè micca ricumpilazione, è in u casu di un grande schema, u pianu pò esse generatu in parallelu à l'inserimentu di dati, postu chì l'inserimentu di variàbili di tavula hè pussibule solu in un filu.
Dopu avè eseguitu u script sopra, una finestra cù una tabella d'indici appariscerà:
Pudete ancu vede altre informazioni dettagliate quì, cum'è:
basa di dati
numeru di sezzioni
data è ora di l'ultima chjama
cumpressione
gruppu di file
i t. d.
I parlanti stessi ponu esse persunalizati:
In i celi di a colonna Fix, pudete selezziunate quale azzione serà realizatu durante l'ottimisazione. Inoltre, quandu a scansione finisci, una azzione predeterminata hè selezziunata basatu annantu à i paràmetri scelti:
Avete bisognu di selezziunà l'indici desiderati per u processu.
Utilizendu u menu principale, pudete salvà u script (u stessu buttone principia u prucessu di ottimisazione di l'indici stessu):
è salvà a tavula in diversi formati (u stessu buttone vi permette di apre paràmetri detallati per analizà è ottimisate l'indici):
Pudete ancu aghjurnà l'infurmazioni clicchendu nantu à u terzu buttone à manca in u menù principale accantu à a lente d'ingrandimentu.
U buttone cù una lupa vi permette di selezziunà e basa di dati desiderate per cunsiderà.
Ùn ci hè attualmente un sistema di aiutu cumpletu. Dunque, pressu u buttone "?" Semplicemente pruvucarà una finestra modale chì cuntene l'infurmazioni basi nantu à u pruduttu software:
In più di tuttu ciò chì hè descrittu sopra, u menu principale hà una barra di ricerca:
Quandu principia u prucessu di ottimisazione di l'indici:
Pudete ancu vede un logu di l'azzioni realizati in u fondu di a finestra:
In a finestra di paràmetri detallati per l'analisi di l'indici è l'ottimisazione, pudete cunfigurà opzioni più sottili:
Richieste per l'applicazione:
permette di aghjurnà selettivamente e statistiche micca solu per l'indici è ancu in modi diffirenti (aghjurnà cumplettamente o parzialmente)
rende pussibule micca solu di selezziunà una basa di dati, ma ancu diversi servitori (questu hè assai cunvene quandu ci sò parechje casi di MS SQL Server)
Per una più grande flessibilità in l'usu, hè suggeritu di imbulighjà i cumandamenti in biblioteche è di emette à i cumandamenti di PowerShell, cum'è hè fattu, per esempiu, quì:
permette di salvà è cambià i paràmetri persunali sia per tutta l'applicazione sia, se ne necessariu, per ogni istanza di MS SQL Server è ogni basa di dati
Da i punti 2 è 4, segue chì vulete creà gruppi per basa di dati è gruppi per istanze MS SQL Server, per quale i paràmetri sò listessi.
cercate l'indici duplicati (completi è incompleti, chì sò ligeramente diffirenti o sò diffirenti solu in e colonne incluse)
Siccomu SQLIndexManager hè utilizatu solu per MS SQL Server DBMS, hè necessariu di riflette questu in u nome, per esempiu, cum'è seguente: SQLIndexManager per MS SQL Server
Sposta tutte e parti non-GUI di l'applicazione in moduli separati è riscrivite in .NET Core 2.1
À u mumentu di a scrittura, l'articulu 6 di i desideri hè attivamente sviluppatu è ci hè digià supportu in a forma di ricerca di duplicati cumpleti è simili: