sqlserver 数据库巡检脚本

--所有数据库状态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 ModelSELECT 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_enabledFROM sys.databases AS db WITH (NOLOCK)INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) ON db.NAME = lu.instance_nameINNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) ON db.NAME = ls.instance_nameWHERE 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 > 0OPTION (RECOMPILE);

--查看近的 Full Backup 信息--use #DB_nameSELECT 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 DESCOPTION (RECOMPILE);

--获取所有数据库的 VLF 数量/*VLF ：Virtual Log FileSQL 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 ,VLFCountFROM #VLFCountResultsORDER 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_versionFROM 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] = 2ORDER 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_idOPTION (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_compressedFROM sys.master_files mfCROSS 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 vsOPTION (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_MBFROM sys.master_files AS fCROSS 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_lockasbegin 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 @@ERROR0 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 @@ERROR0 RETURN @@ERROR -- 找到临时表的记录数 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who IF @@ERROR0 RETURN @@ERROR if @intCountProperties=0 select '现在没有阻塞和死锁信息' as message -- 循环开始 while @intCounter 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_schedulersWHERE scheduler_id < 255GROUP BY is_online ,[status];

--检查数据库中的所有对象的分配和机构完整性是否存在错误dbcc checkdb

--查询文件组和文件select df.[name],df.physical_name,df.[size],df.growth, f.[name][filegroup],f.is_defaultfrom sys.database_files df join sys.filegroups fon 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_countfrom 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_fileswhere type_desc = 'LOG';

--收缩数据库dbcc shrinkdatabase(CF_HIDB)

--查看某个表的结构use CF_TBMPROexec 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 datacountfrom sysindexes a , sysobjects bwhere a.id = b.id and a.indid < 2 and objectproperty(b.id, 'IsMSShipped') = 0order by datacount desc;

select sum(a.rowcnt) as '总条数'from sysindexes a , sysobjects bwhere 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_filesFROM 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 tableCREATE TABLE #ErrorLog ( logdate DATETIME ,processinfo VARCHAR(255) ,Message VARCHAR(500) )-- Populate the temporary tableINSERT #ErrorLog ( logdate ,processinfo ,Message )EXEC master.dbo.xp_readerrorlog 0 ,1 ,NULL ,NULL ,@Time_Start ,@Time_End ,N'desc';-- Filter the temporary tableSELECT LogDate ,MessageFROM #ErrorLogWHERE ( 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 #IOWarningResultsEXEC xp_readerrorlog 0 ,1 ,N'taking longer than 15 seconds';INSERT INTO #IOWarningResultsEXEC xp_readerrorlog 1 ,1 ,N'taking longer than 15 seconds';INSERT INTO #IOWarningResultsEXEC xp_readerrorlog 2 ,1 ,N'taking longer than 15 seconds';INSERT INTO #IOWarningResultsEXEC xp_readerrorlog 3 ,1 ,N'taking longer than 15 seconds';INSERT INTO #IOWarningResultsEXEC xp_readerrorlog 4 ,1 ,N'taking longer than 15 seconds';SELECT LogDate ,ProcessInfo ,LogTextFROM #IOWarningResultsORDER 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 tabORDER 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 fsINNER 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 DESCOPTION (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_idFROM 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_StatisticsAS ( 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_StatisticsORDER 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 vfsINNER 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 qtWHERE qt.[dbid] = DB_ID()ORDER BY [Avg IO] DESCOPTION (RECOMPILE);

--查询正在等待 I/O 的请求等待时间SELECT DB_NAME(database_id) AS [DBNAME] ,file_id ,io_stall ,io_pending_ms_ticks ,scheduler_addressFROM sys.dm_io_virtual_file_stats(NULL, NULL) iovfs ,sys.dm_io_pending_io_requests AS iopiorWHERE iovfs.file_handle = iopior.io_handle