Rezension des kostenlosen Tools SQLIndexManager

Wie Sie wissen, spielen Indizes in einem DBMS eine wichtige Rolle und ermöglichen eine schnelle Suche nach den erforderlichen Datensätzen. Deshalb ist es so wichtig, sie rechtzeitig zu warten. Über Analyse und Optimierung wurde schon viel Material geschrieben, auch im Internet. Dieses Thema wurde beispielsweise kürzlich in besprochen dieser Veröffentlichung.

Hierfür gibt es viele kostenpflichtige und kostenlose Lösungen. Es gibt zum Beispiel ein Fertigprodukt Entscheidung, basierend auf einer adaptiven Indexoptimierungsmethode.

Schauen wir uns als Nächstes das kostenlose Dienstprogramm an SQLIndexManager, geschrieben von AlanDenton.

Der wichtigste technische Unterschied zwischen SQLIndexManager und einer Reihe anderer Analoga wird vom Autor selbst angegeben hier и hier.

In diesem Artikel werfen wir einen Blick von außen auf das Projekt und die betrieblichen Möglichkeiten dieser Softwarelösung.

Diskussion dieses Dienstprogramms hier.
Im Laufe der Zeit wurden die meisten Kommentare und Fehler behoben.

Kommen wir nun zum Dienstprogramm SQLIndexManager selbst.

Die Anwendung ist in C# .NET Framework 4.5 in Visual Studio 2017 geschrieben und verwendet DevExpress für Formulare:

und sieht so aus:

Alle Anfragen werden in den folgenden Dateien generiert:

1. Index
2. Abfrage
3. Abfrage-Engine
4. ServerInfo

Beim Herstellen einer Verbindung zu einer Datenbank und beim Senden von Abfragen an das DBMS wird die Anwendung wie folgt signiert:

ApplicationName=”SQLIndexManager”

Wenn Sie die Anwendung starten, öffnet sich ein modales Fenster zum Hinzufügen einer Verbindung:

Hier funktioniert das Laden einer vollständigen Liste aller über lokale Netzwerke erreichbaren MS SQL Server-Instanzen noch nicht.

Sie können eine Verbindung auch über die Schaltfläche ganz links im Hauptmenü hinzufügen:

Als nächstes werden die folgenden Abfragen an das DBMS gestartet:

1. Erhalten von Informationen über das DBMS

   SELECT ProductLevel  = SERVERPROPERTY('ProductLevel')
        , Edition       = SERVERPROPERTY('Edition')
        , ServerVersion = SERVERPROPERTY('ProductVersion')
        , IsSysAdmin    = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT)
   

2. Abrufen einer Liste 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 kurzen Informationen zu den Datenbanken der ausgewählten Instanz von MS SQL Server:

Es ist zu beachten, dass erweiterte Informationen basierend auf Rechten angezeigt werden. Wenn es gibt Sysadmin, dann können Sie Daten aus der Ansicht auswählen sys.master_files. Fehlen solche Rechte, werden einfach weniger Daten zurückgegeben, um die Anfrage nicht zu verlangsamen.

Hier müssen Sie die gewünschten Datenbanken auswählen und auf die Schaltfläche „OK“ klicken.

Als nächstes wird das folgende Skript für jede ausgewählte Datenbank ausgeführt, um den Status der Indizes zu analysieren:

Indexstatusanalyse

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 Abfragen selbst hervorgeht, werden häufig temporäre Tabellen verwendet. Dies geschieht, damit es nicht zu Neukompilierungen kommt und bei einem großen Schema der Plan beim Einfügen von Daten parallel generiert werden kann, da das Einfügen von Tabellenvariablen nur in einem Thread möglich ist.

Nach der Ausführung des obigen Skripts erscheint ein Fenster mit einer Indextabelle:

Hier können Sie sich auch weitere Detailinformationen anzeigen lassen, wie zum Beispiel:

1. Datenbank
2. Anzahl der Abschnitte
3. Datum und Uhrzeit des letzten Anrufs
4. Kontraktion
5. Dateigruppe

usw.
Die Lautsprecher selbst können individuell angepasst werden:

In den Zellen der Spalte „Fix“ können Sie auswählen, welche Aktion während der Optimierung ausgeführt werden soll. Außerdem wird nach Abschluss des Scans eine Standardaktion basierend auf den ausgewählten Einstellungen ausgewählt:

Sie müssen die gewünschten Indizes zur Verarbeitung auswählen.

Über das Hauptmenü können Sie das Skript speichern (die gleiche Schaltfläche startet den Indexoptimierungsprozess selbst):

und speichern Sie die Tabelle in verschiedenen Formaten (über dieselbe Schaltfläche können Sie detaillierte Einstellungen zur Analyse und Optimierung von Indizes öffnen):

Sie können die Informationen auch aktualisieren, indem Sie im Hauptmenü links neben der Lupe auf die dritte Schaltfläche klicken.

Über die Schaltfläche mit der Lupe können Sie die gewünschten Datenbanken zur Betrachtung auswählen.

Derzeit gibt es kein umfassendes Hilfesystem. Drücken Sie daher die „?“-Taste führt lediglich dazu, dass ein modales Fenster mit grundlegenden Informationen zum Softwareprodukt angezeigt wird:

Zusätzlich zu allem, was oben beschrieben wurde, verfügt das Hauptmenü über eine Suchleiste:

Beim Starten des Indexoptimierungsprozesses:

Sie können auch ein Protokoll der durchgeführten Aktionen unten im Fenster anzeigen:

Im Detaileinstellungsfenster zur Indexanalyse und -optimierung können Sie subtilere Optionen konfigurieren:

Anfragen zur Bewerbung:

1. ermöglichen die selektive Aktualisierung von Statistiken nicht nur für Indizes, sondern auch auf unterschiedliche Weise (vollständig oder teilweise aktualisieren).
2. Ermöglichen Sie nicht nur die Auswahl einer Datenbank, sondern auch verschiedener Server (dies ist sehr praktisch, wenn viele Instanzen von MS SQL Server vorhanden sind).
3. Für eine größere Flexibilität bei der Verwendung empfiehlt es sich, die Befehle in Bibliotheken zu packen und an PowerShell-Befehle auszugeben, wie dies beispielsweise hier geschieht:
4. dbatools.io/commands
5. ermöglichen das Speichern und Ändern persönlicher Einstellungen sowohl für die gesamte Anwendung als auch bei Bedarf für jede Instanz von MS SQL Server und jede Datenbank
6. Aus den Absätzen 2 und 4 folgt, dass Sie Gruppen nach Datenbanken und Gruppen nach MS SQL Server-Instanzen erstellen möchten, für die die Einstellungen gleich sind
7. Suche nach doppelten Indizes (vollständig und unvollständig, die sich entweder geringfügig unterscheiden oder sich nur in den enthaltenen Spalten unterscheiden)
8. Da SQLIndexManager nur für MS SQL Server DBMS verwendet wird, muss sich dies im Namen widerspiegeln, beispielsweise wie folgt: SQLIndexManager für MS SQL Server
9. Verschieben Sie alle Nicht-GUI-Teile der Anwendung in separate Module und schreiben Sie sie in .NET Core 2.1 neu

Punkt 6 der Wünsche wird zum Zeitpunkt des Schreibens aktiv weiterentwickelt und es gibt bereits Unterstützung in Form der Suche nach vollständigen und ähnlichen Duplikaten:

Quellen

• Methode zur adaptiven Indexoptimierung
• SQLIndexManager-Dienstprogramm
• Kurzer technischer Überblick über das Dienstprogramm SQLIndexManager
• Diskussion über das Dienstprogramm SQLIndexManager
• SQL Index Manager – Kostenloses GUI-Tool für die Indexpflege auf SQL Server und Azure
• Analyse und Optimierung des SQL Server-Index

Source: habr.com