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

　　查看原图（大图）

　　有几个重要的栏位: 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')  

 　　结果如下:

　　可以看到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语句得到:

　　由此可见,储存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语句得到:

 

　　可以看到储存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  

　　查看原图（大图）

　　同上一个例子一样运行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')  

 　　可以看到:

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

1. ALTER TABLE dbo.Test2 DROP COLUMN col3 ;  

　 　运行SQL DMV之后看到的是:

　　和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查询:

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

　　3.空间回收(DBCC CLEANTABLE)

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

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

　 　运行过之后可以看到:

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