Überblick über das kostenlose Tool SQLIndexManager

Wie bekannt ist, spielen Indizes eine wichtige Rolle in DBMS, da sie eine schnelle Suche nach den benötigten Datensätzen ermöglichen. Daher ist es so wichtig, sie rechtzeitig zu warten. Es gibt bereits viele Materialien zu Analyse und Optimierung, auch im Internet. Kürzlich wurde beispielsweise ein Überblick zu diesem Thema in dieser Veröffentlichung.

Es gibt viele sowohl kostenpflichtige als auch kostenlose Lösungen dafür. Zum Beispiel gibt es eine fertige Lösung, die auf der adaptiven Methode zur Optimierung von Indizes basiert.

Als Nächstes betrachten wir das kostenlose Tool SQLIndexManager, dessen Autor AlanDenton.

Der Hauptunterschied zwischen SQLIndexManager und einer Reihe anderer ähnlicher Programme wird vom Autor selbst hervorgehoben. hier und hier.

In diesem Artikel werfen wir einen Blick auf das Projekt und die Möglichkeiten der Nutzung dieser Softwarelösung.

Diese Anwendung wird diskutiert, hier.
Im Laufe der Zeit wurden die meisten Anmerkungen und Fehler behoben.

Lass uns nun zur Software SQLIndexManager übergehen.

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 generiert:

1. Index
2. Abfrage
3. QueryEngine
4. ServerInfo

Bei der Verbindung zur Datenbank und dem Versenden von Abfragen an das DBMS wird die Anwendung wie folgt registriert:

ApplicationName="SQLIndexManager"

Beim Starten der Anwendung öffnet sich ein modales Fenster zum Hinzufügen einer Verbindung:

Hier funktioniert die vollständige Liste aller gehosteten MS SQL Server-Instanzen im lokalen Netzwerk noch nicht.

Eine Verbindung kann auch mit der ganz linken Schaltfläche im Hauptmenü hinzugefügt werden:

Anschließend werden die folgenden Abfragen an das DBMS ausgeführt:

1. Abrufen von Informationen über das DBMS

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

2. Abrufen der Liste der verfügbaren Datenbanken mit deren 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 obigen Skripte wird ein Fenster angezeigt, das Kurzinformationen über die Datenbanken der ausgewählten MS SQL Server-Instanz enthält:

Es ist wichtig zu beachten, dass die erweiterten Informationen je nach Rechten angezeigt werden. Wenn es sysadmin, können Daten aus der Ansicht ausgewählt werden sys.master_files. Wenn solche Rechte nicht vorhanden sind, werden einfach weniger Daten zurückgegeben, um die Anfrage nicht zu verlangsamen.

Hier müssen die gewünschten Datenbanken ausgewählt und auf die Schaltfläche „OK“ gedrückt werden.

Anschließend wird das folgende Skript für jede ausgewählte Datenbank zur Analyse des Zustands der Indizes ausgeführt:

Analyse des Zustands der Indizes

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 = @Fragmentation
        OR
            i.PagesCount > @PreDescribeSize
        OR
            i.IndexType IN (5, 6)
    )

Wie aus den Anfragen selbst ersichtlich ist, werden temporäre Tabellen häufig verwendet. Dies geschieht, um Rekompilationen zu vermeiden, und im Falle großer Schemata kann der Plan parallel beim Einfügen von Daten generiert werden, da das Einfügen mit Tabellenvariablen nur in einem Thread möglich ist.

Nach Ausführung des oben genannten Skripts erscheint ein Fenster mit der Index-Tabelle:

Hier können auch andere detaillierte Informationen ausgegeben werden, wie:

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

usw.
Die Spalten selbst können konfiguriert werden:

In den Zellen der Fix-Spalte kann ausgewählt werden, welche Aktion bei der Optimierung ausgeführt werden soll. Auch nach Abschluss des Scans wird die Standardaktion basierend auf den ausgewählten Einstellungen ausgewählt:

Es müssen die benötigten Indizes zur Bearbeitung ausgewählt werden.

Über das Hauptmenü kann das Skript sowohl gespeichert werden (dieser Knopf startet auch den Optimierungsprozess der Indizes):

als auch die Tabelle in verschiedene Formate gespeichert werden (dieser Knopf öffnet auch die detaillierten Einstellungen zur Analyse und Optimierung der Indizes):

Auch die Informationen können aktualisiert werden, indem man auf die dritte Schaltfläche links im Hauptmenü neben der Lupe klickt.

Die Schaltfläche mit der Lupe ermöglicht die Auswahl der benötigten Datenbanken zur Betrachtung.

Ein vollwertiges Handbuchsystem gibt es derzeit nicht. Daher wird durch Drücken des Knopfes „?“ einfach ein modales Fenster mit grundlegenden Informationen über das Produkt geöffnet:

Neben allem oben Beschriebenen gibt es im Hauptmenü eine Suchzeile:

Beim Start des Optimierungsprozesses für Indizes:

Auch unten im Fenster kann das Protokoll der ausgeführten Aktionen eingesehen werden:

Im Fenster der detaillierten Einstellungen zur Analyse und Optimierung von Indizes können feinere Optionen konfiguriert werden:

Wünsche an die Anwendung:

1. es zu ermöglichen, Statistiken selektiv nur für Indizes und auf verschiedene Arten (vollständig oder teilweise) zu aktualisieren
2. es zu ermöglichen, nicht nur Datenbanken, sondern auch verschiedene Server auszuwählen (sehr praktisch, wenn viele Instanzen des MS SQL Servers vorhanden sind)
3. für größere Flexibilität wird vorgeschlagen, die Befehle in Bibliotheken zu kapseln und in PowerShell-Befehle auszugeben, wie es hier beispielsweise gemacht wurde:
4. dbatools.io/commands
5. ermöglicht es, 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
6. aus Ziff. 2 und 4 ergibt sich der Wunsch, Gruppen nach Datenbanken und Gruppen nach Instanzen von MS SQL Server zu erstellen, für die die Einstellungen identisch sind
7. eine Suche nach doppelten Indizes (vollständige und unvollständige, die entweder wenig abweichen oder sich nur in den enthaltenen Spalten unterscheiden) durchzuführen
8. da SQLIndexManager nur für MS SQL Server-Datenbanken verwendet wird, sollte dies im Namen widerspiegelt werden, beispielsweise: SQLIndexManager for MS SQL Server
9. alle Teile der Anwendung, die kein GUI sind, in separate Module auslagern und auf .NET Core 2.1 neu schreiben

Zum Zeitpunkt der Erstellung dieses Artikels wird Punkt 6 aus den Wünschen aktiv entwickelt und unterstützt bereits die Suche nach vollständigen und ähnlichen Duplikaten:

Quellen

• adaptives Indexoptimierungsverfahren
• Dienstprogramm SQLIndexManager
• kurze technische Übersicht über das Dienstprogramm SQLIndexManager
• Diskussion über das Dienstprogramm SQLIndexManager
• SQL Index Manager – Kostenloses GUI-Tool zur Indexverwaltung auf SQL Server und Azure
• SQL Server Indexanalyse und -optimierung

Quelle: habr.com