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