Seit Microsoft SQL Server 2012 gibt es eine neue Möglichkeit, die allokierten Datenseiten eines Objekts mit Hilfe der Systemfunktion [sys].[dm_db_database_page_allocations] zu ermitteln. Über die Verwendung habe ich bereits im Artikel “Neue DMF für Aufteilung von Datenseiten” geschrieben. Diese Systemfunktion ist keine offiziell dokumentierte Funktion. Ich benutze diese Funktion sehr gern, da sie – anders als DBCC IND die Daten als Tabelle zurück liefert, dessen Ergebnis durch Prädikate eingegrenzt werden können. Eher durch Zufall ist aufgefallen, dass diese Funktion nicht immer zuverlässig arbeitet.

Inhaltsverzeichnis

Verwendung von sys.dm_db_database_page_allocations

sys.dm_db_database_page_allocation wird verwendet, um sich einen Überblick über die durch ein Objekt belegten Datenseiten zu verschaffen. Hierbei handelt es sich um eine Funktion; die grundsätzliche Filterung findet bereits durch die übergebenen Parameter statt. Der grundsätzliche Aufruf der Funktion sieht wie folgt aus:

SELECT *
FROM sys.dm_db_database_page_allocations
     (
        @DatabaseId   SMALLINT,
        @TableId      INT          = NULL,
        @IndexId      INT          = NULL,
        @PartitionId  BIGINT       = NULL,
        @Mode         NVARCHAR(64) = 'LIMITED'
     );

Das Ergebnis dieser Funktion ist eine Tabelle mit allen durch ein Objekt belegten Datenseiten; oder sollte es sein. Der “Fehler” ist mir in einer Demo-Datenbank aufgefallen, die ich als Grundlage eines zu produzierenden Video-Workshops verwende. Diese Datenbank – mit den Fehlern – kann hier heruntergeladen werden: http://1drv.ms/1ZNxEXH. Die betroffene Tabelle ist [dbo].[Customers]. Diese Tabelle – wie alle anderen auch – ist ein HEAP und besitzt 75.000 Datensätze.

Demonstration

Mit den nachfolgenden Skripten wird zunächst ermittelt, wie viele Datenseiten durch die Tabelle [dbo].[Customers] belegt sind. Die einfachste Art der Feststellung ist das gemessene IO für einen TABLE SCAN.

SET STATISTICS IO ON

USE CustomerOrders;
GO
 
SET STATISTICS IO ON;
GO
 
SELECT * FROM dbo.Customers AS C;
GO
 
SET STATISTICS IO OFF;
GO

Ein direkter Vergleich mit den allokierten Datenseiten bestätigt, dass diese Tabelle 682 Datenseiten belegt.

sys.system_internals_allocation_units

Die Systemsicht sys.system_internals_allocation_units ist nur für die interne Verwendung durch Microsoft SQL Server reserviert. Jede Partition einer Tabelle, eines Indexes oder einer indizierten Sicht hat eine Zeile in sys.system_internals_allocation_units, die eindeutig durch eine Container-ID (container_id) identifiziert ist. Die Container-ID besitzt eine 1:1-Zuordnung zur [partition_id] in der Katalogsicht [sys].[partitions], mit der die Beziehung zwischen den in einer Partition gespeicherten Daten der Tabelle, des Indexes oder der indizierten Sicht und den Zuordnungseinheiten bestimmt wird, die zum Verwalten der Daten in der Partition verwendet werden.

SELECT IAU.total_pages,
       IAU.used_pages,
       IAU.data_pages
FROM   sys.partitions AS P INNER JOIN sys.system_internals_allocation_units AS IAU
       ON (P.partition_id = IAU.container_id)
WHERE  P.object_id = OBJECT_ID(N'dbo.Customers', N'U');

Insgesamt belegt die Tabelle 689 Datenseiten; davon sind 688 REINE Datenseiten und eine IAM-Datenseite belegt. Dass Datenseiten belegt sind, obwohl sie nicht verwendet werden, liegt an dem Umstand, dass Microsoft SQL Server automatisch vollständige Extents belegt, wenn eine Tabelle mehr als 8 Datenseiten belegt! Von den 688 Datenseiten sind 682 Datenseiten mit Daten belegt. Somit stimmt die Ausgabe des SELECT-Befehls. Insgesamt müssen 682 Datenseiten ausgegeben werden.

sys.dm_db_database_page_allocations

Eine Abfrage auf die Systemfunktion sys.dm_db_database_page_allocations zeigt jedoch ein anderes Ergebnis:

SELECT DDDPA.allocation_unit_type_desc,
       DDDPA.allocated_page_page_id,
       DDDPA.page_free_space_percent 
FROM   sys.dm_db_database_page_allocations
       (
          DB_ID(),
          OBJECT_ID(N'dbo.Customers', N'U'),
          NULL,
          NULL,
          N'DETAILED'
       ) AS DDDPA 
WHERE  DDDPA.is_allocated = 1 AND 
       DDDPA.page_type = 1 
ORDER BY 
       DDDPA.page_type DESC, 
       DDDPA.allocated_page_page_id ASC; 
GO

Wie man an der obigen Abbildung deutlich erkennen kann, werden NICHT die erwarteten 682 Datenseiten angezeigt sondern es fehlt offensichtlich eine Datenseite. Wird jedoch die Zuordnung der Datenseiten mit DBCC IND überprüft, stimmt die Zuordnung wieder:

DBCC IND(CustomerOrders, 'dbo.Customers', 0);

Die Differenz von +1 hängt damit zusammen, dass DBCC IND nicht nur die reinen Datenseiten ausgibt sondern zusätzlich die IAM-Datenseiten im Resultat auswirft. Zieht man die IAM-Datenseite vom Ergebnis ab, so verbleiben 682 Datenseiten für die Ausgabe der Datensätze von [dbo].{Customers]. Bei genauerer Betrachtung war im Anschluss erkennbar, dass die allokierte Datenseite 40447 nicht von sys.dm_db_database_page_allocation berücksichtigt wurde.

Zusammenfassung

Die seit Microsoft SQL Server 2012 zur Verfügung gestellte Funktion ist eine große Hilfe für DBA, wenn es darum geht, gezielt eine Liste der allokierten Datenseiten mit Hilfe von Filtern und Sortierungen ausgeben zu lassen. Ist man auf die exakte Anzal der allokierten Datenseiten angewiesen, sollte besser weiterhin mit DBCC IND die Ausgabe gesteuert werden. Im konkreten Fall half der Neuaufbau der Tabelle mit Hilfe eines REBUILDs.

Herzlichen Dank fürs Lesen!

Don’t trust sys.dm_db_database_page_allocations