作者：馬弓手三菜
SQL server的空間問題一直有客戶在詢問，今天就給大家彙總講解下SQL server 的全部空間開銷。
SQL server 的空間組成
從檔案類型來看，SQL server 的檔案類型分資料檔案（MDF,NDF），日志檔案（LDF）
從資料庫來看分為系統資料庫和使用者資料庫，其中系統資料庫中，最容易出現空間問題的，就是臨時資料庫（tempdb）
下面我們将分别來研究下空間的常見問題和解決方法。
使用者資料庫的資料檔案
正常情況下，資料檔案是随着資料庫使用，正常增長的。
sp_spaceused 
這裡給大家解釋下，這幾個參數。假設我們隻有一個MDF,和一個LDF。
那麼MDF 的檔案大小 = reserved(8142.5MB) + unallocated space (2250.37MB)
Reserved (8337992KB)= DATA(4248352KB) +INDEX_SIZE(4086384KB) + Unused(3256KB)
Database size(10393.94MB) = MDF(10392.94MB) + LDF(1MB)
但是有時候，因為頻繁更改，會帶來碎片（fragmentation）,碎片度太高，會導緻内部的空間浪費。同時，每個SQL 操作，因為碎片，可能要通路更多的頁面（page），導緻開銷變大。
可以通過這個指令檢視下目前資料庫的索引碎片。
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc 通常情況下，碎片度大于30%的索引，我們會選擇rebuild index。
ALTER INDEX [idx_name] ON [dbo].[test] REBUILD 當然rebuild 之後，這些碎片空間也不會直接被OS回收，而是作為資料檔案的可重用空間。
如果特别想回收這部分空間，可以嘗試下這個指令，回收下檔案尾部的空間。
dbcc shrinkfile(N'testdb',0,truncateonly) 使用者資料庫的日志檔案
SQL server的redo 段和 undo 段都是記錄在 T-Log的，是以很容易被撐大。
我們的SQL Server都是使用 FULL RECOVERY 級别，是以日志空間并不會自動釋放。
如何檢視LOG 大小
dbcc sqlperf(logspace) 可以看見testdb 這個庫，有15G的日志占用，切使用率99%
Log Reuse 的最常見的兩種原因：
1） Log backup
2) Active transaction
那麼，如何具體檢視LOG 等待 reuse的原因？
select name,log_reuse_wait_desc,* from sys.databases where name='testdb' 原因一：日志産生過快，日志備份頻率過低，等待日志備份
大部分情況，這裡應該是log_backup, 這時候，如果日志占用很大且使用率很高，就可以考慮調整下日志備份政策。修改的位址在 備份恢複-> 備份設定-> 編輯，我們這裡可以改成30分鐘一次，以提高備份頻率。
等到log使用率下降後，我們可以通過這個指令來shrink 日志檔案，也可以通過控制台上“收縮事務日志”按鈕來收縮日志。
-- 注意，可以通過這裡查詢下目前資料庫的日志檔案名，替換下文中的test_log
-- select name,* from sys.database_files where name='dbname'
-- 此處，将盡可能的讓testdb_log日志收縮到100MB
dbcc shrinkfile (N'test_log',100) 原因二：有活躍事務阻塞了日志空間釋放
如果是Active transaction, 那麼可以這麼去查
dbcc opentran 這裡就能看見阻塞者的開始時間，已經SPID。根據SPID，可以再查到這個會話最後一條SQL語句是什麼。
dbcc inputbuffer(64) 把session kill之後，再查下log_reuse_wait_desc ，如果變成log_backup，就可以嘗試下shrink 了
tempdb的空間問題
tempdb是一個非常特殊的db，每次執行個體啟動的時候，都會根據tempdb的預設值大小，重建tempdb檔案。是以tempdb的空間問題，都可以通過重新開機解決。重點在于，找到tempdb的開銷來自哪裡，從根源上優化。
tempdb的空間，主要分為三塊：
1） user objects
2) internal objects
3) versioning objects
想要看下目前tempdb的大小，可以試下這個語句
Select 'Tempdb' as DB, getdate() as Time,
SUM (user_object_reserved_page_count)*8 as user_objects_kb,
SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id = 2 這個指令可以檢視到目前tempdb的空間開銷
如果是user objects占用多，就要考慮下是否使用了大量的臨時表，表變量等自建對象。
如果是 internal objects占用多，就要去檢查自己的排序記憶體，hash記憶體是否不夠
如果是versioning 占用多，就要去檢查自己是否打開了versioning相關的設定，這個并發程度是否是預期内的。
如果是freespace 最大，就說明問題已經發生過了，需要定期抓取這個資料，等待問題重制來判斷是上述三個中的哪一個導緻的。
關于versioning可以查詢下這裡：
select is_read_committed_snapshot_on,snapshot_isolation_state_desc,*
from sys.databases 以上就是本期的全部内容，如果有任何疑問，可以在下方留言。