Measuring file IO performance characteristics using QPI library

One of the probably most important metric that you need to measure and monitor is IO performance characteristics. In most of the cases performance of your database depends on the speed of underlying storage and files where the data is stored.

If you want to be able to quickly troubleshoot some performance issues you need to know how to quickly determine IO performance of your files.

Measuring the file performance and comparing performance characteristics between SQL Server database engines in Azure cloud and your on-premises environment might be tricky and require some better knowledge of SQL server system objects and views. In this post, you will see how to use one open-source library that can help you to analyze and compare file performance.

This script will add a set of views/procedures in QPI schema in your database.

This is the baseline for IO statistics and you can get the cumulative/average values until this point.

Now you need to keep your workload running until the moment where you want to measure IO performance using the following view:

In the results of this view you can find information about the size of each file, IOPS, throughput, latency, etc.

You can also get the IO statistics in the current database:

The results are shown on the following image:

Most of the columns are self-explanatory, but there is difference in the following two:

You can also get the file statistics from some point in time in the past (make sure that you had done snapshot before this pint in time):

If you are comparing IO performance results on Managed Instance and your on-prem or Azure SQL VM (IaaS) try to compare the results from this query in order to get the consistent results and fair comparison.

QPI is a set of useful scripts that can help you to more easily analyze performance of your database files.

QPI is open-source library and if you find any issue in the functions feel free to post the bug submit or pull request with a fix.

Related posts:

Capitalism in and of itself is a great thing. It’s the opportunity to create products that are needed in the village, offer them, and expand and grow your business as people enjoy and love your…

It still gives me chills, When I recall -the way you called, My name while entering the room; A rush of excitement -and a heart attack, Because all those emotions -they bounce right back; When that’s…

The Christian life is spiritual. I believe having a clear understanding of what constitutes who we are as Christians in this life on Earth is the first step towards living well as Christians. To my…