THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

sys.dm_db_partition_stats

For the current database, gives you space oriented statistics for each partition of indexes (even if you only have one partion), like row count, page counts, etc. Resembles the sysindexes in previous versions of SQL Server, with more information.

Type: view

Data: accumulating, refreshed at server restart

Scope: Reset on server restart (or object/partition drop and recreate)

Columns:

  • partition_id - numeric identifier of the partition (relates to sys.partitons)
  • object_id - the object_id of the object that the partition belongs to
  • index_id - identifies the index on the table. 0 = Heap
  • partition_number - 1 for non-partitioned tables. For partitioned tables will be the 1 – based number of the partition.
  • in_row_data_page_count - the number of pages being used for data of the object. Can be the leaf pages of an index or the data pages of a clustered table or heap
  • in_row_used_page_count - Includes all pages in use for the object, including non-leaf index and index allocation map pages.
  • in_row_reserved_page_count - Includes all pages in use plus any pages reserved for the object, even if the pages are not currently in use.
  • lob_used_page_count - Count of pages in use for the object to store out-of-row values such as varchar(max), varbinary(max), etc.
  • lob_reserved_page_count – Count of out of the row pages including any that are reserved but not in use.
  • row_overflow_used_page_count - Count of pages that are in use for storing overflow data for rows that are larger than will fit on a single ~8K page
  • row_overflow_reserved_page_count - Count of overlow pages that includes any pages that are reserved but not in use
  • used_page_count - Total number of pages in use in the partiton for any reason
  • reserved_page_count - Total number of pages in use or reserved in the partiton for any reason
  • row_count – The number of rows in the table

Examples:

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

Other planned examples include a sample partition to show the rowcounts/usage, and summations of some of the other values to give a meaningful data other than rowcount.

 

Published Wednesday, July 11, 2007 9:41 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi Louis

I used  sys.allocation_units  to JOIN on container_id and it does not have system tables :-)))

SELECT

t.name,

SUM

(

CASE

WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows

ELSE 0

END

) as [rowcount]

FROM

sys.tables t

INNER JOIN sys.partitions p

ON t.object_id = p.object_id

INNER JOIN sys.allocation_units a

ON p.partition_id = a.container_id

GROUP BY

t.name;

July 12, 2007 12:09 AM
 

drsql said:

Hmm, I wonder which is better?  The catalog view or the dynamic management view.  I knew about the rows in sys.partions, but just went with this since it was pretty easy (I need to do more work on examples :)

Thanks

July 27, 2007 9:33 PM
 

AaronBertrand said:

Uri, can you explain what you mean by, "it does not have system tables"?  Also why do you feel allocation_units is better?  Both can potentially have inaccurate counts.  The allocation_units view is better if you do not use bulk operations or online rebuilds (in those cases the data can obviously be inaccurate).  Personally I prefer the breakdown that the DMV gives, even though it is more susceptible to small inaccuracies due to in-flight transactions.  And since I have several high-volume tables with lots of BULK INSERT operations, it lets me approach table growth monitoring consistently across the board.

February 26, 2010 3:03 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement