SQL SERVER DATABASE FILES WISE I/O LATENCY PART#2

Since the DMF sys.dm_io_virtual_file_stats shows cumulative I/O statistics value for the database files, you can’t just use the DMF to shoot a conclusion that the database files are experiencing high latency issue. The reason is the overall aggregation will mask spikes of poor performance.

It is recommended to capture the latency data over a period to reach a conclusion. In this tips, we will explore how to capture I/O latency statistics data that occurred over a period using the DMF. 

But before that I would request you to have a look on the first part of this blog, that will help you to understand what all the information you will be capturing over the period  – SQL SERVER DATABASE FILES WISE I/O LATENCY PART#1

Let’s follow a step by step approach to capturing the period data;

Step1: 

  • Create a table ‘SQL_database_file_wise_I_O_latency’ the under master database
  • Captures the output from sys.dm_io_virtual_file_stats into the table

Step2:

  •  Archive two weeks older data from the table. You can modify the archiving period as per requirement.

Script to create SQL Server Job directly

  • Created a SQL Server job using the above scripts and scripted out the SQL job below. So that, you use the script to create a job directly to capture database files wise I/O latency details. Kindly run the script on the SQL Server instance where you want to capture the data.
Note: On the first successful execution of the job, the table “SQL_database_file_wise_I_O_latency” will be created in master database. If you want to keep the table in different database, you need to replace the master database with your database name.

  • Once you run the above script successfully, verify the created job.

  • Verify the job steps, it will contain two steps – Capturing & archiving the data

 

  • Check the job schedule – By default, it will be capturing the data every day every 10 minutes.

Do you have any better way to capture the data? Please share via comments to help others.

Leave a comment

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