隨著硬件能力的升級，以及軟件應用的擴展，現(xiàn)在的數(shù)據(jù)庫是越來越大了。回想10年前，一個上百GB的數(shù)據(jù)庫就會把支持工程師嚇得要命。而現(xiàn)在，上TB的數(shù)據(jù)庫真是比比皆是。
DBA遇到的一個常見問題，就是如何監(jiān)視數(shù)據(jù)空間的增長情況，或者在數(shù)據(jù)庫用滿的時候，能夠迅速定位誰是罪魁禍首。了解一個數(shù)據(jù)庫空間使用的最簡單方法，就是在Management Studio里，右鍵點擊數(shù)據(jù)庫名字，選擇”Reports” – “Standard Reports”，缺省就有4個Disk Usage的報表。它們能很好地統(tǒng)計出了從不同角度分析的數(shù)據(jù)庫空間使用情況。
但是在一個非常繁忙的生產(chǎn)環(huán)境里，隨隨便便地跑UI的報表總是有點頭皮發(fā)麻。而且有些詳細的信息，報表上可能沒有。所以許多DBA更喜歡用命令來查詢。SP_Spaceused是個大名鼎鼎的指令？上У氖牵慕Y(jié)果太籠統(tǒng)，也不精確。作者就從來不用它。
本文將介紹幾個常用的指令，能夠迅速檢查數(shù)據(jù)空間的使用情況，并比較它們的區(qū)別和不同。
SQL Server的數(shù)據(jù)庫文件分兩種：數(shù)據(jù)文件，主要是放數(shù)據(jù)的；日志文件，主要是放置事務紀錄，幫助SQL Server維護事務的一致性。兩類文件都有可能增長到很大。而且在空間耗盡的時候，SQL Server在這個數(shù)據(jù)庫上的操作都有可能會失敗。所以首先DBA要確定的，是到底哪一類文件使用量不正常。
檢查文件空間的方法不止一種，這里推薦一種又快又準的方法：
同時運行下面兩條命令。
Use <數(shù)據(jù)庫>
Go
dbcc showfilestats
go
dbcc sqlperf(logspace)
go
“dbcc showfilestats”命令會以Extent為單位，統(tǒng)計當前數(shù)據(jù)庫下所有數(shù)據(jù)文件里有多少個Extent，其中有多少個被使用過了。一個Extent是64K。乘一下，就能得到每個文件的使用情況。
這個命令直接從系統(tǒng)分配頁面上面讀取區(qū)分配信息，能夠快速準確地計算出一個數(shù)據(jù)庫數(shù)據(jù)文件區(qū)的總數(shù)和已使用過的區(qū)的數(shù)目，而系統(tǒng)分配頁上的信息永遠是實時更新的，所以這種統(tǒng)計方法比較準確可靠。在服務器負載很高的情況下也能安全執(zhí)行，不會增加額外系統(tǒng)負擔。所以看數(shù)據(jù)庫數(shù)據(jù)文件級的使用情況，它是個比較好的選擇。
“dbcc sqlperf(logspace)”命令的輸出非常淺顯易懂。它返回SQL里所有數(shù)據(jù)庫的日志文件當前使用量。這個命令的輸出也非？焖贉蚀_，使用安全。
通過這兩個命令，你應該能定位是日志文件用得太多，還是數(shù)據(jù)文件用得太多了吧。日志文件為什么用那么多，是個很熱門的話題。我們的Blog會另有討論。Tempdb的使用和一般用戶數(shù)據(jù)庫的使用也不同，我們的Blog也會另有討論。這里我們就只討論用戶數(shù)據(jù)庫數(shù)據(jù)文件的使用。
如果發(fā)現(xiàn)是數(shù)據(jù)庫文件用得太多，很自然地，就要去找是哪張表現(xiàn)在長得那么大？下面這個查詢可以很容易地回答你的問題。
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é)果的第一列是每個表的名字。SQL Server在使用數(shù)據(jù)頁的時候，為了提高速度，會先把一些頁面一次預留”reserve”給表格，然后真正有數(shù)據(jù)插入的時候，再使用。所以這里有兩列，Reserved_page_count和Used_page_count。兩列的結(jié)果相差一般不會很多。所以粗略來講，Reserved_page_count*8K，就是這張表格占用的空間大小。
DataPages是這張表數(shù)據(jù)本身占有的空間。因此，（Used_page_count – DataPages）就是索引所占有的空間。索引的個數(shù)越多，需要的空間也會越多。
RowCounts，是現(xiàn)在這個表里有多少行數(shù)據(jù)。
通過這些信息，DBA可以掌握數(shù)據(jù)庫的使用情況。
還有一個方法可以精確地統(tǒng)計出某張表格的空間使用量，那就是DBCC SHOWCONTIG（或者直接查詢系統(tǒng)管理視圖sys.dm_db_index_physical_stats）。它可以說是最精確的方法，可以告訴你某張表（或索引）用了多少頁面，多少區(qū)，甚至頁面上的平均數(shù)據(jù)量。從這些值可以算出一張表格占用了多少空間。然而，得到這些精確的數(shù)據(jù)也是要付出代價的。SQL Server從整體性能的角度出發(fā)，不可能一直維護這樣底層的統(tǒng)計信息。為了完成這個命令，SQL Server必須要對數(shù)據(jù)庫進行掃描。所以說，這種方式雖然精確，但是在數(shù)據(jù)庫處于工作高峰時，還是需要避免使用。
總之，一共有五種常見的分析數(shù)據(jù)文件存儲空間的方法。下面的表格里比較了它們的優(yōu)缺點和使用特點。
|
命令 |
精確單位 |
性能影響 |
準確性 |
|
dbcc showfilestats |
Extent |
無 |
基本準確 |
|
Sp_spaceused |
Page |
無 |
有時不準確 |
|
Sp_spaceused + updateusage |
Page |
稍有 |
基本準確 |
|
sys.dm_db_partition_stats |
Page |
無 |
有時不準確 |
|
Dbcc showcontig |
Page/Extent |
有 |
準確 |
如果管理者只需要看數(shù)據(jù)文件的整體使用情況，dbcc showfilestats是比較好的選擇。如果要看每個對象的空間使用情況，可以使用動態(tài)管理視圖sys.dm_db_partition_stats。如果還想了解每個page，每個extent的使用情況、碎片程度，那Dbcc showcontig是比較好的選擇。
加好友 
發(fā)短信
Post By：2013/11/7 14:10:39
