Query Performance Insight - analyze performance of your SQL Server Database Engine
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
build
doc
src
LICENSE
README.md

README.md

Query Performance Insights

Query Performance Insights (QPI) is a collection of useful scripts that enable you find what is happening with your Azure SQL Database Managed Instance (most of the scripts would work on SQL Server 2016+). It is a set of views and functions that wrap Query Store and Dynamic Management Views. See how to install the proper version for your SQL Server in Installation section.

Why I need this kind of library?

SQL Server/Azure SQL Database provide a lot of views that we can use to analyze query performance (dynamic management views and Query Store views). However, sometime it is hard to see what is happening in the database engine. There are DMVs and Query Store, but when we need to get the answer to the simple questions such as "Did query performance changed after I added an index?" or "How many IOPS do we use?", we need to dig into Query Store schema, think about every single report, or search for some useful query. Also, for most of the views, you would need to read several articles to understand how to interpret the results.

This is the reason why I have collected the most useful queries that find information from underlying system views, and wrapped them in a set of useful views. Some usefull resources that I have used:

Examples

Let's start with some common examples of the queries that I use.

Getting information abouth your workload:

select * from qpi.queries; -- All recorded queries in Query Store

select * from qpi.dm_queries;           -- Currently running queries
select * from qpi.dm_bre;               -- Active Backup/Restore requests
select * from qpi.dm_blocked_queries;   -- Information about the currently blocked queries
select * from qpi.dm_query_locks;       -- Information about the locks that the queries are holding

select * from qpi.dm_cpu_usage; --  Information about CPU usage.
select * from qpi.dm_mem_usage; --  Information about memory usage.

Getting the information about the system performance:

select * from qpi.sys_info; --  Get CPU & memory
select * from qpi.volumes;  --  Get info about used and available space on the storage volumes

exec qpi.snapshot_file_stats;   --  Take the file statistics baseline
<run some query or workload>
select * from qpi.file_stats;   --  Get the file stats

exec qpi.snapshot_wait_stats;   --  Take the wait statistics baseline
<run some query or workload>
select * from qpi.wait_stats;   --  Get the wait stats

exec qpi.snapshot_perf_counters;    -- Take the performance counter baseline (required for some perf counters)
<run some query or workload>
select * from qpi.perf_counters;    -- Get the perf counter values

See more details about the available views and functions in the QPI API page. Find more information how to get the system info in system information page.

Performance analysis

QPI library simplifies query performance analysis. Some useful scripts and scenarios are shown in query performance analysis page and query performance page.

File performance

QPI library enables you to find performance of underlying file system - find more information in file statistics page.

Installation

QPI library is just a set of views, functions, and utility tables that you can install on your SQL Server or Azure SQL instance. Currently, it supports SQL Server 2016+ and Azure SQL Database. You can download the source and run it in your database. Choose the version based on your SQL Server version:

All functions, views, and tables are placed in qpi schema in your database.

Many views depends on Query Store so make sure that Query store is running on your SQL Server.

You can also remove all functions and views in qpi schema using the cleaning script.

If you are using SQL Agent on SQL Server and Azure SQL Managed you can easily take the snapshots of wait/file statistics. Thsi is needed because file and wait statistics compare the current statistics with the previous ones. You can create a job that periodically snapshot the file and wait statistics using the QPI Agent job script. Before you run this query, set the name of database where you created QPI functionalities:

DECLARE @database sysname = <'put the name of the database where QPI procedures are placed'>;