Performance Tunning :- Find I/O Usage Per Databases in SQL Server

Through Below Script you are able to identify the I/O utilization By per databases in your SQL Server.The information returned by DMV is the data that SQL Server has been recorded since the last restart and therefore it is cumulative data:-


WITH IO_Uses_Per_DB
AS

(SELECT 
  DB_NAME(database_id) AS Database_Name
  , CONVERT(DECIMAL(12,2), SUM(num_of_bytes_read + num_of_bytes_written) / 1024 / 1024) AS Use_Total_Mb
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) dmivfs
 GROUP BY database_id)

 SELECT 
    Database_Name
    ,Use_Total_Mb
    ,CAST(Use_Total_Mb / SUM(Use_Total_Mb) OVER() * 100 AS DECIMAL(5,2)) AS [I/O_Uses%]
FROM IO_Uses_Per_DB

ORDER BY [I/O_Uses%] DESC;

 

Leave a Reply

Your email address will not be published. Required fields are marked *