RDSforSQLserver空間問題排查彙總

馬弓手三菜發表於2018-10-26
SQLServer

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

 

以上就是本期的全部內容，如果有任何疑問，可以在下方留言。


相關文章