--查看數據庫版本信息
select @@version
--查看所有數據庫名稱及大小
exec sp_helpdb

--所有數據庫狀態
select name,
	user_access_desc,--用戶訪問模式
	state_desc,--數據庫狀態
	recovery_model_desc,--恢復模式
	page_verify_option_desc,--頁檢測選項
	log_reuse_wait_desc--日志重用等待
from sys.databases;

--某個數據庫的大小
exec sp_spaceused
--刷新某個數據庫統計信息
dbcc updateusage('CF_TBMPRO')

--某個數據庫中的所有文件及大小
exec sp_helpfile
--查看所有文件所在數據庫、路徑、狀態、大小
select db_name(database_id) dbname,
	type_desc,
	name,
	physical_name,
	state_desc,
	size * 8.0/1024 as '文件大小（MB）'
from sys.master_files;

--某個數據庫大小統計，TotalExtents*64/1024(MB)，不統計日志文件
dbcc showfilestats

--統計某個數據庫所有的表信息

--查詢數據庫設置的 Recovery Model
SELECT db.[name] AS [Database Name]
    ,db.recovery_model_desc AS [Recovery Model]
    ,db.state_desc
    ,db.log_reuse_wait_desc AS [Log Reuse Wait Description]
    ,CONVERT(DECIMAL(18, 2), ls.cntr_value / 1024.0) AS [Log Size (MB)]
    ,CONVERT(DECIMAL(18, 2), lu.cntr_value / 1024.0) AS [Log Used (MB)]
    ,CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Log Used %]
    ,db.[compatibility_level] AS [DB Compatibility Level]
    ,db.page_verify_option_desc AS [Page Verify Option]
    ,db.is_auto_create_stats_on
    ,db.is_auto_update_stats_on
    ,db.is_auto_update_stats_async_on
    ,db.is_parameterization_forced
    ,db.snapshot_isolation_state_desc
    ,db.is_read_committed_snapshot_on
    ,db.is_auto_close_on
    ,db.is_auto_shrink_on
    ,db.target_recovery_time_in_seconds
    ,db.is_cdc_enabled
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) ON db.NAME = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) ON db.NAME = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
    AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
    AND ls.cntr_value > 0
OPTION (RECOMPILE);

--查看最近的 Full Backup 信息
--use #DB_name
SELECT TOP (30) bs.machine_name
    ,bs.server_name
    ,bs.database_name AS [Database Name]
    ,bs.recovery_model
    ,CONVERT(BIGINT, bs.backup_size / 1048576) AS [Uncompressed Backup Size (MB)]
    ,CONVERT(BIGINT, bs.compressed_backup_size / 1048576) AS [Compressed Backup Size (MB)]
    ,CONVERT(NUMERIC(20, 2), (CONVERT(FLOAT, bs.backup_size) / CONVERT(FLOAT, bs.compressed_backup_size))) AS [Compression Ratio]
    ,DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)]
    ,bs.backup_finish_date AS [Backup Finish Date]
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
WHERE DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0
    AND bs.backup_size > 0
    AND bs.type = 'D' -- Change to L if you want Log backups
    AND database_name = DB_NAME(DB_ID())
ORDER BY bs.backup_finish_date DESC
OPTION (RECOMPILE);

--獲取所有數據庫的 VLF 數量
/*VLF ：Virtual Log File
SQL Server 將日志文件 LDF 劃分為多個 VLF 以提高日志處理效率。VLF 的數量和大小不能通過配置指定，SQL Server 會按情況自行判斷處理，而如果生成了過多的 VLF 則會產生性能問題。通過指定合理的日志文件初始大小和增長步長，可以有效的防止過多 VLF 的產生。
	1M-64M 　　4
	64M-1GB    8
	>1GB       16
較高的 VLF 數量會影響寫入性能，并且會使數據庫的恢復過程變慢，通常需要保持 VLF Counts 在 200 以下。*/
CREATE TABLE #VLFInfo (
    RecoveryUnitID INT
    ,FileID INT
    ,FileSize BIGINT
    ,StartOffset BIGINT
    ,FSeqNo BIGINT
    ,[Status] BIGINT
    ,Parity BIGINT
    ,CreateLSN NUMERIC(38)
    );
CREATE TABLE #VLFCountResults (
    DatabaseName SYSNAME
    ,VLFCount INT
    );
EXEC sp_MSforeachdb N'Use [?]; 
                INSERT INTO #VLFInfo 
                EXEC sp_executesql N''DBCC LOGINFO([?])'';      
                INSERT INTO #VLFCountResults 
                SELECT DB_NAME(), COUNT(*) 
                FROM #VLFInfo; 
                TRUNCATE TABLE #VLFInfo;'
SELECT DatabaseName
    ,VLFCount
FROM #VLFCountResults
ORDER BY VLFCount DESC;
DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;



--查看數據庫所在機器的操作系統參數
exec master..xp_msver
--查看數據庫啟動的參數
exec sp_configure
--查看數據庫啟動時間
select convert(varchar(30),login_time,120)
from master..sysprocesses where spid=1

--查看數據庫服務器名
select 'Server Name:'+ltrim(@@servername)

--查看Windows 操作系統是什么版本
SELECT windows_release
    ,windows_service_pack_level
    ,windows_sku
    ,os_language_version
FROM sys.dm_os_windows_info WITH (NOLOCK)
OPTION (RECOMPILE);
--其中 windows_release 中的版本號代表著：
-- 6.3	Windows 8.1 or Windows Server 2012 R2   
-- 6.2	Windows 8 or Windows Server 2012 
-- 6.1 	Windows 7 or Windows Server 2008 R2
-- 6.0	Windows Vista or Windows Server 2008
-- 5.2	Windows XP or Windows Server 2003
--其中 windows_sku 代表著：
-- 4		Enterprise Edition  
-- 7		Standard Edition 
-- 48		Professional Edition

--查看數據庫實例名
select 'Instance:'+ltrim(@@servicename)
--數據庫的磁盤空間及使用信息
exec sp_spaceused
--日志文件大小及使用情況
dbcc sqlperf(logspace)
--表的磁盤空間使用信息
exec sp_spaceused 'tablename'
--獲取磁盤讀寫情況
select
@@total_read [讀取磁盤次數],
@@total_write [寫入磁盤次數],
@@total_errors [磁盤寫入錯誤數],
getdate() [當前時間]

--數據文件和日志文件位置和大小
SELECT DB_NAME([database_id]) AS [Database Name]
    ,[file_id]
    ,[name]
    ,physical_name
    ,type_desc
    ,state_desc
    ,is_percent_growth
    ,growth
    ,CONVERT(BIGINT, growth / 128.0) AS [Growth in MB]
    ,CONVERT(BIGINT, size / 128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
    AND [database_id] <> 32767
    OR [database_id] = 2
ORDER BY DB_NAME([database_id])
OPTION (RECOMPILE);

--查看指定數據庫文件的大小和可用空間
SELECT f.[name] AS [File Name]
    ,f.physical_name AS [Physical Name]
    ,CAST((f.size / 128.0) AS DECIMAL(15, 2)) AS [Total Size in MB]
    ,CAST(f.size / 128.0 - CAST(FILEPROPERTY(f.[name], 'SpaceUsed') AS INT) / 128.0 AS DECIMAL(15, 2)) AS [Available Space In MB]
    ,[file_id]
    ,fg.[name] AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id
OPTION (RECOMPILE);

SELECT db_name(vs.database_id) AS DatabaseName
    ,vs.file_id
    ,vs.volume_mount_point
    ,vs.volume_id
    ,vs.logical_volume_name
    ,vs.file_system_type
    ,(vs.total_bytes / 1024 / 1024 / 1024) AS [TotalSize(GB)]
    ,(vs.available_bytes / 1024 / 1024 / 1024) AS [AvailableSize(GB)]
    ,vs.supports_compression
    ,vs.supports_alternate_streams
    ,vs.supports_sparse_files
    ,vs.is_read_only
    ,vs.is_compressed
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs;

--服務器 Disk 容量和掛載信息
SELECT DISTINCT vs.volume_mount_point
    ,vs.file_system_type
    ,vs.logical_volume_name
    ,CONVERT(DECIMAL(18, 2), vs.total_bytes / 1073741824.0) AS [Total Size (GB)]
    ,CONVERT(DECIMAL(18, 2), vs.available_bytes / 1073741824.0) AS [Available Size (GB)]
    ,CAST(CAST(vs.available_bytes AS FLOAT) / CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Space Free %]
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
OPTION (RECOMPILE);

--查看 Disk 剩余空間
EXEC master.dbo.xp_fixeddrives

SELECT DISTINCT SUBSTRING(volume_mount_point, 1, 1) AS Volume_mount_point
    ,total_bytes / 1024 / 1024 AS Total_MB
    ,available_bytes / 1024 / 1024 AS Available_MB
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);




--獲取I/O工作情況
select @@io_busy,
@@timeticks [每個時鐘周期對應的微秒數],
@@io_busy*@@timeticks [I/O操作毫秒數],
getdate() [當前時間]
--查看CPU活動及工作情況
select
@@cpu_busy,
--@@timeticks [每個時鐘周期對應的微秒數],
@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作時間(秒)],
@@idle*cast(@@timeticks as float)/1000 [CPU空閑時間(秒)],
getdate() [當前時間]
--檢查鎖與等待
exec sp_lock
--檢查死鎖
exec sp_who_lock --自己寫個存儲過程即可
/*
create procedure sp_who_lock
as
begin
    declare @spid int,@bl int,
    @intTransactionCountOnEntry int,
    @intRowcount int,
    @intCountProperties int,
    @intCounter int
    create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)
    IF @@ERROR<>0 RETURN @@ERROR
    insert into #tmp_lock_who(spid,bl) select 0 ,blocked
    from (select * from sys.sysprocesses where blocked>0 ) a
    where not exists(select * from (select * from sys.sysprocesses where blocked>0 ) b
    where a.blocked=spid)
    union select spid,blocked from sys.sysprocesses where blocked>0
    IF @@ERROR<>0 RETURN @@ERROR
        -- 找到臨時表的記錄數
        select @intCountProperties = Count(*),@intCounter = 1
        from #tmp_lock_who
    IF @@ERROR<>0 RETURN @@ERROR
    if @intCountProperties=0
    select '現在沒有阻塞和死鎖信息' as message
    -- 循環開始
    while @intCounter <= @intCountProperties
    begin
    -- 取第一條記錄
    select @spid = spid,@bl = bl
    from #tmp_lock_who where id = @intCounter
    begin
    if @spid =0
        select '引起數據庫死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + '進程號,其執行的SQL語法如下'
    else
        select '進程號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '進程號SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其當前進程執行的SQL語法如下'
    DBCC INPUTBUFFER (@bl )
    end
    -- 循環指針下移
    set @intCounter = @intCounter + 1
    end
    drop table #tmp_lock_who
    return 0
end
*/

--用戶和進程信息
exec sp_who
exec sp_who2

--活動用戶和進程的信息
exec sp_who 'active'

--查看進程中正在執行的SQL
dbcc inputbuffer(進程號)
exec sp_who3
/*
CREATE PROCEDURE sp_who3 ( @SessionID INT = NULL )
AS
    BEGIN


        SELECT  SPID = er.session_id ,
                Status = ses.status ,
                [Login] = ses.login_name ,
                Host = ses.host_name ,
                BlkBy = er.blocking_session_id ,
                DBName = DB_NAME(er.database_id) ,
                CommandType = er.command ,
                SQLStatement = st.text ,
                ObjectName = OBJECT_NAME(st.objectid) ,
                ElapsedMS = er.total_elapsed_time ,
                CPUTime = er.cpu_time ,
                IOReads = er.logical_reads + er.reads ,
                IOWrites = er.writes ,
                LastWaitType = er.last_wait_type ,
                StartTime = er.start_time ,
                Protocol = con.net_transport ,
                ConnectionWrites = con.num_writes ,
                ConnectionReads = con.num_reads ,
                ClientAddress = con.client_net_address ,
                Authentication = con.auth_scheme
        FROM    sys.dm_exec_requests er
                OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
                LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
                LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
        WHERE   er.session_id > 50
                AND @SessionID IS NULL
                OR er.session_id = @SessionID
        ORDER BY er.blocking_session_id DESC ,
                er.session_id


    END
*/

--查看所有數據庫用戶登錄信息
exec sp_helplogins

--查看所有數據庫用戶所屬的角色信息
exec sp_helpsrvrolemember

--查看鏈接服務器
exec sp_helplinkedsrvlogin

--查看遠端數據庫用戶登錄信息
exec sp_helpremotelogin

--獲取網絡數據包統計信息
select
@@pack_received [輸入數據包數量],
@@pack_sent [輸出數據包數量],
@@packet_errors [錯誤包數量],
getdate() [當前時間]

--查看邏輯CPU情況,任務調度器（Scheduler）
SELECT is_online
    ,[status]
    ,COUNT(*) AS [count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
GROUP BY is_online
    ,[status];

--檢查數據庫中的所有對象的分配和機構完整性是否存在錯誤
dbcc checkdb

--查詢文件組和文件
select
    df.[name],df.physical_name,df.[size],df.growth,
    f.[name][filegroup],f.is_default
from sys.database_files df join sys.filegroups f
on df.data_space_id = f.data_space_id


--得到最耗時的前10條T-SQL語句
;with maco as
(
    select top 10
        plan_handle,
        sum(total_worker_time) as total_worker_time ,
        sum(execution_count) as execution_count ,
        count(1) as sql_count
    from sys.dm_exec_query_stats group by plan_handle
    order by sum(total_worker_time) desc
)
select  t.text ,
        a.total_worker_time ,
        a.execution_count ,
        a.sql_count
from    maco a
        cross apply sys.dm_exec_sql_text(plan_handle) t

--查看SQL Server的實際內存占用
select * from sysperfinfo where counter_name like '%Memory%'


--顯示所有數據庫的日志空間信息
dbcc sqlperf(logspace)
--查看日志文件所在數據庫、路徑、狀態、大小
select db_name(database_id) dbname,
	type_desc,--文件類型
	name,
	physical_name,--文件位置
	state_desc,--文件狀態
	size * 8.0/1024 as '文件大小（MB）'
from sys.master_files
where type_desc = 'LOG';

--收縮數據庫
dbcc shrinkdatabase(CF_HIDB)



--查看某個表的結構
use CF_TBMPRO
exec sp_help 'dbo.PRO_TBM_UIDataPlaceholder'
--查看視圖的定義
SELECT object_definition (object_id('sys.tables'));
EXEC sp_helptext 'sys.tables';

--查看數據庫中所有表的條數
select  b.name as tablename ,
        a.rowcnt as datacount
from    sysindexes a ,
        sysobjects b
where   a.id = b.id
        and a.indid < 2
        and objectproperty(b.id, 'IsMSShipped') = 0
order by datacount desc;

select  sum(a.rowcnt) as '總條數'
from    sysindexes a ,
        sysobjects b
where   a.id = b.id
        and a.indid < 2
        and objectproperty(b.id, 'IsMSShipped') = 0;


--清除 sql server錯誤日志文件
exec sp_cycle_errorlog

--SQL Server 的錯誤日志位置
SELECT is_enabled
    ,[path]
    ,max_size
    ,max_files
FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK)
OPTION (RECOMPILE);

--查詢近期的 Error Log 信息
DECLARE @Time_Start DATETIME;
DECLARE @Time_End DATETIME;
SET @Time_Start = getdate() - 2;
SET @Time_End = getdate();
-- Create the temporary table
CREATE TABLE #ErrorLog (
    logdate DATETIME
    ,processinfo VARCHAR(255)
    ,Message VARCHAR(500)
    )
-- Populate the temporary table
INSERT #ErrorLog (
    logdate
    ,processinfo
    ,Message
    )
EXEC master.dbo.xp_readerrorlog 0
    ,1
    ,NULL
    ,NULL
    ,@Time_Start
    ,@Time_End
    ,N'desc';
-- Filter the temporary table
SELECT LogDate
    ,Message
FROM #ErrorLog
WHERE (
        Message LIKE '%error%'
        OR Message LIKE '%failed%'
        )
    AND processinfo NOT LIKE 'logon'
ORDER BY logdate DESC
-- Drop the temporary table 
DROP TABLE #ErrorLog

--在錯誤日志中查詢 I/O 超過 15s 的請求
--如果能夠查詢出結果，可以說明 I/O 性能存在問題，但是哪里引起的還需進一步探索。
CREATE TABLE #IOWarningResults (
    LogDate DATETIME
    ,ProcessInfo SYSNAME
    ,LogText NVARCHAR(1000)
    );
INSERT INTO #IOWarningResults
EXEC xp_readerrorlog 0
    ,1
    ,N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults
EXEC xp_readerrorlog 1
    ,1
    ,N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults
EXEC xp_readerrorlog 2
    ,1
    ,N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults
EXEC xp_readerrorlog 3
    ,1
    ,N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults
EXEC xp_readerrorlog 4
    ,1
    ,N'taking longer than 15 seconds';
SELECT LogDate
    ,ProcessInfo
    ,LogText
FROM #IOWarningResults
ORDER BY LogDate DESC;
DROP TABLE #IOWarningResults;


--查詢 Disk 的性能指標
--通常 Latency 的值大于 20-25 ms 時可考慮有性能問題
SELECT [Drive]
    ,CASE 
        WHEN num_of_reads = 0
            THEN 0
        ELSE (io_stall_read_ms / num_of_reads)
        END AS [Read Latency (ms)]
    ,CASE 
        WHEN io_stall_write_ms = 0
            THEN 0
        ELSE (io_stall_write_ms / num_of_writes)
        END AS [Write Latency (ms)]
    ,CASE 
        WHEN (
                num_of_reads = 0
                AND num_of_writes = 0
                )
            THEN 0
        ELSE (io_stall / (num_of_reads + num_of_writes))
        END AS [Overall Latency (ms)]
    ,CASE 
        WHEN num_of_reads = 0
            THEN 0
        ELSE (num_of_bytes_read / num_of_reads)
        END AS [Avg Bytes/Read]
    ,CASE 
        WHEN io_stall_write_ms = 0
            THEN 0
        ELSE (num_of_bytes_written / num_of_writes)
        END AS [Avg Bytes/Write]
    ,CASE 
        WHEN (
                num_of_reads = 0
                AND num_of_writes = 0
                )
            THEN 0
        ELSE ((num_of_bytes_read + num_of_bytes_written) / (num_of_reads + num_of_writes))
        END AS [Avg Bytes/Transfer]
FROM (
    SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive
        ,SUM(num_of_reads) AS num_of_reads
        ,SUM(io_stall_read_ms) AS io_stall_read_ms
        ,SUM(num_of_writes) AS num_of_writes
        ,SUM(io_stall_write_ms) AS io_stall_write_ms
        ,SUM(num_of_bytes_read) AS num_of_bytes_read
        ,SUM(num_of_bytes_written) AS num_of_bytes_written
        ,SUM(io_stall) AS io_stall
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
    INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON vfs.database_id = mf.database_id
        AND vfs.file_id = mf.file_id
    GROUP BY LEFT(UPPER(mf.physical_name), 2)
    ) AS tab
ORDER BY [Overall Latency (ms)]
OPTION (RECOMPILE);


--查看哪個數據庫文件 I/O 瓶頸最嚴重
--考慮將數據庫文件移動到不同的磁盤上，或更快的磁盤陣列上以改進性能
SELECT DB_NAME(fs.database_id) AS [Database Name]
    ,CAST(fs.io_stall_read_ms / (1.0 + fs.num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
    ,CAST(fs.io_stall_write_ms / (1.0 + fs.num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
    ,CAST((fs.io_stall_read_ms + fs.io_stall_write_ms) / (1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
    ,CONVERT(DECIMAL(18, 2), mf.size / 128.0) AS [File Size (MB)]
    ,mf.physical_name
    ,mf.type_desc
    ,fs.io_stall_read_ms
    ,fs.num_of_reads
    ,fs.io_stall_write_ms
    ,fs.num_of_writes
    ,fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls]
    ,fs.num_of_reads + fs.num_of_writes AS [total_io]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON fs.database_id = mf.database_id
    AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC
OPTION (RECOMPILE);

--按照 Write I/O 進行排名
SELECT [ReadLatency] = CASE 
        WHEN [num_of_reads] = 0
            THEN 0
        ELSE ([io_stall_read_ms] / [num_of_reads])
        END
    ,[WriteLatency] = CASE 
        WHEN [num_of_writes] = 0
            THEN 0
        ELSE ([io_stall_write_ms] / [num_of_writes])
        END
    ,[Latency] = CASE 
        WHEN (
                [num_of_reads] = 0
                AND [num_of_writes] = 0
                )
            THEN 0
        ELSE ([io_stall] / ([num_of_reads] + [num_of_writes]))
        END
    ,[AvgBytesPerRead] = CASE 
        WHEN [num_of_reads] = 0
            THEN 0
        ELSE ([num_of_bytes_read] / [num_of_reads])
        END
    ,[AvgBytesPerWrite] = CASE 
        WHEN [num_of_writes] = 0
            THEN 0
        ELSE ([num_of_bytes_written] / [num_of_writes])
        END
    ,[AvgBytesPerTransfer] = CASE 
        WHEN (
                [num_of_reads] = 0
                AND [num_of_writes] = 0
                )
            THEN 0
        ELSE (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes]))
        END
    ,LEFT([mf].[physical_name], 2) AS [Drive]
    ,DB_NAME([vfs].[database_id]) AS [DB]
    ,[mf].[physical_name]
    ,[mf].file_id
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [vfs]
JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
ORDER BY [WriteLatency] DESC;


--獲取數據庫的 I/O 使用率
WITH Aggregate_IO_Statistics
AS (
    SELECT DB_NAME(database_id) AS [Database Name]
        ,CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS io_in_mb
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
    GROUP BY database_id
    )
SELECT ROW_NUMBER() OVER (
        ORDER BY io_in_mb DESC
        ) AS [I/O Rank]
    ,[Database Name]
    ,io_in_mb AS [Total I/O (MB)]
    ,CAST(io_in_mb / SUM(io_in_mb) OVER () * 100.0 AS DECIMAL(5, 2)) AS [I/O Percent]
FROM Aggregate_IO_Statistics
ORDER BY [I/O Rank]
OPTION (RECOMPILE);

--查看指定數據庫文件的 I/O 狀況,需要指定數據庫
SELECT DB_NAME(DB_ID()) AS [Database Name]
    ,df.[name] AS [Logical Name]
    ,vfs.[file_id]
    ,df.physical_name AS [Physical Name]
    ,vfs.num_of_reads
    ,vfs.num_of_writes
    ,vfs.io_stall_read_ms
    ,vfs.io_stall_write_ms
    ,CAST(100. * vfs.io_stall_read_ms / (vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10, 1)) AS [IO Stall Reads Pct]
    ,CAST(100. * vfs.io_stall_write_ms / (vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10, 1)) AS [IO Stall Writes Pct]
    ,(vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads]
    ,CAST(vfs.num_of_bytes_read / 1048576.0 AS DECIMAL(10, 2)) AS [MB Read]
    ,CAST(vfs.num_of_bytes_written / 1048576.0 AS DECIMAL(10, 2)) AS [MB Written]
    ,CAST(100. * vfs.num_of_reads / (vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10, 1)) AS [# Reads Pct]
    ,CAST(100. * vfs.num_of_writes / (vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10, 1)) AS [# Write Pct]
    ,CAST(100. * vfs.num_of_bytes_read / (vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Read Bytes Pct]
    ,CAST(100. * vfs.num_of_bytes_written / (vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
INNER JOIN sys.database_files AS df WITH (NOLOCK) ON vfs.[file_id] = df.[file_id]
OPTION (RECOMPILE);

--找出 I/O 平均使用最多的語句
SELECT TOP (50) OBJECT_NAME(qt.objectid, dbid) AS [SP Name]
    ,(qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS [Avg IO]
    ,qs.execution_count AS [Execution Count]
    ,SUBSTRING(qt.[text], qs.statement_start_offset / 2 + 1, (
            CASE 
                WHEN qs.statement_end_offset = - 1
                    THEN LEN(CONVERT(NVARCHAR(max), qt.[text])) * 2
                ELSE qs.statement_end_offset
                END - qs.statement_start_offset
            ) / 2) AS [Query Text]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC
OPTION (RECOMPILE);

--查詢正在等待 I/O 的請求等待時間
SELECT DB_NAME(database_id) AS [DBNAME]
    ,file_id
    ,io_stall
    ,io_pending_ms_ticks
    ,scheduler_address
FROM sys.dm_io_virtual_file_stats(NULL, NULL) iovfs
    ,sys.dm_io_pending_io_requests AS iopior
WHERE iovfs.file_handle = iopior.io_handle