運維腳本：文件的空間使用和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
  • dotnet C# 簡單的追加文件夾到 ZipArchive 壓縮文件的方法
    新改進提供的Taurus Rpc 功能，可以簡化微服務間的調用，同時可以不用再手動輸出模塊名稱，或調用路徑，包括負載均衡，這一切，由框架實現並提供了。新的Taurus Rpc 功能，將使得服務間的調用，更加輕鬆、簡約、高效。 ...
  • Elasticsearch 系列（六）- ES數據同步和ES集群
    本章將和大家分享ES的數據同步方案和ES集群相關知識。廢話不多說，下麵我們直接進入主題。 一、ES數據同步 1、數據同步問題 Elasticsearch中的酒店數據來自於mysql資料庫，因此mysql數據發生改變時，Elasticsearch也必須跟著改變，這個就是Elasticsearch與my ...
  • 自動化測試數據生成：Asp.Net Core單元測試利器AutoFixture詳解
    引言 在我們之前的文章中介紹過使用Bogus生成模擬測試數據，今天來講解一下功能更加強大自動生成測試數據的工具的庫"AutoFixture"。 什麼是AutoFixture? AutoFixture 是一個針對 .NET 的開源庫，旨在最大程度地減少單元測試中的“安排（Arrange）”階段，以提高 ...
  • .NET Emit 入門教程：第七部分：實戰項目1：將 DbDataReader 轉實體
    經過前面幾個部分學習，相信學過的同學已經能夠掌握 .NET Emit 這種中間語言，並能使得它來編寫一些應用，以提高程式的性能。隨著 IL 指令篇的結束，本系列也已經接近尾聲，在這接近結束的最後，會提供幾個可供直接使用的示例，以供大伙分析或使用在項目中。 ...
  • Windows下綁定線程到指定的CPU核心
    當從不同來源導入Excel數據時，可能存在重覆的記錄。為了確保數據的準確性，通常需要刪除這些重覆的行。手動查找並刪除可能會非常耗費時間，而通過編程腳本則可以實現在短時間內處理大量數據。本文將提供一個使用C# 快速查找並刪除Excel重覆項的免費解決方案。 以下是實現步驟： 1. 首先安裝免費.NET ...
  • mORMot 1.18 第07章 簡單的讀寫操作
    C++ 異常處理 C++ 異常處理機制允許程式在運行時處理錯誤或意外情況。它提供了捕獲和處理錯誤的一種結構化方式，使程式更加健壯和可靠。 異常處理的基本概念： 異常: 程式在運行時發生的錯誤或意外情況。 拋出異常: 使用 throw 關鍵字將異常傳遞給調用堆棧。 捕獲異常: 使用 try-catch ...
  • mORMot 1.18 第06章 概念
    優秀且經驗豐富的Java開發人員的特征之一是對API的廣泛瞭解，包括JDK和第三方庫。 我花了很多時間來學習API，尤其是在閱讀了Effective Java 3rd Edition之後 ，Joshua Bloch建議在Java 3rd Edition中使用現有的API進行開發，而不是為常見的東西編 ...
  • php 開發規範
    框架 · 使用laravel框架，原因：tp的框架路由和orm沒有laravel好用 · 使用強制路由，方便介面多時，分多版本，分文件夾等操作 介面 · 介面開發註意欄位類型，欄位是int，查詢成功失敗都要返回int（對接java等強類型語言方便） · 查詢介面用GET、其他用POST 代碼 · 所 ...
  • 2024 年 4 月 28 日 周日 晴 熱（1253 字）
    正文 下午找企業的人去鎮上做貸後。 車上聽同事跟那個司機對罵，火星子都快出來了。司機跟那同事更熟一些，連我在內一共就三個人，同事那一手指桑罵槐給我都聽愣了。司機也是老社會人了，馬上聽出來了，為那個無辜的企業經辦人辯護，實際上是為自己辯護。 “這個事情你不能怪企業。”“但他們總不能讓銀行的人全權負責， ...
  • mORMot 1.18 第08章 Delphi中的伺服器端JavaScript
    1. JUnit 最佳實踐指南 原文： https://howtodoinjava.com/best-practices/unit-testing-best-practices-junit-reference-guide/ 我假設您瞭解 JUnit 的基礎知識。 如果您沒有基礎知識，請首先閱讀（已針 ...
所有分類