Wie bekannt ist, spielen Indizes eine wichtige Rolle in Datenbanksystemen, indem sie eine schnelle Suche nach den benötigten Einträgen ermöglichen. Daher ist es von großer Bedeutung, sie regelmäßig zu warten. Über die Analyse und Optimierung gibt es eine Fülle von Materialien, auch im Internet. Beispielsweise wurde kürzlich ein Überblick zu diesem Thema in .
Es gibt viele sowohl kostenpflichtige als auch kostenlose Lösungen dafür. Zum Beispiel gibt es eine fertige , die auf einer adaptiven Methode zur Optimierung von Indizes basiert.
Im Folgenden betrachten wir das kostenlose Tool , dessen Autor .
Das Hauptunterschied zwischen SQLIndexManager und einigen anderen Alternativen wird vom Autor selbst beschrieben. und .
In diesem Artikel werfen wir zudem einen Blick auf das Projekt und die Einsatzmöglichkeiten dieser Softwarelösung.
Über dieses Tool wird diskutiert. .
Im Laufe der Zeit wurden die meisten Anmerkungen und Fehler behoben.
Kommen wir nun zur Anwendung SQLIndexManager.
Die Anwendung ist in C# .NET Framework 4.5 in Visual Studio 2017 geschrieben und verwendet DevExpress für die Formulare:
und sieht folgendermaßen aus:
Alle Abfragen werden in den folgenden Dateien erstellt:
- Index
- Query
- QueryEngine
- ServerInfo
Beim Verbinden mit der Datenbank und dem Senden von Anfragen an das DBMS erfolgt die Anwendung wie folgt:
ApplicationName="SQLIndexManager" Beim Starten der Anwendung öffnet sich ein modales Fenster zur Hinzufügung einer Verbindung:
Hier wird derzeit noch keine vollständige Liste aller Instanzen von MS SQL Server, die über lokale Netzwerke verfügbar sind, geladen.
Eine Verbindung kann auch über die ganz linke Schaltfläche im Hauptmenü hinzugefügt werden:
Anschließend werden folgende Anfragen an das DBMS ausgeführt:
- Abruf von Informationen über das DBMS
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Abruf der verfügbaren Datenbanken mit ihren kurzen Eigenschaften
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
Nach der Ausführung der oben genannten Skripte erscheint ein Fenster mit einer kurzen Übersicht über die Datenbanken der ausgewählten MS SQL Server-Instanz:
Es ist erwähnenswert, dass die erweiterten Informationen basierend auf den Berechtigungen angezeigt werden. Wenn die , vorhanden sind, können Daten aus der Ansicht ausgewählt werden. Andernfalls werden einfach weniger Daten zurückgegeben, um die Abfrage nicht zu verlangsamen.
Hier müssen die interessierenden Datenbanken ausgewählt werden, und Sie klicken auf die Schaltfläche „OK“.
Anschließend wird das folgende Skript für jede ausgewählte Datenbank zur Analyse des Indexzustands ausgeführt:
Analyse des Indexzustands
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)
)
Wie aus den Anfragen selbst hervorgeht, werden temporäre Tabellen häufig verwendet. Dies geschieht, um eine Neukompilierung zu vermeiden, und bei komplexen Schemata kann ein Plan parallel beim Einfügen von Daten generiert werden, da das Einfügen mit Tabellenspeicher-Variablen nur in einem einzigen Thread möglich ist.
Nach der Ausführung des obigen Skripts erscheint ein Fenster mit der Indextabelle:
Hier können auch weitere detaillierte Informationen angezeigt werden, wie zum Beispiel:
- Datenbank
- Anzahl der Bereiche
- Datum und Uhrzeit der letzten Abfrage
- Kompression
- Dateigruppe
usw. verwendet.
Die Spalten selbst sind anpassbar:
In den Zellen der Fix-Spalte können Sie auswählen, welche Maßnahmen bei der Optimierung durchgeführt werden. Bei Abschluss des Scans wird auch die Standardaktion basierend auf den gewählten Einstellungen ausgewählt:
Es müssen die erforderlichen Indizes zur Verarbeitung ausgewählt werden.
Über das Hauptmenü kann sowohl das Skript gespeichert werden (dieser Button startet auch den Optimierungsprozess der Indizes):
als auch die Tabelle in verschiedene Formate gespeichert werden (dieser Button öffnet auch die detaillierten Einstellungen zur Analyse und Optimierung der Indizes):
Sie können die Informationen auch aktualisieren, indem Sie auf die dritte Schaltfläche von links im Hauptmenü neben der Lupe klicken.
Die Lupe ermöglicht es Ihnen, die Datenbanken auszuwählen, die Sie betrachten möchten.
Derzeit gibt es kein vollständig funktionierendes Hilfesystem. Ein Klick auf die Schaltfläche „?“ führt lediglich zu einem Popup-Fenster mit grundlegenden Informationen über das Produkt:
Zusätzlich zu den oben beschriebenen Funktionen gibt es im Hauptmenü eine Suchleiste:
Beim Start des Indexoptimierungsprozesses:
Unten im Fenster können Sie auch das Protokoll der durchgeführten Aktionen einsehen:
Im Fenster der detaillierten Einstellungen für Analyse und Optimierung der Indizes können Sie spezifischere Optionen anpassen:
Wünsche für die Anwendung:
- die Möglichkeit, die Statistiken nicht nur für Indizes selektiv zu aktualisieren und dabei verschiedene Methoden (komplett oder teilweise) zu verwenden.
- die Möglichkeit, nicht nur Datenbanken auszuwählen, sondern auch verschiedene Server (was sehr praktisch ist, wenn viele Instanzen von MS SQL Server vorhanden sind).
- Um mehr Flexibilität in der Nutzung zu gewährleisten, wird vorgeschlagen, die Befehle in Bibliotheken zu kapseln und sie in PowerShell-Befehle auszugeben, wie hier beispielhaft dargestellt:
- Es ermöglichen, persönliche Einstellungen sowohl für die gesamte Anwendung als auch bei Bedarf für jede Instanz von MS SQL Server und jede Datenbank zu speichern und zu ändern.
- Aus Punkt 2 und 4 resultiert der Wunsch, Gruppen nach Datenbanken und Gruppen nach Instanzen von MS SQL Server zu erstellen, für die die Einstellungen identisch sind.
- Suche nach doppelten Indizes (vollständigen und unvollständigen, die sich entweder kaum unterscheiden oder nur in Bezug auf eingeschlossene Spalten unterscheiden).
- Da SQLIndexManager nur für die Datenbankmanagementsysteme MS SQL Server verwendet wird, sollte dies im Namen reflektiert werden, beispielsweise so: SQLIndexManager für MS SQL Server.
- Alle Teile der Anwendung, die nicht über die GUI verfügen, sollten in separate Module ausgelagert und auf .NET Core 2.1 neu geschrieben werden.
Zum Zeitpunkt der Erstellung dieses Artikels wird Punkt 6 aus den Wünschen aktiv entwickelt und es gibt bereits Unterstützung in Form der Suche nach vollständigen und ähnlichen Duplikaten:
Quellen
Quelle: habr.com
