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

来源:http://www.cnblogs.com/ljhdo/archive/2017/06/28/4933384.html
-Advertisement-
Play Games

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


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

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

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

 

參考文檔：

sp_spaceused

sys.dm_db_file_space_usage (Transact-SQL)

sys.dm_db_partition_stats (Transact-SQL)


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 不常見正則表達式總結
    /^(0(?:[.](?:[1-9]\d?|0[1-9]))|[1-9]\d*(?:[.]\d{1,2}|$))$/ 不為0的正整數或帶1到2位小數的數字（以0打頭的則後面必須接小數點且小數點後面必然為01-99,以大於0的數打頭則小數可為1至2位任意數字或無小數部分.） ...
  • Picasso通過URL獲取--用戶頭像的圓形顯示
    1.設置佈局屬性： 2.BitmapUtils類-- 得到指定圓形的Bitmap對象 3.BitmapUtils類--壓縮圖片 4.根據user.getImageurl()顯示圓形圖像 ...
  • iOS各種調試技巧豪華套餐
    轉自：http://www.cnblogs.com/daiweilai/p/4421340.html 侵刪 目錄 前言逼優雞知己知彼 百戰不殆抽刀斷Bug 普通操作 全局斷點（Global BreakPoint） 條件斷點(Condational Breakpoints)列印的藝術 NSLog 開啟 ...
  • MVP架構
    一、介紹 MVP(Model View Presenter)架構是從著名的MVC(Model View Controller)架構演變而來的。對於在Android應用中開發就可以視為是MVC架構，佈局文件視為View，Activity視為Controller，但是Activity還要控制佈局的更新， ...
  • 音樂播放器後臺之歌曲讀取
    音樂播放器後臺之歌曲讀取 1.引言 & 160;& 160;& 160;& 160;C 中可以使用的第三方解決方案實在是不多，可以適用於UWP的解決方案就更少了。經過兩天的不懈努力，我算是基本上完成了讀取MP3文件中的標題、參與創作的藝術家、唱片集等信息，但是很遺憾，對MP3文件封面的讀取沒有實現。 ...
  • 自定義封裝 banner 組件
    1. 效果圖預覽 2.基本功能 3.基本實現 1. 自定義屬性 2.基本方法 更多用法 詳見代碼，這裡就不全部粘貼了。 3.使用示例： 項目源碼下載 導入自己項目 How to How to How to To get a Git project into your build: To get a ...
  • Android Animation 知識點速記備忘思維導圖
    備註的大段文本，無法在圖片中體現， 思維導圖源文件放在附件中。使用 Xmind 8 製作。 附件：AndroidAnimation-xmind.zip ...
  • 轉：MySQL與Oracle的區別
    1. Oracle是大型資料庫而Mysql是中小型資料庫，Oracle市場占有率達40%，Mysql只有20%左右，同時Mysql是開源的而Oracle價格非常高。 2. Oracle支持大併發，大訪問量，是OLTP最好的工具。 3. 安裝所用的空間差別也是很大的，Mysql安裝完後才152M而Or ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類