[MSSQL] 15. DISK I/O 많이 쓰는 데이터 파일 찾는 쿼리

DB를 구성하는 MDF, NDF, LDF 파일의 기본적인 용량과 IO Stats를 조회할 때 사용할 수 있습니다.

IF OBJECT_ID(N'tempdb..#DB') IS NOT NULL
BEGIN
	DROP TABLE #DB
END

CREATE TABLE #DB (name sysname, dbid smallint)

INSERT INTO #DB (name, dbid)
SELECT name, dbid FROM master.dbo.sysdatabases
WHERE name not in ('master', 'msdb', 'model')
GO

DECLARE @dbname sysname
DECLARE @min_dbid smallint
DECLARE @SQL varchar(8000)

IF OBJECT_ID(N'tempdb..#DBIOSTAT') IS NOT NULL 
BEGIN
     DROP TABLE #DBIOSTAT
END

CREATE TABLE #DBIOSTAT (
	  DBID INT 
	, dbname sysname
	, fileid INT
	, filename sysname
	, fgname sysname NULL
	, num_reads bigint
	, stall_read_ms bigint
	, avg_stall_read_ms NUMERIC(36, 2)
	, num_writes bigint
	, stall_write_ms bigint
	, avg_stall_write_ms NUMERIC(36, 2)
	, total_io bigint
	, total_stall_ms bigint
	, avg_stall_io_ms NUMERIC(36, 2)
	, total_io_MB NUMERIC(36, 2)
	, size_mb NUMERIC(36, 2)
	, free_mb NUMERIC(36, 2)
	, drive nvarchar(10)
	, drive_total_gb NUMERIC(36, 2)
	, drive_free_gb NUMERIC(36, 2)
);

SET @min_dbid = 0

WHILE (@min_dbid IS NOT NULL)
BEGIN
  SELECT @min_dbid = MIN(dbid) FROM #DB WHERE dbid > @min_dbid
  IF (@min_dbid IS NULL)  BREAK

  SELECT @dbname = name FROM #DB WHERE dbid = @min_dbid

  SET @SQL = 'USE [' + @dbname + ']; 
				SELECT 
				      DB_ID() AS DBID
					, DB_NAME() AS dbname
					, fs.file_id AS fileid
					, df.name AS fileName
					, fg.name AS fgName
					, fs.num_of_reads AS num_reads
					, fs.io_stall_read_ms AS stall_read_ms
					, ISNULL((fs.io_stall_read_ms * 1.0) / NULLIF(fs.num_of_reads,0), 0.0) AS avg_stall_read_ms
					, fs.num_of_writes AS num_writes
					, fs.io_stall_write_ms AS stall_write_ms
					, ISNULL((fs.io_stall_write_ms * 1.0) / NULLIF(fs.num_of_writes,0), 0.0) AS avg_stall_write_ms
					, (fs.num_of_reads + fs.num_of_writes) AS total_io
					, fs.io_stall AS total_stall_ms
					, ISNULL((io_stall * 1.0) / NULLIF(fs.num_of_reads + fs.num_of_writes,0), 0.0) AS avg_stall_io_ms
					, (num_of_bytes_read + num_of_bytes_written)/(1024.0*1024.0) AS total_io_MB
					, CONVERT(NUMERIC(36,2),df.SIZE/128.0) AS size_mb
					, CONVERT(NUMERIC(36,2),df.SIZE/128.0 - CAST(FILEPROPERTY(df.NAME, ''SPACEUSED'') AS INT)/128.0) AS free_mb
					, vs.volume_mount_point AS drive
					, vs.total_bytes/(1024.0*1024.0*1024.0) AS drive_total_gb
					, vs.available_bytes/(1024.0*1024.0*1024.0) AS drive_free_gb
				FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) fs
				INNER JOIN sys.database_files df ON fs.file_id = df.file_id
				LEFT OUTER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id 
				CROSS APPLY sys.dm_os_volume_stats(fs.database_id, fs.file_id) vs
				ORDER BY fs.file_id
				'
  INSERT INTO #DBIOSTAT 
  EXEC (@SQL)

  --UPDATE #DBIOSTAT SET dbname = @dbname WHERE dbname IS NULL
END

SELECT * 
FROM #DBIOSTAT 
ORDER BY DBID, fileid;