Geekly articles weekly

Übersicht über das kostenlose SQLIndexManager-Tool

Wie Sie wissen, spielen Indizes im DBMS eine wichtige Rolle und ermöglichen eine schnelle Suche nach den erforderlichen Datensätzen. Daher ist es so wichtig, sie rechtzeitig zu warten. Es wurde viel Material über Analyse und Optimierung geschrieben, auch im Internet. Beispielsweise wurde in dieser Veröffentlichung eine aktuelle Überprüfung dieses Themas vorgenommen.

Hierfür gibt es viele kostenpflichtige und kostenlose Lösungen. Beispielsweise gibt es eine schlüsselfertige Lösung, die auf einer adaptiven Indexoptimierungsmethode basiert.

Betrachten Sie als Nächstes das kostenlose Dienstprogramm SQLIndexManager , das von AlanDenton erstellt wurde .

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

Im selben Artikel werfen wir einen Blick auf das Projekt und die Möglichkeiten der Verwendung dieser Softwarelösung.

Besprechen Sie dieses Dienstprogramm hier .
Im Laufe der Zeit wurden die meisten Kommentare und Fehler behoben.

Kommen wir nun zum Dienstprogramm SQLIndexManager.

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

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



Wenn Sie eine Verbindung zur Datenbank herstellen und Anforderungen an das DBMS senden, wird die Anwendung wie folgt signiert:

ApplicationName=”SQLIndexManager”

Wenn die Anwendung gestartet wird, wird ein modales Fenster geöffnet, in dem Sie eine Verbindung hinzufügen können:


Hier funktioniert das Laden der vollständigen Liste aller über lokale Netzwerke verfügbaren Instanzen von MS SQL Server noch nicht.

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



Als Nächstes werden die folgenden DBMS-Abfragen gestartet:

  1. Abrufen von DBMS-Informationen
     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 dem Ausführen der obigen Skripts wird ein Fenster mit kurzen Informationen zu den Datenbanken der ausgewählten Instanz von MS SQL Server angezeigt:



Es ist erwähnenswert, dass erweiterte Informationen basierend auf Rechten angezeigt werden. Wenn sysadmin vorhanden ist, können Sie Daten aus der Ansicht sys.master_files auswählen. Wenn es keine solchen Rechte gibt, werden weniger Daten zurückgegeben, um die Anforderung nicht zu verlangsamen.

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

Als Nächstes wird für jede ausgewählte Datenbank das folgende Skript ausgeführt, um den Status von 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 Sie den Abfragen selbst entnehmen können, werden häufig temporäre Tabellen verwendet. Dies geschieht so, dass keine Neukompilierung erfolgt. Bei einem großen Schema kann der Plan beim Einfügen von Daten parallel generiert werden, da das Einfügen mit Tabellenvariablen nur in einem Stream möglich ist.

Nach dem Ausführen des obigen Skripts wird ein Fenster mit der Indextabelle angezeigt:



Hier können Sie auch andere detaillierte Informationen anzeigen, z.

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

usw.
Die Spalten selbst können angepasst werden:



In den Zellen der Spalte Fix können Sie auswählen, welche Aktion während der Optimierung ausgeführt werden soll. Wenn der Scanvorgang abgeschlossen ist, wird die Standardaktion basierend auf den ausgewählten Einstellungen ausgewählt:



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

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



Speichern Sie die Tabelle in verschiedenen Formaten (mit derselben Schaltfläche können Sie detaillierte Einstellungen für die Analyse und Optimierung von Indizes öffnen):



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

Über eine Schaltfläche mit einer Lupe können Sie die gewünschte Datenbank zur Berücksichtigung auswählen.

Derzeit gibt es kein vollständiges Hilfesystem. Drücken Sie daher das "?" Es wird einfach ein modales Fenster angezeigt, das grundlegende Informationen zum Softwareprodukt enthält:



Zusätzlich zu all dem hat das Hauptmenü eine Suchleiste:



Beim Starten des Indexoptimierungsprozesses:



Außerdem sehen Sie unten im Fenster das Protokoll der durchgeführten Aktionen:



Im Fenster zur detaillierten Analyse und Optimierung von Indizes können Sie subtilere Optionen konfigurieren:



Vorschläge für die Anwendung:

  1. ermöglichen es, Statistiken nicht nur für Indizes, sondern auch auf unterschiedliche Weise (vollständig oder teilweise zu aktualisieren) selektiv zu aktualisieren
  2. Ermöglichen Sie nicht nur die Auswahl der 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 wird vorgeschlagen, Befehle in Bibliotheken zu verpacken und in PowerShell-Befehle auszugeben, wie dies beispielsweise hier getan wird: dbatools.io/commands
  4. 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
  5. Aus den Abschnitten 2 und 4 folgt der Wunsch, Gruppen in Datenbanken und Gruppen in Instanzen von MS SQL Server zu erstellen, für die die Einstellungen identisch sind
  6. Suche nach doppelten Indizes (vollständig und unvollständig, die sich entweder geringfügig oder nur in den enthaltenen Spalten unterscheiden)
  7. Da SQLIndexManager nur für MS SQL Server DBMS verwendet wird, müssen Sie dies im Namen wie folgt widerspiegeln: SQLIndexManager für MS SQL Server
  8. Entfernen Sie alle Teile der Anwendung von der GUI in separate Module und schreiben Sie sie in .NET Core 2.1

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



Quellen


Source: https://habr.com/ru/post/de461277/

More articles:


All Articles