Created
June 23, 2022 20:10
-
-
Save YhorbyMatias/dfc6391df6e0200a4e02ec2ef26ad2da to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment