Lesson Learned #413:PerfStat: Performance Stats Collection for Azure SQL Database

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Some days ago, we published an article in our blog: Lesson Learned #412:PerfStat: Performance Stats Collection for SQL Server (OnPrem) Using DiagManager - Microsoft Community Hub . In this service request, our customer reported an execution timeout due to, most probably, due to a blocking issues. 

We have two additional articles to capture blocking issues: Lesson Learned #259: Capturing all blocking TSQL in Azure SQL DB and Managed Instance - Microsoft Community Hub and Lesson Learned #22: How to identify blocking issues? - Microsoft Community Hub but in this case we would like to have a deeper analysis about queries running, what they are executing, the main wait stats, etc.. 

This script helps a lot in this service request to identify a huge blocking change causing performance issues in the database. PerfStats Script for Azure SQL Database is located here: https://sqlperfstats.blob.core.windows.net/zip/sql-azure-perf-stats/sql-azure-perf-stats.zip

SQL Azure Perf Stats

Follow the instructions below to collect Perf Stats script output from your Sql Azure database

Prerequisites

• You must have an SQL login that has access to both the user database and the master database.
• You need to know the name of your database server, which will be provided to the script without including ".database.windows.net".
• Make sure your system has SQL Server tools installed, specifically sqlcmd.exe. You can verify its availability by running sqlcmd.exe in the command line. If you receive an error message indicating that it is "not recognized as an internal or external command," you need to locate sqlcmd.exe and add it to the "PATH" environment variable.

Steps to Use PerfStat on Azure SQL Database

Follow these steps to collect performance statistics using PerfStat on your Azure SQL database:

1. Download the PerfStat ZIP file from the provided link and extract it to a location of your choice on your system.

2. After extracting the files, verify that the following files are present, in addition to this README file:

   • PerfStats.ps1
   • SQL_Azure_Perf_Stats.sql

3. Open PowerShell and navigate to the directory where you extracted the PerfStat files. For example, use the following command to navigate to the "C:\perfstats" directory:

4. To run the script, change the PowerShell execution policy using the following command:

5. When a performance issue occurs, execute the PerfStats.ps1 script from PowerShell using the following command:

This will initiate the data collection process. Follow the prompts provided by the script to complete the process. Alternatively, you can provide the following parameters in the command to specify specific details of your database:

6. Press Ctrl+C to end the data collection process

7. Afterwards you will have an output folder containing <servername>_SQL_Azure_Perf_Stats.txt. 

The content of this file is very interesting to understand the workload of your database. It is very important don't leave too much time this script executing because is running every X seconds several DMVs that could impact in the performance of your database if it is very busy.

We need to use this script only when we need a deeper analysis what is happening in our database.

As you know, we have additional options, like Query Data Store, Performance Insights that might help on that without affecting in the database performance overall.

Enjoy!