Mit Hilfe des undokumentierten Befehls DBCC IND in Verbindung mit dem – ebenfalls undokumentierten – Befehl DBCC PAGE kann man bisher die Aufteilung der Daten in den einzelnen Datenseiten (Pages) analysieren. DBCC IND hat jedoch das Problem, dass die Ausgabe nicht sortiert oder gefiltert werden kann. Aus diesem Grund wurde als Workaround eine Tabelle angelegt, in die das Ergebnis von DBCC IND mittels sp_executesql umgeleitet worden ist. Seit der Version von Microsoft SQL Server 2012 gibt es endlich eine – undokumentierte – DMF, 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;
GO

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

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

Der Nachteil dieser Variante besteht vor allen Dingen darin, dass zunächst eine Tabelle vorhanden sein muss, die absolut identisch mit der Ausgabe von DBCC IND ist. Nur dann kann mit Hilfe von sp_executesql der Output in diese „Zwischentabelle“ eingefügt werden.

sys.dm_db_database_page_allocations

Mit Microsoft SQL Server 2012 kann der oben beschriebene Prozess deutlich vereinfacht werden, indem die neue „Dynamic Management Function“ 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(N'HumanResources.Employee', N'U'),
            1,
            NULL,
            N'DETAILED'
         ) 
ORDER BY 
        page_type DESC, 
        page_level DESC, 
        previous_page_page_id ASC;

Die DMF erwartet fünf – optionale – Parameter:

Parameter Beschreibung
DatabaseId Eindeutige Id der Datenbank, dessen Datenseiten analysiert werden sollen. Mit der Funktion 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 sys.indexes ermittelt werden.
PartitionId Eindeutige Id der Partition, die durch den Index belegt wird. Sind keine dedizierten Partitionen für die Datenbank vorhanden, wird die ID 1 für die primäre Partition angegeben.
Mode LIMITED oder DETAILED. Bei Verwendung von „Limited“ werden Informationen zu den Datentypen sowie deren Verbindung zueinander nicht mit analysiert. (previous Page / next Page)

Verweise

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

Herzlichen Dank fürs Lesen!

Shares
Share This