Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

MSSQLFUN

I, Rohit Garg, am working as Consultant in IT Company. I am having an around 5 years of experience in MSSQL server & other Microsoft technologies. I am working as DBA in Microsoft SQL Server from last 5 years in e-Commerce, Telecom, Finance domain. In this tenure, I got a chance of working as Database administrator, Developer and trainer on SQL server 2000 to SQL Server 2012. I am holding Master’s degree in Computer Science along with certification in SQL Server & .Net. I like to learn new things by hand-on experience on regular basis. This journey is so far is delightful & valuable with the addition of wonderful friends.

DMV-14 : Which Table has Max. rows & Size……..sys.dm_db_partition_stats

sys.dm_db_partition_stats DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms187737.aspx

Returns page and row-count information for every partition in the current database.

This DMV gives useful information about rowcount, free & reserved space of each table in database. It can be used for analysis purpose while adding new indexes, doing index Maintenance or checking the biggest table of database.

Query 1 : Details of table with row count & space used

SELECT

SS.NAME AS SCHEMANAME,

SO.NAME AS OBJECTNAME,

SI.NAME AS INDEXNAME,

PS.RESERVED_PAGE_COUNT*8 TOTAL_SPACE_CONSUMED_KB,

PS.USED_PAGE_COUNT*8 USED_SPACE_KB,

(PS.RESERVED_PAGE_COUNT – PS.USED_PAGE_COUNT)*8 FREE_SPACE_KB,

CASE

WHEN PS.INDEX_ID IN (0,1) THEN PS.ROW_COUNT

ELSE NULL

END AS ROW_COUNT

FROM

SYS.DM_DB_PARTITION_STATS PS

INNER JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = PS.OBJECT_ID

INNER JOIN SYS.SCHEMAS SS ON SS.SCHEMA_ID = SO.SCHEMA_ID

LEFT JOIN SYS.INDEXES SI ON SI.OBJECT_ID = PS.OBJECT_ID

AND SI.INDEX_ID = PS.INDEX_ID

WHERE

SO.IS_MS_SHIPPED = 0

Sample Result

Remarks

1. To use this DMV, User required VIEW DATABASE STATE permission on the server.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)


Comments

Leave a comment on the original post [mssqlfun.com, opens in a new window]

Loading comments...