« Tücken bei der Verwendung von sp_executeSQL
Zertifikate für die Ausführung von Prozeduren verwenden »

Neue DMV für Aufteilung der Pages

Im Artikel "Optimierung von Datenbankmodellen - Richtige Wahl von Datentypen und Indexen (Teil 2)" habe ich gezeigt, wie man mit dem undokumentierten Befehl DBCC IND in Verbindung mit dem - ebenfalls undokumentierten - Befehl DBCC PAGE die Aufteilung der Daten in den einzelnen Datenseiten (Pages) analysieren kann.

Das Problem von DBCC IND ist jedoch, dass die Ausgabe nicht sortiert werden kann. Also wurde als Workaround eine Tabelle angelegt, in die das Ergebnis von DBCC IND mittels sp_executeSQL in die Tabelle umgeleitet.

Seit der Version von Microsoft SQL Server 2012 gibt es endlich eine - leider auch undokumentierte - DMV, mit deren Hilfe die Aufteilung der Daten ohne DBCC IND ausgegeben und unmittelbar weiterverarbeitet (filtern, sortieren) kann.

Bisherige Analyse der Datenseiten

Der folgende Code musste bisher ausgeführt werden, um Informationen über die Datenseiten zu analysieren.

TRUNCATE TABLE sp_table_pages;

INSERT INTO sp_table_pages
EXEC sp_executeSQL 'DBCC IND (DatenbankName, TabellenName, 1)';

SELECT  indexId,
        Pagetype,
        PagePId,
        NextPagePId,
        PrevPagePId
FROM    sp_table_pages
ORDER BY
        IndexLevel DESC,
        PrevPagePId ASC;

Neue Analyse mittels sys.dm_db_database_page_allocations

Seit SQL Server 2012 kann der oben beschriebene Prozess deutlich vereinfacht werden, indem die neue "Dynamic Management View" verwendet wird.

SELECT  index_id,
        page_type,
        page_type_desc,
        allocation_unit_type,
        allocated_page_page_id,
        next_page_page_id,
        previous_page_page_id
FROM    sys.dm_db_database_page_allocations(db_id(), OBJECT_ID('HumanResources.Employee', 'U'), 1, NULL, 'DETAILED')
ORDER BY
        page_type DESC,
        page_level DESC,
        previous_page_page_id ASC;

Die DMV erwartet fünf Parameter:

Parameter Beschreibung
DatabaseId Eindeutige Id der Datenbank, dessen Datenseiten analysiert werden sollen.
Mit db_Id() kann die ID der aktuellen Datenbank angegeben werden.
Welche Id eine Datenbank besitzt, kann mittels sys.databases ermittelt werden.
ObjectId Eindeutige Id der Relation, dessen Datenseiten analysiert werden sollen.
Die Id einer Relation kann mittels OBJECT_ID() ermittelt werden.
IndexId Eindeutige Id des Indexes, dessen Datenseiten analysiert werden sollen.
Ein "Clustered Index" hat IMMER die ID 1. Alle weiteren Id können über die Relation dbo.sysindexes ermittelt werden.
PartitionId Eindeutige Id der Partition, auf der sich der Index befindet. Sind keine dedizierten Partitionen für die Datenbank verfügbar, wird die ID 1 für die PRIMARY-Partition angegeben.
Mode LIMITED oder DETAILED
Bei Limited werden Informationen zu den Pagetypes sowie deren Verbindung zueinander nicht mit analysiert. (Vorherige Page / Nachfolgende Page)

Herzlichen Dank für's Lesen

DBCC IND http://www.sascha-dittmann.de/post/Undokumentierter-SQL-Befehl-DBCC-IND.aspx
DBCC PAGE http://www.sascha-dittmann.de/post/Undokumentierter-SQL-Befehl-DBCC-PAGE.aspx
db_id http://msdn.microsoft.com/de-de/library/ms186274.aspx
object_id http://msdn.microsoft.com/de-de/library/ms190328.aspx
sys.databases http://msdn.microsoft.com/de-de/library/ms178534.aspx
sys.indexes http://msdn.microsoft.com/de-de/library/ms173760.aspx
Administration, Tipps und Tricks, Optimierung / Performance
 Print article This entry was posted by Uwe Ricken on 25.11.12 at 10:42:00 . Follow any responses to this post through RSS 2.0.