Home / server / Questions / 96517
Accepted
blueberryfields
Asked: 2009-12-22 16:04:23 +0800 CST

What is the easiest way to get statistics on disk usage for my SQL Server database?

  • 772

I have a SQL Server database with multiple hundreds of data tables in it, and I am looking for disk usage statistics, as it relates to each individual table.

Is there a quick way to find this information?

sql-server

2 Answers

  1. Best Answer

    Marc Brown put together a stored procedure using the sp_spaceused and sp_tables system stored procedures to gather that information. Also, if you don't mind using a Microsoft private procedure, this stored procedure is a bit shorter method of accomplishing the same goal.

    • 1

  2. Which SQL Server version?
    Which 'usage' statistics? Size? Reads? Writes?

    For size you look into sys.allocation_units:

    select object_name(object_id) as [Object]
 , sum(au.total_pages)*8 as [Size (Kb)]
 from sys.allocation_units au
 join sys.partitions p on au.container_id = p.partition_id
 group by object_id
 having sum(au.total_pages) > 0

    For IO you look into sys.dm_db_index_usage_stats.

    Another possible approach is to enable performance collector sets and use the SQL Server 2008 Management Data Warehouse.

    • 0