SQL Server keeps track of all the reads and writes it submits to the operating system (OS). It records this information in sys.dm_io_virtual_file_stats DMF. We can use the information recorded there in conjunction with the when the SQL was last restarted to figure total number of Input-Output Per Seconds (IOPS) and Bytes Per Second (BPS) generated by SQL Server.
You ask… WHY? Why do I care?
Well let’s say you are having problem with disk sub system, you have multiple databases you are trying to figure out which database generated the most IO? Or better yet which file did the most work …
You say AHA! I can just select everything virtual file stats DMF. True, True you can, you can find out which file has the most activity. But now you go to your SAN guy, you SAN is not good enough for my SQL Server.
SAN guy in return is going to say “Nope, I don’t see any issues…”
So now what?
This is where this information comes useful, you need to present to the SAN guy with total number of IOPS and Throughput per second SQL Server is generating. Then ask the SAN guy can the current configuration support you IOPS workload? Can you current HBA configuration or iSCSI configuration support the current workload?
So this is where this script comes in, up_Get_IOPS_n_BPS. Try it out and tell me what you think, I welcome comments.
SET NOCOUNT ON DECLARE @SQLRestartDateTime Datetime DECLARE @TimeInSeconds Float SELECT @SQLRestartDateTime = create_date FROM sys.databases WHERE database_id = 2 SET @TimeInSeconds = Datediff(s,@SQLRestartDateTime,GetDate()) PRINT 'Input-Output Per Second and Bytes Per Second by Database and File' PRINT '' SELECT DB_NAME(IVFS.database_id) AS DatabaseName , MF.type_desc AS FileType , MF.name AS VirtualFileName , MF.Physical_Name AS StorageLocation , ROUND((num_of_reads + num_of_writes)/@TimeInSeconds,4) AS IOPS , ROUND((num_of_bytes_read + num_of_bytes_written)/@TimeInSeconds,2) AS BPS FROM sys.dm_io_virtual_file_stats(null,null) IVFS JOIN sys.master_files MF ON IVFS.database_id = MF.database_id AND IVFS.file_id = MF.file_id ORDER BY DatabaseName ASC, VirtualFileName ASC PRINT 'Input-Output Per Second and Bytes Per Second by Database' PRINT '' SELECT DB_NAME(IVFS.database_id) AS DatabaseName , ROUND((SUM(num_of_reads + num_of_writes))/@TimeInSeconds,4) AS IOPS , ROUND((SUM(num_of_bytes_read + num_of_bytes_written))/@TimeInSeconds,2) AS BPS FROM sys.dm_io_virtual_file_stats(null,null) IVFS GROUP BY db_name(IVFS.database_id) ORDER BY DatabaseName ASC PRINT 'Total Input-Output Per Second and Bytes Per Second on Server' PRINT '' SELECT ROUND((SUM(num_of_reads + num_of_writes))/@TimeInSeconds,4) AS IOPS , ROUND((sum(num_of_bytes_read + num_of_bytes_written))/@TimeInSeconds,2) AS BPS FROM sys.dm_io_virtual_file_stats(null,null) IVFS SET NOCOUNT OFF
I will be adding additional scripts for this that let you calculate IOPS generated in last hour or last day also, maybe the heavy workload happened only recently. Buttttt… that’s for another day.
Example Output – Input-Output Per Second and Bytes Per Second by Database
Example – Total Input-Output Per Seconds and Bytes Per Seconds on Server
Update Oct 1, 2012:
Fixed the SQL Statement to get the last restart date. Added “Where database_id = 2”. Since TempDB is recreated every time, we can use that to calculate the our start up time.
SELECT @SQLRestartDateTime = create_date FROM sys.databases
What does this returns .. the last entry of the last created databases….
how is that good for SQLRestartDateTime?
You are correct. This does not. It should have a where clause checking for tempdb. Good catch. I’ll fix when I get to a computer.
Fixed. Sorry for the mistake, AnoopKim good catch again. Thanks :).
or you can always get the correct start time for SQL Server by querying sys.dm_os_sys_info DMV for ‘sqlserver_start_time’
Thanks for the tip. Didn’t consider looking in that DMV. I think these values are generally close the time SQL Starts to time TempDB created is usually with in matter of seconds, if these times are too far apart. Then maybe we have issue at SQL Server startup. Thanks again for tip :).
Was looking into setting up perfmon but then this script came handy to get IOPs to date since SQL restart. Ty
Your welcome :).
You can use this with deltas to understand what is generating IO if your system is having IO issues :).
Hi, Is the IOPS measure in Thousands?
0.3615 = 3,615(three thousand six hundred and fiftheen)?
IOPS represent each call SQL Server makes. Therefore 3615 is total number of reads and write requests made. No division are needed.