Disk Space usage overview

收集DB的Disk Space Usage的统计信息，是数据库空间管理的第一步，Sql server 提供了多种方式，或以extent，或以page 来统计 File Space usage 。

1，sp_spaceused 是以page 为单位进行统计的，缺点是一次只能查询一个object

sp_spaceused  是根据sys.allocation_units 和 sys.partitions 这两个DMV来计算存储空间的。有时，例如删除索引后，表的空间信息不是立即更新的，那么这两个DMV可能不能及时反映出数据库的准确信息，可以使用updateusage 参数来更新DMV的统计信息，但是，对于比较大的DB，更新DMV会消耗一定量的资源，在Sql Server 比较繁忙时候，updateusage会影响DB的性能。

When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.

Note:When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

2，sys.dm_db_partition_stats ，对DB内的所有object 进行统计，原理和sp_spaceused 一样。

sys.dm_db_partition_stats displays information about the space used to store and manage in-row data LOB data, and row-overflow data for all partitions in a database. One row is displayed per partition.

The counts on which the output is based are cached in memory or stored on disk in various system tables.

In-row data, LOB data, and row-overflow data represent the three allocation units that make up a partition. The sys.allocation_units catalog view can be queried for metadata about each allocation unit in the database.

If a heap or index is not partitioned, it is made up of one partition (with partition number = 1); therefore, only one row is returned for that heap or index. The sys.partitions catalog view can be queried for metadata about each partition of all the tables and indexes in a database.

The total count for an individual table or an index can be obtained by adding the counts for all relevant partitions.

Example Script

select o.name,
    sum(ps.reserved_page_count) as reserved_page_count,
    sum(ps.used_page_count) as used_page_count,
    sum( case when ps.index_id<2
                    then ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count
              else ps.lob_used_page_count+ps.row_overflow_used_page_count end
        ) as DataPages,
    sum( case when ps.index_id<2 then ps.row_count else 0 end) as RowCounts

from sys.dm_db_partition_stats ps
inner join sys.objects o
    on ps.object_id=o.object_id
group by o.name

3，最精确的方法，Sql server 需要对DB进行扫描，在扫描的过程中，Sql server 需要加锁。

使用 DBCC showcontig 或 sys.dm_db_index_physical_stats，这两种方式也能用来查看index碎片

4，按照 file 和 extent 来统计

这个命令直接从GAM 和 SGAM 系统page上读取Extent allocate 信息，直接计算DB file里有多少extent allocated 或 unallocated.

dbcc showfilestats

5，分析日志文件存储空间

dbcc sqlperf(logspace) 返回的结果总是准确的，语句的执行不会对sql server带来负担

dbcc sqlperf(logspace)

参考文档：

MSDN: sp_spaceused

MSDN: sys.dm_db_index_physical_stats

Disk Space usage overview

原文：http://www.cnblogs.com/ljhdo/p/5146423.html