運維腳本：文件的空間使用和IO統計

資料庫佔用的存儲空間，從高層次來看，可以查看資料庫文件（數據文件，日誌文件）佔用的存儲空間，從較細的粒度上來看，分為數據表，索引，分區佔用的存儲空間。監控資料庫對象佔用的硬碟空間，包括已分配，未分配，和未使用的空間佔比，能夠有效地管控存儲空間，合理利用，避免部分文件空間不足，而其他文件浪費空間的問題。

一，資料庫文件佔用的存儲空間

1，查看資料庫的各個文件佔用的存儲空間

select db.name as database_name,
db.is_auto_shrink_on,
db.recovery_model_desc,
mf.file_id,
mf.type_desc,
mf.name as logic_file_name,
mf.size*8/1024/1024 as size_gb,
mf.physical_name,
--mf.max_size,
mf.growth,
mf.is_percent_growth,
mf.state_desc
from sys.databases db
inner join sys.master_files mf
on db.database_id=mf.database_id
where mf.size*8/1024/1024>1 -- GB
order by size_gb desc

View Code

2，查看數據文件中已分配，未分配和混合區的空間

視圖：sys.dm_db_file_space_usage以Page為單位，實際上，Page的計數都是在區（Extent）級別上統計的，數據文件不夠GAM（Global Allocation Map）和SGAM（Shared Global Allocation Map）。

select db_name(mf.database_id) as db_name
,su.file_id
,su.filegroup_id
,mf.name as file_logic_name
,su.total_page_count*8/1024/1024 as total_gb
,su.allocated_extent_page_count*8/1024/1024 as allocated_gb
,su.unallocated_extent_page_count*8/1024/1024 as unallocated_gb
,su.mixed_extent_page_count*8/1024 as mixed_mb
from sys.dm_db_file_space_usage su
inner join sys.master_files mf
on su.database_id=mf.database_id
and su.file_id=mf.file_id

還有一個DBCC命令返回相同的數據，該命令以區（Extent）為單位，統計資料庫的文件上已分配的，未分配的區的數量：

dbcc showfilestats

該命令從系統page：GAM 和 SGAM 上讀取Extent的分配信息

3，使用查看當前資料庫的空間使用量

exec sys.sp_spaceused

資料庫的空間利用信息：

• database_size

  ：數據文件和日誌文件的大小；database_size 會比

  reserved

  + unallocated space的加和大，這是因為

  reserved

  和

  unallocated_space 僅僅是數據文件的大小；

• unallocated space

  ：數據文件中的未分配空間雖然佔用文件的硬碟空間，但是，沒有分配，不能被其他資料庫對象使用；

• reserved：

  數據文件中的保留空間，是已經分配的硬碟空間，能夠被其他資料庫對象使用；

• data

  ：數據佔用的硬碟空間；

• index_size

  ：索引佔用的硬碟空間；

• unused

  ：已經分配，但是未被使用的硬碟空間；

4， 統計SQL Server 實例中所有資料庫的日誌文件的硬碟空間使用

dbcc sqlperf(logspace)返回的結果總是準確的，語句的執行不會增加系統負擔

dbcc sqlperf(logspace)

二，查看資料庫中，各個數據表或索引所佔用的硬碟空間

1，查看資料庫所有資料庫表或索引所佔用的硬碟空間

;with cte_space as
(
select
ps.object_id
,sum(case when ps.index_id<2 then ps.row_count else 0 end) as row_count ,sum(ps.reserved_page_count)*8/1024 as reserved_mb ,sum(ps.used_page_count)*8/1024 as used_mb ,sum(case when ps.index_id<2 then ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count else 0 end )*8/1024 as data_used_mb from sys.dm_db_partition_stats ps inner join sys.tables t on ps.object_id=t.object_id group by ps.object_id ) select object_schema_name(s.object_id)+"."+object_name(s.object_id) as table_name ,s.row_count ,s.reserved_mb ,s.used_mb ,s.data_used_mb ,s.used_mb-s.data_used_mb as index_used_mb ,s.reserved_mb-s.used_mb as unsed_mb from cte_space s where s.reserved_mb>512 -- more than 512MB
order by unsed_mb desc
,index_used_mb desc

View Code

2，以索引為單位，查看數據表上各個索引佔用的硬碟空間

;with cte_space as
(
select
ps.object_id
,ps.index_id
,sum(ps.row_count) as row_count
,sum(ps.reserved_page_count)*8/1024 as reserved_mb
,sum(ps.used_page_count)*8/1024 as used_mb
,sum(ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count)*8/1024 as data_used_mb
,sum(ps.in_row_data_page_count)*8/1024 as in_row_data_used_mb
,sum(ps.lob_used_page_count+ps.row_overflow_used_page_count)*8/1024 as max_data_used_mb
from sys.dm_db_partition_stats ps
inner join sys.tables t
on ps.object_id=t.object_id
group by ps.object_id
,ps.index_id
)
select object_schema_name(s.object_id)+"."+object_name(s.object_id) as table_name
,i.index_id
,i.name as index_name
,s.row_count
,s.reserved_mb
,s.used_mb
,s.data_used_mb
,s.in_row_data_used_mb
,s.max_data_used_mb
,s.used_mb-s.data_used_mb as index_used_mb
,s.reserved_mb-s.used_mb as unsed_mb
from cte_space s
inner join sys.indexes i
on s.object_id=i.object_id
and s.index_id=i.index_id
where s.reserved_mb>512 -- more than 512MB
--and i.index_id>1
order by unsed_mb desc
,index_used_mb desc

View Code

3，在當前DB中，查看某一個數據表的空間使用信息

該存儲過程用於查看當前資料庫數據表的空間使用，返回的結果並不精確，默認情況下，該存儲過程底層使用系統視圖 sys.allocation_units和sys.partitions獲取數據表的所佔用空間的「近似」信息。當索引被刪除、索引被重建、或者大表被刪除（drop，truncate）時，資料庫引擎會延遲Page的釋放，延遲刪除導致不會立即釋放已分配的空間，在這種情況下，該存儲過程不會立即返回精確的空間使用信息。

exec sys.sp_spaceused "dbo.dt_study"

• rows

  :：數據表的總行數；

• reserved：

  數據文件中已分配的空間；

• data：

  數據文件中，基礎表佔用的空間；

• index_size：

  數據文件中，索引佔用的空間；

• unused：

  數據文件中，已分配，但是為被資料庫對象（基礎表和索引）使用的空間；

三，查看伺服器各個邏輯盤符剩餘的硬碟空間

Exec master.sys.xp_fixeddrives

四，壓縮文件

在SQL Server中，使用 DBCC ShrinkFile命令壓縮資料庫文件（數據文件和日誌文件），或直接把資料庫文件清空。除非硬碟空間不足，不要輕易收縮資料庫的文件，這會打亂索引的物理順序，大幅增加的索引外部碎片，影響查詢性能。

在執行DBCC ShrinkFile命令，收縮數據文件的時候，資料庫引擎首先把文件尾部的區（Extent）移動到文件的開頭，然後釋放文件末尾的空閑空間，歸還給操作系統。在移動Page時，資料庫引擎會掃描數據文件並對正在讀取的頁面加鎖，對資料庫的性能會有所影響。但是收縮操作不是一個獨佔行為，其他用戶仍然可以對資料庫進行讀寫操作。在進程中的任意一個時間點停止文件收縮操作，任何已經完成的工作都將保留。

收縮文件以區為單位，它會把文件末尾已分配的區前移，把未分配的區從文件末尾移除。該命令不會把一個區裡面的空閑頁面(empty page)移除，也不會合并區以釋放空閑頁面，如果資料庫中有很多隻使用了一兩個頁面的區，DBCC SHRINKFILE的效果會不明顯。

DBCC SHRINKFILE ( { file_name | file_id }, EMPTYFILE )
DBCC SHRINKFILE ( { file_name | file_id }, target_size , { NOTRUNCATE | TRUNCATEONLY } )

1，參數說明

target_size：是整數類型，單位是MB，資料庫引擎嘗試把文件收縮到指定的大小（Size），但不會收縮到小於數據實際存儲必需的空間。只有和參數NOTRUNCATE搭配使用，才起作用。

EMPTYFILE ：把數據從指定的文件遷移到同一個文件組中的其他文件中，也就是說，把當前文件清空，把數據轉存到其他文件中，數據的遷移只能在同一個文件組的不同文件之間進行。數據文件被清空之後，資料庫引擎不會把數據存儲到空文件中，可以使用 ALTER DATABASE 把文件從資料庫中移除。

NOTRUNCATE：只用於數據文件，對日誌文件不起作用；該參數用於把已分配（Allocated）的區（Extent）從數據文件的末尾移動到數據文件開頭的未分配（Unallocated）的空間中，文件末尾被釋放的空間不會返回給操作系統，仍然存在於文件中，處於未分配狀態，這意味著資料庫文件佔用的存儲空間不變。和target_size參數一起使用，用於指定文件收縮的大小。由於區（Extent）的移動是IO密集型操作，會影響資料庫的IO性能。

TRUNCATEONLY：把文件末尾的所有空閑空間都釋放，返回給操作系統，該參數不會執行任何的Page移動，也就是說，該參數收縮資料庫文件，而忽略target_size參數，收縮的硬碟空間以區（Extent）為單位。

2，收縮文件示例

想要收縮資料庫文件，釋放硬碟空間，需要分兩步：先移動，後釋放

Step1，將文件末尾已分配的區（extent）向前移動，移動到文件前端未被分配的區中，移動的區被標記為未分配（Unallocated）

dbcc shrinkfile("filename",0,notruncate)

Step2，將文件末尾的空閑空間（以區為單位）釋放，歸還給操作

dbcc shrinkfile("filename",target_size_MB,truncateonly)

五，IO請求的等待和掛起

資料庫引擎記錄對數據文件和日誌文件的IO操作，緩存到函數：sys.dm_io_virtual_file_stats，對於數據文件，數據的物理讀操作更為重要；對於日誌文件，數據的讀寫操作都重要：

• io_stall_read_ms

  ：等待讀操作的時間

• io_stall_write_ms

  ：等待寫操作的時間

如果硬碟繁忙，資料庫引擎發送的IO請求，可能會被IO子系統掛起（pending），資料庫引擎把pending的IO請求緩存到視圖：sys.dm_io_pending_io_requests，

• io_pending：

  指定是否有IO請求掛起或完成

1，查看資料庫文件的IO和等待IO完成的時間

select db_name(vfs.database_id) as db_name,
--vfs.file_id,
mf.name as file_name,
mf.type_desc as file_type,
vfs.sample_ms/1000/60/60 as sample_h,
vfs.io_stall_read_ms/vfs.num_of_reads as avg_stall_read_ms,
vfs.io_stall_write_ms/vfs.num_of_writes as avg_stall_write_ms,

vfs.num_of_reads as physical_reads,
vfs.num_of_bytes_read/vfs.num_of_reads/1024 as avg_read_kb,
vfs.num_of_writes as physical_writes,
vfs.num_of_bytes_written/vfs.num_of_writes/1024 as avg_written_kb,
cast(vfs.size_on_disk_bytes/1024/1024/1024.0 as decimal(10,2)) as disk_size_gb,
--cast(mf.size/1024*8/1024.0 as decimal(10,2)) as file_size_gb,
vfs.file_handle
from sys.master_files mf
cross apply sys.dm_io_virtual_file_stats(mf.database_id,mf.file_id) as vfs
where mf.database_id=db_id --current db
order by avg_stall_read_ms desc ,avg_stall_write_ms desc

View Code

2，查看pending的IO請求

select db_name(vfs.database_id) as db_name,
--vfs.file_id,
mf.name as file_name,
pr.io_type,
sum(pr.io_pending_ms_ticks) as io_pending_ms,
pr.io_pending
from sys.dm_io_virtual_file_stats(null,null) vfs
inner join sys.dm_io_pending_io_requests as pr
on vfs.file_handle=pr.io_handle
inner join sys.master_files mf
on vfs.database_id=mf.database_id
and vfs.file_id=mf.file_id
group by vfs.database_id,
mf.file_id,
mf.name,
pr.io_type,
pr.io_pending
order by vfs.database_id,
mf.name

View Code

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！