SQL Server重新利用数据表的空间

1.理解SQL Server怎样自动的重新利用数据表的空间,下面以实例来做简要的说明.

  1.1 建立临时测试数据

   创建一个测试table: Test,填空1000行数据.

  1. --create a test table  
  2. CREATE  TABLE dbo.Test  
  3. (  
  4. col1 INT  
  5. ,col2 CHAR(25)  
  6. ,col3  VARCHAR(4000)  
  7. ) ;  
  8. --create some test data  
  9. DECLARE @cnt INT ;  
  10. SET  @cnt = 0 ;  
  11. WHILE @cnt < 1000  
  12. BEGIN  
  13. SELECT  
  14. @cnt = @cnt +  1 ;  
  15. INSERT  
  16. dbo.Test ( col1,col2,col3 )  
  17. VALUES (  
  18. @cnt  
  19. ,'test  row # ' + CAST(@cnt AS VARCHAR(10)) + 'A'  
  20. ,REPLICATE('ABCD',  ROUND(RAND() * @cnt, 0))  
  21. ) ;  
  22. END  

  首先介绍一个 DMV:sys.dm_db_index_physical_stats

数据表空间的重用(Reusing space in a table)

  查看原图(大图)

  有几个重要的栏位: alloc_unit_type_desc:有效值为:IN_ROW_DATA,LOB_DATA,ROW_OVERFLOW_DATA page_count:储存数据行的数据页数 avg_page_space_used_in_percent:数据页使用的平均百分比 record_count:总的记录数根据这个DMV看看测试表Test的查询结果:

  1. --DMV SQL  
  2. select   alloc_unit_type_desc,page_count,avg_page_space_used_in_percent,record_count    
  3. from  sys.dm_db_index_physical_stats(db_id(),object_id('Test'),NULL,NULL,'Detailed')  
   结果如下:

数据表空间的重用(Reusing space in a table)

  可以看到1000条数据占用152个数据页,平均每个页空间使用率为84.2% 1.2 删除部分旧数据,再次查看数据空间使用率删除一半的数据.

  1. --delete the odd rows  
  2. DELETE  FROM Test WHERE col1 % 2 = 1  
  3. --view the table  
  4. SELECT * from Test  

   重新运行如上的DMV SQL语句得到:

数据表空间的重用(Reusing space in a table)

  由此可见,储存500条数据仍然占用152个数据页,平均每个页的数据空间使用率为 43.2%(几乎是84.2%的1/2) 1.3 在添加一些新的数据行

  1. --add some more test  data  
  2. DECLARE @cnt INT ;  
  3. SET @cnt = 0 ;  
  4. WHILE @cnt < 500  
  5. BEGIN  
  6. SET  @cnt = @cnt + 1 ;  
  7. INSERT INTO dbo.Test ( col1,col2,col3 ) VALUES (  
  8. @cnt,'test  row # ' + CAST(@cnt AS VARCHAR(10)),REPLICATE('WXYZ', ROUND(RAND() *  @cnt, 0))) ;  
  9. END  

  添加500条数据后,测试表Test现在有1000条数据, 再次重新运行如上的DMV SQL语句得到:

数据表空间的重用(Reusing space in a table)

 

  可以看到储存1000条数据依然用到152页,但是每个页的空间使用率从43.2%提高到了65.%,说明SQL Server重用表中多余的空间,而没有增加新的数据页.  在此请注意用到的测试表是用堆存储结构(没有建立索引),表中数据行没有预先特定的排序.在索引表中也同样适用,SQL Server会很好的重用表中空余的空间(holes). 1.4 CLean Up

  1. --clean up  
  2.   
  3.  DROP TABLE dbo.Test ;  

  2.SQL Server不自动回收空间时

  在某种情况下,SQL Server并不会自动的回收已经不用的空间.如果一个表的定义被改变(比如说DROP掉一个或者几个栏位),已经消耗掉的空间并不会被SQL Server立即重用. 下面举例说明: 2.1 建立测试数据

  1. CREATE TABLE dbo.Test2  
  2. (  
  3. col1  INT  
  4. ,col2 CHAR(25)  
  5. ,col3 VARCHAR(4000)  
  6. ) ;  
  7. --create some  test data  
  8. DECLARE @cnt INT ;  
  9. SET @cnt = 0 ;  
  10. WHILE @cnt <  1000  
  11. BEGIN  
  12. SET @cnt = @cnt + 1 ;  
  13. INSERT INTO dbo.Test2 (  col1,col2,col3) VALUES (  
  14. @cnt,'test row # ' + CAST(@cnt AS  VARCHAR(10)),REPLICATE('A', 4000)) ;  
  15. END  

数据表空间的重用(Reusing space in a table)

  查看原图(大图)

  同上一个例子一样运行SQL DMV的查询,针对table:Test2

  1. --SQL DMV  
  2. select  alloc_unit_type_desc,page_count,avg_page_space_used_in_percent,record_count    
  3. from  sys.dm_db_index_physical_stats(db_id(),object_id('Test2'),NULL,NULL,'Detailed')  
   可以看到:

数据表空间的重用(Reusing space in a table)

  1000行数据占用500个数据页,每个页的空间使用率为99.8% 2.2 DROP掉一个栏位(比如col3)

  1. ALTER TABLE dbo.Test2 DROP COLUMN col3 ;  

   运行SQL DMV之后看到的是:

数据表空间的重用(Reusing space in a table)

  和DROP掉一个栏位之前一样的是:1000行数据占用500个数据页,每个页的空 间使用率为99.8%.SQL Server并没有回收不用的空间,没有从数据页从移除掉那一栏的数据. SQL Server的做法只是更新系统表中的元数据,在查询时这一列不会作为结果集显示出来,数据所占的空间也不会被释放掉. 2.3 添加数据

  1. --insert  additional rows  
  2. DECLARE @cnt INT ;  
  3. SET @cnt = 0 ;  
  4. WHILE @cnt  < 500  
  5. BEGIN  
  6. SET @cnt = @cnt + 1 ;  
  7. INSERT INTO dbo.Test2 (  col1,col2 )VALUES (  
  8. @cnt,'test row # ' + CAST(@cnt AS VARCHAR(10))) ;  
  9. END  

   再次运行SQL DMV查询:

数据表空间的重用(Reusing space in a table)

  1500行的数据占用503个数据页,每个页的空间使用率为99.7%,可以看到新 增三个数据页.如果空间可以重用的话,应该会有足够多的空间存储新增的500行数据,并不需要新增数据页.

  3.空间回收(DBCC CLEANTABLE)

  在某些情况下SQL Server会自动的回收空间重复利用,但是在某些情况下则不会. 可以通过DBCC Command来回收不用的空间.

  1. DBCC CLEANTABLE('TEST', 'dbo.Test2') ;  

   运行过之后可以看到:

数据表空间的重用(Reusing space in a table)

  回收不用的空间以后,页面空间使用率大大降低.还是503个页,并没有压缩. 在DROP,DELETE之后用DBCC CLEANTABLE可以及时回收做删除标记的资料作占用的空间,可以更好的提高页面和磁盘的空间利用率.

posted @ 2010-08-23 14:13  路灯下的诗人  阅读(508)  评论(0编辑  收藏  举报