Seit vielen Jahren als SQL Server-DBA tätig, Serveradministration und anschließend Leistungsoptimierung. Im Allgemeinen wollte ich in meiner Freizeit etwas Nützliches für das Universum und unsere Kollegen tun. Am Ende haben wir also ein kleines
Open Source Index-Wartungstool für SQL Server und Azure.
Idee
Wenn Sie an ihren Prioritäten arbeiten, ähneln die Leute manchmal einer Fingerbatterie - es gibt genug Motivationsladung für nur einen Blitz, und dann ist es soweit. Und bis vor kurzem war ich keine Ausnahme von dieser Beobachtung des Lebens. Oft besuchten mich Ideen, um etwas Eigenes zu kreieren, aber die Prioritäten änderten sich und nichts wurde zum Ende gebracht.
Einen ziemlich starken Einfluss auf meine Motivation und berufliche Entwicklung hatte die Arbeit in der Kharkov-Firma Devart, die sich mit der Erstellung von Software für die Entwicklung und Verwaltung von SQL Server-, MySQL- und Oracle-Datenbanken befasste.
Bevor ich zu ihnen kam, hatte ich wenig Ahnung von den Besonderheiten der Erstellung meines eigenen Produkts, aber bereits im Prozess habe ich viel Wissen über die interne Struktur von SQL Server gewonnen. Nachdem ich mehr als ein Jahr lang Metadatenabfragen in ihren Produktlinien optimiert hatte, begann ich allmählich zu verstehen, welche Funktionen auf dem Markt mehr gefragt sind als alle anderen.
Zu einem bestimmten Zeitpunkt entstand die Idee, ein neues Nischenprodukt zu schaffen, aber aufgrund der Umstände setzte sich diese Idee nicht durch. Zu diesem Zeitpunkt gab es für das neue Projekt nicht genügend freie Ressourcen innerhalb des Unternehmens, unbeschadet des Kerngeschäfts.
Bereits als er an einem neuen Ort arbeitete und versuchte, das Projekt alleine zu machen, musste er ständig Kompromisse eingehen. Die ursprüngliche Idee, ein großes Produkt mit zahlreichen Funktionen zu versehen, wurde schnell zunichte gemacht und schrittweise in eine andere Richtung umgewandelt - die geplante Funktionalität in separate Mini-Tools aufzuteilen und diese unabhängig voneinander zu implementieren.
Als Ergebnis wurde
SQL Index Manager geboren - ein kostenloses Tool zur Indexpflege für SQL Server und Azure. Die Hauptidee war, kommerzielle Alternativen von RedGate und Devart als Grundlage zu nehmen und zu versuchen, ihre Funktionalität zu verbessern. Sowohl Anfängern als auch erfahrenen Benutzern die Möglichkeit zu bieten, Indizes bequem zu analysieren und zu pflegen.
Implementierung
Mit anderen Worten, alles klingt immer einfach ... Ich nahm einen und sah mir ein paar motivierende Vidosiks an, stand im Regal und fing an, ein cooles Produkt herzustellen. In der Praxis ist jedoch nicht alles so rosig, da es bei der Arbeit mit der Systemtabellenfunktion sys.dm_db_index_physical_stats viele Fallstricke gibt und in Kombination der einzige Ort, an dem Sie relevante Informationen zur Indexfragmentierung erhalten können.
Von den ersten Tagen der Entwicklung an gab es eine großartige Gelegenheit, einen trostlosen Weg zwischen den Standardschemata zu finden und die bereits debuggte Logik der Arbeit konkurrierender Anwendungen zu kopieren, während ein kleiner Gag hinzugefügt wurde. Nachdem ich die Anfragen nach Metadaten analysiert hatte, wollte ich etwas Optimierteres tun, das aufgrund der Bürokratie großer Unternehmen niemals in ihren Produkten aufgetaucht wäre.
Bei der Analyse des RedGate SQL Index Managers (1.1.9.1378 - $ 155) können Sie feststellen, dass die Anwendung einen sehr einfachen Ansatz verwendet: Mit einer Abfrage erhalten wir eine Liste der Benutzertabellen und -ansichten, und nach der zweiten Abfrage wird eine Liste aller Indizes in der ausgewählten Datenbank zurückgegeben.
SELECT objects.name AS tableOrViewName , objects.object_id AS tableOrViewId , schemas.name AS schemaName , CAST(ISNULL(lobs.NumLobs, 0) AS BIT) AS ContainsLobs , o.is_memory_optimized FROM sys.objects AS objects JOIN sys.schemas AS schemas ON schemas.schema_id = objects.schema_id LEFT JOIN ( SELECT object_id , COUNT(*) AS NumLobs FROM sys.columns WITH (NOLOCK) WHERE system_type_id IN (34, 35, 99) OR max_length = -1 GROUP BY object_id ) AS lobs ON objects.object_id = lobs.object_id LEFT JOIN sys.tables AS o ON o.object_id = objects.object_id WHERE objects.type = 'U' OR objects.type = 'V' SELECT i.object_id AS tableOrViewId , i.name AS indexName , i.index_id AS indexId , i.allow_page_locks AS allowPageLocks , p.partition_number AS partitionNumber , CAST((c.numPartitions - 1) AS BIT) AS belongsToPartitionedIndex FROM sys.indexes AS i JOIN sys.partitions AS p ON p.index_id = i.index_id AND p.object_id = i.object_id JOIN ( SELECT COUNT(*) AS numPartitions , object_id , index_id FROM sys.partitions GROUP BY object_id , index_id ) AS c ON c.index_id = i.index_id AND c.object_id = i.object_id WHERE i.index_id > 0 -- ignore heaps AND i.is_disabled = 0 AND i.is_hypothetical = 0
Dann wird in einem Zyklus für jeden Abschnitt des Index eine Anforderung gesendet, um seine Größe und seinen Fragmentierungsgrad zu bestimmen. Am Ende des Scans werden Indizes, die weniger als den Eintragsschwellenwert wiegen, auf dem Client verworfen.
EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 1, @partitionNr = 1 EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 2, @partitionNr = 1 EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 3, @partitionNr = 1
Bei der Analyse der Logik dieser Anwendung können Sie viele Mängel feststellen. Wenn Sie beispielsweise Fehler bei Kleinigkeiten feststellen, wird vor dem Senden einer Anforderung nicht überprüft, ob der aktuelle Abschnitt Zeichenfolgen enthält, um leere Abschnitte vom Scannen auszuschließen.
Das Problem ist jedoch in einem anderen Aspekt am akutesten: Die Anzahl der Serveranforderungen entspricht ungefähr der Gesamtzahl der Zeilen von sys.partitions. Angesichts der Tatsache, dass echte Datenbanken Zehntausende von Abschnitten enthalten können, kann diese Nuance zu einer großen Anzahl ähnlicher Anforderungen an den Server führen. In einer Situation, in der die Datenbank entfernt ist, wird die Scan-Zeit aufgrund erhöhter Netzwerkverzögerungen für jede, selbst die einfachste Anforderung, noch länger.
Im Gegensatz zu RedGate empfängt ein ähnliches Produkt, das in Devart entwickelt wurde - dbForge Index Manager für SQL Server (1.10.38 - 99 US-Dollar) - Informationen in einer großen Abfrage und zeigt dann alles auf dem Client an:
SELECT SCHEMA_NAME(o.[schema_id]) AS [schema_name] , o.name AS parent_name , o.[type] AS parent_type , i.name , i.type_desc , s.avg_fragmentation_in_percent , s.page_count , p.partition_number , p.[rows] , ISNULL(lob.is_lob_legacy, 0) AS is_lob_legacy , ISNULL(lob.is_lob, 0) AS is_lob , CASE WHEN ds.[type] = 'PS' THEN 1 ELSE 0 END AS is_partitioned FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s JOIN sys.partitions p ON s.[object_id] = p.[object_id] AND s.index_id = p.index_id AND s.partition_number = p.partition_number JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id LEFT JOIN ( SELECT c.[object_id] , index_id = ISNULL(i.index_id, 1) , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END) , is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END) FROM sys.columns c LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id > 0 WHERE c.system_type_id IN (34, 35, 99) OR c.max_length = -1 GROUP BY c.[object_id], i.index_id ) lob ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id JOIN sys.objects o ON o.[object_id] = i.[object_id] JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id WHERE i.[type] IN (1, 2) AND i.is_disabled = 0 AND i.is_hypothetical = 0 AND s.index_level = 0 AND s.alloc_unit_type_desc = 'IN_ROW_DATA' AND o.[type] IN ('U', 'V')
Wir haben es geschafft, das Hauptproblem mit dem Schleier der gleichen Art von Abfragen in einem Konkurrenzprodukt zu beseitigen, aber die Nachteile dieser Implementierung sind, dass keine zusätzlichen Parameter an die Funktion sys.dm_db_index_physical_stats übergeben werden, die das Scannen offensichtlich unnötiger Indizes einschränken können. Tatsächlich führt dies dazu, dass Informationen zu allen Indizes im System und zusätzliche Festplattenlasten in der Scanphase abgerufen werden.
Es ist wichtig zu beachten, dass die aus sys.dm_db_index_physical_stats erhaltenen Daten nicht dauerhaft im Pufferpool zwischengespeichert werden. Daher war die Minimierung der physischen Messwerte beim Abrufen von Informationen zur Indexfragmentierung eine der vorrangigen Aufgaben während der Entwicklung.
Nach mehreren Experimenten stellte sich heraus, dass beide Ansätze kombiniert wurden, wobei der Scan in zwei Teile geteilt wurde. Zunächst bestimmt eine große Abfrage die Größe der Abschnitte, wobei diejenigen vorgefiltert werden, die nicht im Filterbereich liegen:
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
Als nächstes erhalten wir nur die Abschnitte, die Daten enthalten, um unnötige Lesevorgänge aus leeren Indizes zu vermeiden.
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)
Abhängig von den Einstellungen werden nur die Indextypen abgerufen, die der Benutzer analysieren möchte (die Arbeit mit Heaps, Cluster- / Nicht-Cluster-Indizes und Spaltenindikatoren wird unterstützt).
INSERT INTO #Indexes SELECT ObjectID = i.[object_id] , IndexID = i.index_id , IndexName = i.[name] , PagesCount = a.ReservedPages , UnusedPagesCount = a.ReservedPages - a.UsedPages , 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
Danach beginnt ein wenig Magie: Für alle kleinen Indizes bestimmen wir den Fragmentierungsgrad, indem wir wiederholt die Funktion sys.dm_db_index_physical_stats mit einer vollständigen Angabe aller Parameter aufrufen.
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)
Als nächstes geben wir alle möglichen Informationen an den Client zurück und filtern die überschüssigen Daten heraus:
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) )
Danach bestimmen Punktabfragen den Fragmentierungsgrad für große Indizes.
EXEC sp_executesql N' DECLARE @DBID INT = DB_ID() SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'') WHERE [index_level] = 0 AND [alloc_unit_type_desc] = ''IN_ROW_DATA''' , N'@ObjectID int,@IndexID int,@PartitionNumber int' , @ObjectId = 1044198770, @IndexId = 1, @PartitionNumber = 1 EXEC sp_executesql N' DECLARE @DBID INT = DB_ID() SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'') WHERE [index_level] = 0 AND [alloc_unit_type_desc] = ''IN_ROW_DATA''' , N'@ObjectID int,@IndexID int,@PartitionNumber int' , @ObjectId = 1552724584, @IndexId = 0, @PartitionNumber = 1
Aufgrund dieses Ansatzes konnten beim Generieren von Abfragen die Probleme mit der Scanleistung gelöst werden, die in den Anwendungen der Wettbewerber auftraten. Dies konnte abgeschlossen werden, aber im Verlauf der Entwicklung tauchten nach und nach neue Ideen auf, die es ermöglichten, den Anwendungsbereich Ihres Produkts zu erweitern.
Zunächst wurde die Unterstützung für die Arbeit mit WAIT_AT_LOW_PRIORITY implementiert, dann wurde es möglich, DATA_COMPRESSION und FILL_FACTOR zum Neuerstellen von Indizes zu verwenden.
Die Anwendung war mit zuvor ungeplanten Funktionen wie der Wartung von Spaltenspeichern leicht überwachsen:
SELECT * FROM ( SELECT IndexID = [index_id] , PartitionNumber = [partition_number] , PagesCount = SUM([size_in_bytes]) / 8192 , UnusedPagesCount = ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0) / 8192 , Fragmentation = CAST(ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0) * 100. / SUM([size_in_bytes]) AS FLOAT) FROM sys.fn_column_store_row_groups(@ObjectID) GROUP BY [index_id] , [partition_number] ) t WHERE Fragmentation >= @Fragmentation AND PagesCount BETWEEN @MinIndexSize AND @MaxIndexSize
Oder die Möglichkeit, nicht gruppierte Indizes basierend auf Informationen aus dm_db_missing_index zu erstellen:
SELECT ObjectID = d.[object_id] , UserImpact = gs.[avg_user_impact] , TotalReads = gs.[user_seeks] + gs.[user_scans] , TotalSeeks = gs.[user_seeks] , TotalScans = gs.[user_scans] , LastUsage = ISNULL(gs.[last_user_scan], gs.[last_user_seek]) , IndexColumns = CASE WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NOT NULL THEN d.[equality_columns] + ', ' + d.[inequality_columns] WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NULL THEN d.[equality_columns] ELSE d.[inequality_columns] END , IncludedColumns = d.[included_columns] FROM sys.dm_db_missing_index_groups g WITH(NOLOCK) JOIN sys.dm_db_missing_index_group_stats gs WITH(NOLOCK) ON gs.[group_handle] = g.[index_group_handle] JOIN sys.dm_db_missing_index_details d WITH(NOLOCK) ON g.[index_handle] = d.[index_handle] WHERE d.[database_id] = DB_ID()
Zusammenfassung
Nach sechs Monaten aktiver Entwicklungsphase bin ich froh, dass die Pläne dort nicht enden, da ich dieses Produkt weiterentwickeln möchte. Der nächste Schritt besteht darin, Funktionen für die Suche nach doppelten oder nicht verwendeten Indizes hinzuzufügen und die vollständige Unterstützung für die Bereitstellung von Statistiken in SQL Server zu implementieren.
Aufgrund der Tatsache, dass es viele kostenpflichtige Lösungen auf dem Markt gibt, möchte ich glauben, dass dieses Produkt aufgrund der freien Positionierung, einer optimierten Metadatenbeschreibung und des Vorhandenseins verschiedener nützlicher Kleinigkeiten für jemanden definitiv für alltägliche Aufgaben nützlich sein wird.
Die aktuelle Version der Anwendung kann auf
GitHub heruntergeladen werden. Quellen befinden sich am selben Ort.