DMV series: sys.dm_db_partition_stats

Article
04/02/2010

DMV: sys.dm_db_partition_stats

This DMV returns information for the current database context. It can tell you how many rows are in each table (per partition) and how much of the space is used and reserved for in-row, overflow, and LOB data (with or without the metadata overhead).

Remember that the stored count information was not always 100% accurate in SQL Server 2000. For a new table created on 2005 the counts will be accurate. But for a table that existed in 2000 and now resides on 2005 through a restore or update, you need to run (only once after the move to 2005) either sp_spaceused @updateusage = N'true' or DBCC UPDATEUSAGE with the COUNT_ROWS option.

Regarding performance with LOB (text, image, XML, max) or overflow (variable length) data: You can end up with "extra" IOs and more page allocations/deallocations, which can potentially make performance worse. They are also processed synchronously which can slow down joins and sorts. If a column is indexed its data cannot be pushed offrow.

· Row-Overflow Data Exceeding 8 KB https://msdn.microsoft.com/en-us/library/ms186981.aspx
Surpassing the 8,060-byte row-size limit might affect performance because SQL Server still maintains a limit of 8 KB per page. When a combination of varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns exceeds this limit, the SQL Server Database Engine moves the record column with the largest width to another page in the ROW_OVERFLOW_DATA allocation unit, while maintaining a 24-byte pointer on the original page. Moving large records to another page occurs dynamically as records are lengthened based on update operations.

SQL 2000 did support storing LOBs (text, image) offrow, but it had no concept of overflow rows for variable length data types. If the actual length of a row with variable length data types would exceed 8060 bytes the insert or update would fail. In 2005+ the data over 8060 bytes is moved offrow, leaving behind a 24 byte pointer.

· Row Size limitation in SQL 2000 and 2005 https://blogs.msdn.com/msdnts/archive/2006/12/01/row-size-limitation-in-sql-2000-and-2005.aspx

· Stretching the 8K Row Limithttps://www.sqlmag.com/Articles/ArticleID/50490/50490.html?Ad=1

Sample Queries

-- DMV: sys.dm_db_partition_stats
-- Returns information for the current database
-- Key data: row count per partition, pages of in-row, overflow, and LOB data, used vs reserved pages
-- Common join objects: sys.allocation_units, sys.partitions, sys.indexes
-- A table with no explicit partitions has partition_number=1
-- Permission needed: VIEW DATABASE STATE

USE AdventureWorks

/*
-- For any databases upgraded or restored from SQL 2000, you have to update the count info once
EXEC sp_spaceused @updateusage = N'true'
DBCC UPDATEUSAGE (0) WITH COUNT_ROWS -- 0 = current db
*/

-- Run the CLEANUP section when you have finished exploring the DMV

/*
-- show that you have to account for the schema too (since table has same name as Person.Address)
-- show how to force a row into LOB_DATA
-- start with inserting just one row and run all the select queries
-- then insert the LOB row and run the selects
-- then run the overflow insert and run the selects
-- DROP TABLE dbo.Address
CREATE TABLE dbo.Address (col1 varchar(max), col2 varchar(max), col3 nvarchar(4000), col4 nvarchar(4000), col5 nvarchar(4000));
INSERT dbo.Address VALUES ('a', 'b', 'c', 'd', 'e'); -- no overflow
INSERT dbo.Address VALUES (REPLICATE ('a', 8000),REPLICATE ('b', 8000), NULL, NULL, NULL); -- LOB_DATA
INSERT dbo.Address VALUES (NULL, NULL, REPLICATE ('a', 4000),REPLICATE ('b', 4000),REPLICATE ('c', 4000)); -- overflow
--SELECT DATALENGTH(col1), DATALENGTH(col2), DATALENGTH(col3), DATALENGTH(col4), DATALENGTH(col5),* FROM dbo.Address

-- add a table with partitions
DROP TABLE a1
DROP PARTITION SCHEME myRangePS1
DROP PARTITION FUNCTION myRangePF1

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO ('primary', 'primary', 'primary', 'primary');
GO
CREATE TABLE a1
(col1 int) ON myRangePS1 (col1)
GO
INSERT a1 VALUES (1)
INSERT a1 VALUES (10)
INSERT a1 VALUES (101)
INSERT a1 VALUES (1001)
*/

-- Simple query with no joins or attempt to get the true count per table
-- Note that it returns multiple rows per table when there are multiple indexes, these row counts should NOT be added together
-- It also returns multiple rows per table when the table is partitioned, these DO need to be added together
SELECT ps.row_count, OBJECT_SCHEMA_NAME(ps.object_id) AS SchemaName, OBJECT_NAME(ps.object_id) AS ObjectName, ps.index_id, ps.partition_number
, *
FROM sys.dm_db_partition_stats ps
ORDER BY OBJECT_NAME(ps.object_id), OBJECT_SCHEMA_NAME(ps.object_id)

-- Still doesn't take into account how to SUM the rows
-- Shows the most interesting columns and calculates the total size
-- You could use the "used" or "data page" columns instead of the "reserved" columns, explore this on your own
SELECT ps.row_count, OBJECT_SCHEMA_NAME(ps.object_id) AS SchemaName, OBJECT_NAME(ps.object_id) AS ObjectName, ps.index_id, ps.partition_number
  , au.type_desc, au.total_pages AS TotalPages, au.total_pages * 8 /*8k pages*/ /1024 /*1024k per mb*/ AS TotalMB
FROM sys.dm_db_partition_stats ps
  JOIN sys.allocation_units au ON ps.partition_id = au.container_id
ORDER BY OBJECT_NAME(ps.object_id), OBJECT_SCHEMA_NAME(ps.object_id)
-- do not order by size or pages as we haven't done a group by to add partitions together

-- Query from https://sqlblog.com/blogs/louis_davidson/archive/2007/07/11/sys-dm-db-partition-stats.aspx
-- "Get rowcount of tables. Note that I grouped on the object_id, because for a partitioned table,
-- you need to add all of the rows in all partitions."
select object_name(dm_db_partition_stats.object_id),
           sum(dm_db_partition_stats.row_count) as row_count
from sys.dm_db_partition_stats
              join sys.indexes
                     on indexes.object_id = dm_db_partition_stats.object_id
                          and indexes.index_id = dm_db_partition_stats.index_id
where indexes.type_desc in ('CLUSTERED','HEAP')
group by dm_db_partition_stats.object_id

-- total rowcount as modified by cgross
SELECT OBJECT_SCHEMA_NAME(ps.object_id) AS SchemaName, OBJECT_NAME(ps.object_id) AS ObjectName,
           SUM(ps.row_count) AS row_count
FROM sys.dm_db_partition_stats ps
JOIN sys.indexes i
  ON i.object_id = ps.object_id
        AND i.index_id = ps.index_id
WHERE i.type_desc IN ('CLUSTERED','HEAP')
AND OBJECT_SCHEMA_NAME(ps.object_id) <> 'sys'
GROUP BY ps.object_id
ORDER BY OBJECT_NAME(ps.object_id), OBJECT_SCHEMA_NAME(ps.object_id)

-- rowcount plus inrow, LOB, overflow info
-- reserved info includes allocated data, index data, metadata
SELECT OBJECT_SCHEMA_NAME(ps.object_id) AS SchemaName, OBJECT_NAME(ps.object_id) AS ObjectName,
           SUM(ps.row_count) AS row_count,
           SUM(ps.in_row_reserved_page_count) AS InRowReservedPgs,
           SUM(ps.lob_reserved_page_count) AS LOBReservedPgs,
           SUM(ps.row_overflow_reserved_page_count) AS OverflowReservedPgs,
           SUM(ps.reserved_page_count) AS TotalReservedPgs
FROM sys.dm_db_partition_stats ps
JOIN sys.indexes i
  ON i.object_id = ps.object_id
        AND i.index_id = ps.index_id
WHERE i.type_desc IN ('CLUSTERED','HEAP')
AND OBJECT_SCHEMA_NAME(ps.object_id) <> 'sys'
GROUP BY ps.object_id
ORDER BY OBJECT_NAME(ps.object_id), OBJECT_SCHEMA_NAME(ps.object_id)

/*
-- Per BOL "The container ID has a one-to-one mapping to the partition_id in the sys.partitions catalog view that maintains the relationship between the table, index, or the indexed view data stored in a partition and the allocation units used to manage the data within the partition. "
-- more info on sys.allocation_units in https://www.sqlmag.com/articles/index.cfm?articleid=50257&pg=2
SELECT * FROM sys.allocation_units
-- WHERE allocation_unit_id = 72057594042974208 OR container_id = 72057594042974208 -- partition_id of Address table
WHERE container_id IN (72057594043957248,72057594038321152) -- two partition_ids for databaselog

-- Per BOL "The sys.system_internals_allocation_units system view is reserved for Microsoft SQL Server internal use only. Future compatibility not guaranteed."
SELECT * FROM sys.system_internals_allocation_units
WHERE allocation_unit_id = 72057594042974208 OR container_id = 72057594042974208 -- partition_id of Address table
*/

/*
References and explanations:
sys.dm_db_partition_stats (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms187737.aspx
Diving In to Object-Storage Metadata https://www.sqlmag.com/articles/index.cfm?articleid=50257&pg=2

-- UpdateUsage
Remember that the stored count information was not always 100% accurate in SQL Server 2000.
For a new table created on 2005 the counts will be accurate.
But for a table that existed in 2000 and now resides on 2005 through a restore or update,
you need to run (only once after the move to 2005) either sp_spaceused @updateusage = N'true' or
DBCC UPDATEUSAGE with the COUNT_ROWS option.

-- Performance
Performance can potentially be worse with LOB (text, image, XML, max) or overflow (variable length) data.
You can end up with "extra" IOs and more page allocations/deallocations, which can potentially make
performance worse. They are also processed synchronously which can slow down joins and sorts.
If a column is indexed its data cannot be pushed offrow.
• Row-Overflow Data Exceeding 8 KB https://msdn.microsoft.com/en-us/library/ms186981.aspx
Surpassing the 8,060-byte row-size limit might affect performance because SQL Server still maintains a limit of 8 KB per page. When a combination of varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns exceeds this limit, the SQL Server Database Engine moves the record column with the largest width to another page in the ROW_OVERFLOW_DATA allocation unit, while maintaining a 24-byte pointer on the original page. Moving large records to another page occurs dynamically as records are lengthened based on update operations.

-- SQL 2000
SQL 2000 did support storing LOBs (text, image) offrow, but it had no concept of overflow rows for variable length data types. If the actual length of a row with variable length data types would exceed 8060 bytes the insert or update would fail. In 2005+ the data over 8060 bytes is moved offrow, leaving behind a 24 byte pointer.
• Row Size limitation in SQL 2000 and 2005 https://blogs.msdn.com/msdnts/archive/2006/12/01/row-size-limitation-in-sql-2000-and-2005.aspx
• Stretching the 8K Row Limit https://www.sqlmag.com/Articles/ArticleID/50490/50490.html?Ad=1
*/

/*
--CLEANUP
DROP TABLE a1
DROP PARTITION SCHEME myRangePS1
DROP PARTITION FUNCTION myRangePF1
DROP TABLE dbo.Address
*/

Partager via

DMV series: sys.dm_db_partition_stats

Ressources supplémentaires