Our performance metrics will include:
- Rank- defines the I/O usage rating of the Disk
- Num of Reads – This tells how many number of reads issued on the file
- Number of Writes – This talks about the number of writes made on the file
- Number of Bytes Reads – This is number of bytes read on this file
- Number of Bytes Written – This gives a value of total number of byes written to a file
Using the following script:
USE MASTER
GO
DECLARE @DML nvarchar(MAX)
DECLARE @SQLShackIOStatistics TABLE
(
[I/ORank] [int] NULL,
[DBName] [nvarchar](128) NULL,
[driveLetter] [nvarchar](1) NULL,
[totalNumOfWrites] [bigint] NULL,
[totalNumOfBytesWritten] [bigint] NULL,
[totalNumOfReads] [bigint] NULL,
totalNumOfBytesRead [bigint] NULL,
[totalI/O(MB)] [decimal](12,2) NULL,
[I/O(%)] [decimal](5, 2) NULL,
[SizeOfFile] [decimal](10,2) NULL
)
SET @DML='
WITH SQLShackIOStatistics
AS
(
select
db_name(mf.database_id) as dbname,
left(mf.physical_name, 1) as driveLetter,
sum(vfs.num_of_writes) [totalNumOfWrites],
sum(vfs.num_of_bytes_written) [totalNumOfBytesWritten],
sum(vfs.num_of_reads) [totalNumOfReads],
sum(vfs.num_of_bytes_read) [totalNumOfBytesRead],
cast(SUM(num_of_bytes_read + num_of_bytes_written)/1024 AS DECIMAL(12, 2)) AS [TotIO(MB)],
MAX(cast(vfs.size_on_disk_bytes/1024/1024.00 as decimal(10,2))) SizeMB
from sys.master_files mf
join sys.dm_io_virtual_file_stats(NULL, NULL) vfs
on mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
GROUP BY mf.database_id,left(mf.physical_name, 1))
SELECT
ROW_NUMBER() OVER(ORDER BY [TotIO(MB)] DESC) AS [I/ORank],
[dbname],
driveLetter,
[totalNumOfWrites],
totalNumOfBytesWritten,
totalNumOfReads,
totalNumOfBytesRead,
[TotIO(MB)] AS [I/O(MB)],
CAST([TotIO(MB)]/ SUM([TotIO(MB)]) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O(%)],
SizeMB
FROM SQLShackIOStatistics
ORDER BY [I/ORank]
OPTION (RECOMPILE)
'
INSERT INTO @SQLShackIOStatistics
EXEC sp_executesql @DML
--SELECT * FROM @SQLShackIOStatistics
select [DBName],
[I/O Rank] =
STUFF(
(SELECT ',' + cast(s.[I/ORank] as varchar(3))
FROM @SQLShackIOStatistics s
WHERE s.[DBName] = t.[DBName]
FOR XML PATH('')),1,1,''),
physicalName=STUFF(
(SELECT ',' + s.driveLetter
FROM @SQLShackIOStatistics s
WHERE s.[DBName] = t.[DBName]
FOR XML PATH('')),1,1,'') ,
FileSizeMB=STUFF(
(SELECT ',' + cast(s.SizeOfFile as varchar(20))
FROM @SQLShackIOStatistics s
WHERE s.[DBName] = t.[DBName]
FOR XML PATH('')),1,1,'') ,
total_num_of_writes=STUFF(
(SELECT ',' + cast(s.[totalNumOfWrites] as varchar(20))
FROM @SQLShackIOStatistics s
WHERE s.[DBName] = t.[DBName]
FOR XML PATH('')),1,1,''),
total_num_of_bytes_written=STUFF(
(SELECT ',' + cast(s.[totalNumOfBytesWritten] as varchar(20))
FROM @SQLShackIOStatistics s
WHERE s.[DBName] = t.[DBName]
FOR XML PATH('')),1,1,''),
total_num_of_reads=STUFF(
(SELECT ',' + cast(s.totalnumofreads as varchar(20))
FROM @SQLShackIOStatistics s
WHERE s.[DBName] = t.[DBName]
FOR XML PATH('')),1,1,''),
total_num_of_Bytes_reads=STUFF(
(SELECT ',' + cast(s.totalNumOfBytesRead as varchar(20))
FROM @SQLShackIOStatistics s
WHERE s.[DBName] = t.[DBName]
FOR XML PATH('')),1,1,''),
[Total I/O (MB)]=STUFF(
(SELECT ',' + cast(s.[TotalI/O(MB)] as varchar(20))
FROM @SQLShackIOStatistics s
WHERE s.[DBName] = t.[DBName]
FOR XML PATH('')),1,1,''),
[I/O Percent]=STUFF(
(SELECT ',' + cast(s.[I/O(%)] as varchar(20))
FROM @SQLShackIOStatistics s
WHERE s.[DBName] = t.[DBName]
FOR XML PATH('')),1,1,'')
from @SQLShackIOStatistics t
group by [DBName]
Comments
Post a Comment