隨著硬件能力的升級(jí)，以及軟件應(yīng)用的擴(kuò)展，現(xiàn)在的數(shù)據(jù)庫是越來越大了。回想10年前，一個(gè)上百GB的數(shù)據(jù)庫就會(huì)把支持工程師嚇得要命。而現(xiàn)在，上TB的數(shù)據(jù)庫真是比比皆是。

 

DBA遇到的一個(gè)常見問題，就是如何監(jiān)視數(shù)據(jù)空間的增長情況，或者在數(shù)據(jù)庫用滿的時(shí)候，能夠迅速定位誰是罪魁禍?zhǔn)住?span style="mso-font-kerning: 0pt; mso-no-proof: yes">了解一個(gè)數(shù)據(jù)庫空間使用的最簡(jiǎn)單方法，就是在Management Studio里，右鍵點(diǎn)擊數(shù)據(jù)庫名字，選擇”Reports” – “Standard Reports”，缺省就有4個(gè)Disk Usage的報(bào)表。它們能很好地統(tǒng)計(jì)出了從不同角度分析的數(shù)據(jù)庫空間使用情況。

  

   

但是在一個(gè)非常繁忙的生產(chǎn)環(huán)境里，隨隨便便地跑UI的報(bào)表總是有點(diǎn)頭皮發(fā)麻。而且有些詳細(xì)的信息，報(bào)表上可能沒有。所以許多DBA更喜歡用命令來查詢。SP_Spaceused是個(gè)大名鼎鼎的指令�？上У氖�，它的結(jié)果太籠統(tǒng)，也不精確。作者就從來不用它。

 

本文將介紹幾個(gè)常用的指令，能夠迅速檢查數(shù)據(jù)空間的使用情況，并比較它們的區(qū)別和不同。

 

SQL Server的數(shù)據(jù)庫文件分兩種：數(shù)據(jù)文件，主要是放數(shù)據(jù)的；日志文件，主要是放置事務(wù)紀(jì)錄，幫助SQL Server維護(hù)事務(wù)的一致性。兩類文件都有可能增長到很大。而且在空間耗盡的時(shí)候，SQL Server在這個(gè)數(shù)據(jù)庫上的操作都有可能會(huì)失敗。所以首先DBA要確定的，是到底哪一類文件使用量不正常。

 

檢查文件空間的方法不止一種，這里推薦一種又快又準(zhǔn)的方法：

同時(shí)運(yùn)行下面兩條命令。

 

Use <數(shù)據(jù)庫>

Go

dbcc showfilestats

go

dbcc sqlperf(logspace)

go

 

“dbcc showfilestats”命令會(huì)以Extent為單位，統(tǒng)計(jì)當(dāng)前數(shù)據(jù)庫下所有數(shù)據(jù)文件里有多少個(gè)Extent，其中有多少個(gè)被使用過了。一個(gè)Extent是64K。乘一下，就能得到每個(gè)文件的使用情況。

 

 

 

這個(gè)命令直接從系統(tǒng)分配頁面上面讀取區(qū)分配信息，能夠快速準(zhǔn)確地計(jì)算出一個(gè)數(shù)據(jù)庫數(shù)據(jù)文件區(qū)的總數(shù)和已使用過的區(qū)的數(shù)目，而系統(tǒng)分配頁上的信息永遠(yuǎn)是實(shí)時(shí)更新的，所以這種統(tǒng)計(jì)方法比較準(zhǔn)確可靠。在服務(wù)器負(fù)載很高的情況下也能安全執(zhí)行，不會(huì)增加額外系統(tǒng)負(fù)擔(dān)。所以看數(shù)據(jù)庫數(shù)據(jù)文件級(jí)的使用情況，它是個(gè)比較好的選擇。

 

“dbcc sqlperf(logspace)”命令的輸出非常淺顯易懂。它返回SQL里所有數(shù)據(jù)庫的日志文件當(dāng)前使用量。這個(gè)命令的輸出也非常快速準(zhǔn)確，使用安全。

 

 

 

通過這兩個(gè)命令，你應(yīng)該能定位是日志文件用得太多，還是數(shù)據(jù)文件用得太多了吧。日志文件為什么用那么多，是個(gè)很熱門的話題。我們的Blog會(huì)另有討論。Tempdb的使用和一般用戶數(shù)據(jù)庫的使用也不同，我們的Blog也會(huì)另有討論。這里我們就只討論用戶數(shù)據(jù)庫數(shù)據(jù)文件的使用。

 

如果發(fā)現(xiàn)是數(shù)據(jù)庫文件用得太多，很自然地，就要去找是哪張表現(xiàn)在長得那么大？下面這個(gè)查詢可以很容易地回答你的問題。

 

Use <數(shù)據(jù)庫>

Go

SELECT o.name ,

                                 SUM (p.reserved_page_count) as reserved_page_count,

                                 SUM (p.used_page_count) as used_page_count,

                                 SUM (

                                                CASE

                                                                WHEN (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 (

                                                CASE

                                                                WHEN (p.index_id < 2) THEN row_count

                                                                ELSE 0

                                                END

                                                ) as rowCounts

                FROM sys.dm_db_partition_stats p inner join sys.objects o

                on p.object_id = o.object_id

                group by o.name

go

 

 

 

輸出結(jié)果的第一列是每個(gè)表的名字。SQL Server在使用數(shù)據(jù)頁的時(shí)候，為了提高速度，會(huì)先把一些頁面一次預(yù)留”reserve”給表格，然后真正有數(shù)據(jù)插入的時(shí)候，再使用。所以這里有兩列，Reserved_page_count和Used_page_count。兩列的結(jié)果相差一般不會(huì)很多。所以粗略來講，Reserved_page_count*8K，就是這張表格占用的空間大小。

DataPages是這張表數(shù)據(jù)本身占有的空間。因此，（Used_page_count – DataPages）就是索引所占有的空間。索引的個(gè)數(shù)越多，需要的空間也會(huì)越多。

 

RowCounts，是現(xiàn)在這個(gè)表里有多少行數(shù)據(jù)。

 

通過這些信息，DBA可以掌握數(shù)據(jù)庫的使用情況。

 

還有一個(gè)方法可以精確地統(tǒng)計(jì)出某張表格的空間使用量，那就是DBCC SHOWCONTIG（或者直接查詢系統(tǒng)管理視圖sys.dm_db_index_physical_stats）。它可以說是最精確的方法，可以告訴你某張表（或索引）用了多少頁面，多少區(qū)，甚至頁面上的平均數(shù)據(jù)量。從這些值可以算出一張表格占用了多少空間。然而，得到這些精確的數(shù)據(jù)也是要付出代價(jià)的。SQL Server從整體性能的角度出發(fā)，不可能一直維護(hù)這樣底層的統(tǒng)計(jì)信息。為了完成這個(gè)命令，SQL Server必須要對(duì)數(shù)據(jù)庫進(jìn)行掃描。所以說，這種方式雖然精確，但是在數(shù)據(jù)庫處于工作高峰時(shí)，還是需要避免使用。

 

總之，一共有五種常見的分析數(shù)據(jù)文件存儲(chǔ)空間的方法。下面的表格里比較了它們的優(yōu)缺點(diǎn)和使用特點(diǎn)。

命令	精確單位	性能影響	準(zhǔn)確性
dbcc showfilestats	Extent	無	基本準(zhǔn)確
Sp_spaceused	Page	無	有時(shí)不準(zhǔn)確
Sp_spaceused + updateusage	Page	稍有	基本準(zhǔn)確
sys.dm_db_partition_stats	Page	無	有時(shí)不準(zhǔn)確
Dbcc showcontig	Page/Extent	有	準(zhǔn)確

 

如果管理者只需要看數(shù)據(jù)文件的整體使用情況，dbcc showfilestats是比較好的選擇。如果要看每個(gè)對(duì)象的空間使用情況，可以使用動(dòng)態(tài)管理視圖sys.dm_db_partition_stats。如果還想了解每個(gè)page，每個(gè)extent的使用情況、碎片程度，那Dbcc showcontig是比較好的選擇。