SqlServer数据空间使用情况分析

DBA遇到 的一个常见问题，就是如何监视数据空间的增长情况，或者在数据库用满的时候，能够迅速定位谁是罪魁祸首。了解一个数据库空间使用的最简单方法，就是在 Management Studio里，右键点击数据库名字，选择”Reports” – “Standard Reports”，缺省就有4个Disk Usage的报表。它们能很好地统计出了从不同角度分析的数据库空间使用情况。


   


但是在一个非常繁忙的生产环境里，随随便便地跑UI的报表总是有点头皮发麻。而且有些详细的信息，报表上可能没有。所以许多DBA更喜欢用命令来查询。SP_Spaceused是个大名鼎鼎的指令。可惜的是，它的结果太笼统，也不精确。作者就从来不用它。
  
 
 
本文将介绍几个常用的指令，能够迅速检查数据空间的使用情况，并比较它们的区别和不同。
  
 
 
SQL Server的数据库文件分两种：数据文件，主要是放数据的；日志文件，主要是放置事务纪录，帮助SQL Server维护事务的一致性。两类文件都有可能增长到很大。而且在空间耗尽的时候，SQL Server在这个数据库上的操作都有可能会失败。所以首先DBA要确定的，是到底哪一类文件使用量不正常。
  
 
 
检查文件空间的方法不止一种，这里推荐一种又快又准的方法：
 
同时运行下面两条命令。
  
 
 

SQL code

Use<数据库>Godbcc showfilestats godbcc sqlperf(logspace) go

  
 
 
“dbcc showfilestats”命令会以Extent为单位，统计当前数据库下所有数据文件里有多少个Extent，其中有多少个被使用过了。一个Extent是64K。乘一下，就能得到每个文件的使用情况。



这 个命令直接从系统分配页面上面读取区分配信息，能够快速准确地计算出一个数据库数据文件区的总数和已使用过的区的数目，而系统分配页上的信息永远是实时更 新的，所以这种统计方法比较准确可靠。在服务器负载很高的情况下也能安全执行，不会增加额外系统负担。所以看数据库数据文件级的使用情况，它是个比较好的 选择。
  
 
 
“dbcc sqlperf(logspace)”命令的输出非常浅显易懂。它返回SQL里所有数据库的日志文件当前使用量。这个命令的输出也非常快速准确，使用安全。



通 过这两个命令，你应该能定位是日志文件用得太多，还是数据文件用得太多了吧。日志文件为什么用那么多，是个很热门的话题。我们的Blog会另有讨论。 Tempdb的使用和一般用户数据库的使用也不同，我们的Blog也会另有讨论。这里我们就只讨论用户数据库数据文件的使用。
  
 
 
如果发现是数据库文件用得太多，很自然地，就要去找是哪张表现在长得那么大？下面这个查询可以很容易地回答你的问题。
  
 
 

SQL code

Use<数据库>GoSELECT o.name , SUM (p.reserved_page_count) as reserved_page_count, SUM (p.used_page_count) as used_page_count, SUM ( CASEWHEN (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 ( CASEWHEN (p.index_id <2) THEN row_count ELSE0END ) as rowCounts FROM[color=#FF0000]sys.dm_db_partition_stats [/color]p innerjoin sys.objects o on p.object_id= o.object_idgroupby o.name go

输 出结果的第一列是每个表的名字。SQL Server在使用数据页的时候，为了提高速度，会先把一些页面一次预留”reserve”给表格，然后真正有数据插入的时候，再使用。所以这里有两 列，Reserved_page_count和Used_page_count。两列的结果相差一般不会很多。所以粗略来 讲，Reserved_page_count*8K，就是这张表格占用的空间大小。
 
DataPages是这张表数据本身占有的空间。因此，（Used_page_count – DataPages）就是索引所占有的空间。索引的个数越多，需要的空间也会越多。
  
 
 
RowCounts，是现在这个表里有多少行数据。
  
 
 
通过这些信息，DBA可以掌握数据库的使用情况。
  
 
 
还 有一个方法可以精确地统计出某张表格的空间使用量，那就是DBCC SHOWCONTIG（或者直接查询系统管理视图sys.dm_db_index_physical_stats）。它可以说是最精确的方法，可以告诉你 某张表（或索引）用了多少页面，多少区，甚至页面上的平均数据量。从这些值可以算出一张表格占用了多少空间。然而，得到这些精确的数据也是要付出代价的。 SQL Server从整体性能的角度出发，不可能一直维护这样底层的统计信息。为了完成这个命令，SQL Server必须要对数据库进行扫描。所以说，这种方式虽然精确，但是在数据库处于工作高峰时，还是需要避免使用。
  
 
 
总之，一共有五种常见的分析数据文件存储空间的方法。下面的表格里比较了它们的优缺点和使用特点。




如 果管理者只需要看数据文件的整体使用情况，dbcc showfilestats是比较好的选择。如果要看每个对象的空间使用情况，可以使用动态管理视图 sys.dm_db_partition_stats。如果还想了解每个page，每个extent的使用情况、碎片程度，那Dbcc showcontig是比较好的选择。