Summary: If you want to see the overall data on space used for a database, use DBCC ShowFileStats or the Graphic Standard Reports in SQL Server 2005 and above. If you want to check the space used info for all objects in a database, use sys.dm_db_partition_stats. If you need to find out the detailed information about space used based on page/extent, use sys.dm_db_index_physical_stats (or DBCC SHOWCONTIG in SQL Server 2000).

1. sp_spaceused

1) What’s for?

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database. It is a page-based calculation.

2) Syntax

sp_spaceused [[ @objname = ] 'objname' ] [,[ @updateusage = ] 'updateusage' ]

3) Three Versions

i. Without any parameters, return space used by the current database

EXEC sp_spaceused

database_name database_size unallocated space

AdventureWorks2012 394.75 MB 98.01 MB

reserved data index_size unused

191480 KB 97496 KB 86328 KB 7656 KB

ii. For a specific table/indexed view/queue

-- For a specific table

EXEC sp_spaceused N'[HumanResources].[Employee]';

name rows reserved data index_size unused

Employee 290 200 KB 56 KB 136 KB 8 KB

-- Nothing for un-indexed view

EXEC sp_spaceused N'[HumanResources].[vEmployee]'

name rows reserved data index_size unused

vEmployee NULL NULL NULL 0 KB 0 KB

-- For a indexed view

EXEC sp_spaceused N'[Person].[vStateProvinceCountryRegion]'

name rows reserved data index_size unused

vStateProvinceCountryRegion 181 32 KB 16 KB 16 KB 0 KB

iii. Displaying updated space information about a database. See below for the reason.

EXEC sp_spaceused @updateusage = N'TRUE';

4) Four major limitations for this sp:

· The calculation is based on the statistics in sys.partitions and sys.allocatin_units. The statistics may not be updated, especially in the case of massive change of a database. To force the sp uses the updated statistics, you can use the optional parameter @updateusage to ensure current values are returned as below.

EXEC sp_spaceused @updateusage = N'TRUE';

· Log information is not returned from this sp

· It works for the user databases, but not for the object stored in the temp database.

· Query an object (the entire DB or a table/indexed view/SSB Queue) one time, you cannot use it to return results for multiple objects in one query. But sys.dm_db_partition_stats can as demonstrated in Section 3 below.

5) When to use it?

i. Generally not recommended.

ii. If the DB is under rapid changes, need to use it with @updateusage. But be aware of the impacts on performance.

2. SSMS Standard Reports (and DBCC ShowFileStats)

1) It actually uses DBCC ShowFileStats to produce these reports

DBCC showfilestats

Fileid FileGroup TotalExtents UsedExtents Name FileName

1 1 4560 2990 AW2012_Data D:\Program Files\xxxx

· Instant and accurate

· Use the Extent Information on the Page Header to calculate the space/extent used/unused

· Light impact on performances. Thus, a good choice for an overall summary of space used, but not at the page level.

2) Four reports

i. Disk Usage

This report provides overview of the utilization of disk space within the Database.

Total Space Usage: 394.75 MB

Data Files Space Usage: 285.00 MB

Transaction Log Space Usage: 109.75 MB

No entry found for autogrow/autoshrink event for AdventureWorks2012 database in the trace log.

Disk Space Used by Data Files

Filegroup Name

Logical File Name

Physical File Name

Space Reserved

Space Used

PRIMARY

AdventureWorks2012_Data

D:\Program Files\Microsoft SQL Server

\MSSQL11.MSSQLSERVER

\MSSQL\DATA\AdventureWorks2012_Data.mdf

285.00 MB

186.88 MB

Please note, the data reported in the above chart are slightly different from those returned from sp_spaceused as the standard report uses extents instead of pages as in sp_spaceused.

Here is the breakdown and comparison of the two methods:

	Standard Report		sp_spaceused
Total Space in MB	394.75		394.75
Data File Space usage (MB)	285
Data	33.46%	95.361	97.496
Index	29.52%	84.132	86.328
Unused	2.62%	7.467	7.656
Unallocated	34.39%	98.0115	98.01

xLog Space Usage (MB)	109.75
Used	4.30%	4.71925
Unused	95.70%	105.03075

On the Standard Report			On sp_spaceused
Note 1: Space reserved = 285			Note 1: Reserved = Data+Index+Unused=191.48
Note 2: Space used = Data+Index+Unused = 186.96			Note 2: No info about the log file

ii. Disk Usage by Table

This report provides detailed data on the utilization of disk space by tables within the Database.

Table Name	# Records	Reserved (KB)	Data (KB)	Indexes (KB)	Unused (KB)
dbo.all$	3,865	520	456	8	56
dbo.APEXSQL_LOG_CONNECTION_MONITOR_SESSION	1	16	8	8	0
dbo.APEXSQL_LOG_LOGIN	9	16	8	8	0
dbo.APEXSQL_LOG_STRINGS	8	16	8	8	0
dbo.AWBuildVersion	1	16	8	8	0
dbo.DatabaseLog	1,597	6,656	6,544	56	56
dbo.ErrorLog	0	0	0	0	0
HumanResources.Department	16	32	8	24	0
HumanResources.Employee	290	200	56	136	8

(……..more…….Omitted Sorted by Data Size in Desc order)

iii. Disk Usage by Top Tables

This report provides detailed data on the utilization of disk space by top 1000 tables within the Database.

Table Name	# Records	Reserved (KB)	Data (KB)	Indexes (KB)	Unused (KB)
Person.Person	19,972	84,632	30,496	52,808	52,616
Sales.SalesOrderDetail	121,317	16,256	9,880	5,904	472
Production.TransactionHistory	113,443	10,368	6,304	3,712	352
Sales.SalesOrderHeader	31,465	8,720	5,456	2,712	552

iv. Disk usage by Partition

This report provides detailed data on the utilization of disk space by index and by partitions within the Database.

Table Name			# Records	Reserved (KB)	Used (KB)
dbo.all$			3,865	520	464
	Table		3,865	520	464
dbo.APEXSQL_LOG_CONNECTION_MONITOR_SESSION			1	16	16
	Index (PK__APEXSQL___F017487445177593)		1	16	16
dbo.APEXSQL_LOG_LOGIN			9	16	16
	Index (IX_APEXSQL_LOG_LOGIN_2010)		9	16	16
dbo.APEXSQL_LOG_STRINGS			8	16	16
	Index (PK_APEXSQL_LOG_STRINGS)		8	16	16
dbo.AWBuildVersion			1	16	16
	Index (PK_AWBuildVersion_SystemInformationID)		1	16	16
dbo.DatabaseLog			1,597	6,656	6,600
	Table		1,597	6,608	6,552
	Index (PK_DatabaseLog_DatabaseLogID)		1,597	48	48
dbo.ErrorLog			0	0	0
	Index (PK_ErrorLog_ErrorLogID)		0	0	0
HumanResources.Department			16	32	32
	Index (PK_Department_DepartmentID)		16	16	16
	Index (AK_Department_Name)		16	16	16
HumanResources.Employee			290	200	192
	Index (PK_Employee_BusinessEntityID)		290	80	72
		Partition No.	# Records	Reserved (KB)	Used (KB)
		1	290	80	72

(……more….Omitted….)

3. Sys.dm_db_partition_stats

1) How to get the space used info for all tables in the current DB based on the pages?

SELECT o.name ,

SUM (p.reserved_page_count) as reserved_page_count,

SUM (p.used_page_count) as used_page_count,

SUM (

CASE

WHEN (p.index_id < 2) THEN (p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count)

ELSE p.lob_used_page_count + p.row_overflow_used_page_count

END

) as DataPages,

SUM (

CASE

WHEN (p.index_id < 2) THEN row_count

ELSE 0

END

) as rowCounts

FROM sys.dm_db_partition_stats p

inner join sys.objects o on p.object_id = o.object_id

group by o.name

name reserved_page_count used_page_count DataPages rowCounts

Address 716 672 344 19614

AddressType 6 6 1 6

............other tables in the DB are omitted………….

2) When to use it?

i. Better than sp_spaceused

ii. Use it if you need to show space used based on page for multiple tables in one query

iii. May not be accurate depending on how frequent the statistics are updated.

4. Sys.dm_db_index_physical_stats (or DBCC ShowContig in SQL Server 2000)

Returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition. Does not return information about xVelocity memory optimized columnstore indexes.

sys.dm_db_index_physical_stats (

    { database_id | NULL | 0 | DEFAULT }

  , { object_id | NULL | 0 | DEFAULT }

  , { index_id | NULL | 0 | -1 | DEFAULT }

  , { partition_number | NULL | 0 | DEFAULT }

  , { mode | NULL | DEFAULT }

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2012'), OBJECT_ID('Person.Address'), NULL, NULL, NULL)

/**** Selected Columns****

database_id object_id index_id alloc_unit_type_desc page_count avg_fragmentation_in_%

7 373576369 1 IN_ROW_DATA 340 0.88235

7 373576369 1 ROW_OVERFLOW_DATA 1 0

7 373576369 1 LOB_DATA 1 0

7 373576369 2 IN_ROW_DATA 64 0

7 373576369 3 IN_ROW_DATA 211 0

7 373576369 4 IN_ROW_DATA 34 0

On the Journey to be a SQL Server Professional

Four Major Methods to Check the Information about Space Used in SQL Server

1. sp_spaceused

1) What’s for?

2) Syntax

3) Three Versions

i. Without any parameters, return space used by the current database

ii. For a specific table/indexed view/queue

iii. Displaying updated space information about a database. See below for the reason.

4) Four major limitations for this sp:

5) When to use it?

i. Generally not recommended.

ii. If the DB is under rapid changes, need to use it with @updateusage. But be aware of the impacts on performance.

2. SSMS Standard Reports (and DBCC ShowFileStats)

1) It actually uses DBCC ShowFileStats to produce these reports

2) Four reports

i. Disk Usage

ii. Disk Usage by Table

iii. Disk Usage by Top Tables

iv. Disk usage by Partition

3. Sys.dm_db_partition_stats

1) How to get the space used info for all tables in the current DB based on the pages?

2) When to use it?

i. Better than sp_spaceused

ii. Use it if you need to show space used based on page for multiple tables in one query

iii. May not be accurate depending on how frequent the statistics are updated.

4. Sys.dm_db_index_physical_stats (or DBCC ShowContig in SQL Server 2000)

1) Most accurate and comprehensive

2) Cost: in order to calculate the information, SQL Server need to lock and scan the table at the page level. Three levels of details:

i. Limited – use the least number of pages, fastest. Scan all page for a heap table, but only at the immediate/root level pages of an indexed table.

ii. Sampled – 1%. If total pages<10,000, use Detailed instead.

iii. Detailed – all pages

3) When not to use it? – Do not use it at the peak time.