Follow the instructions below to collect Perf Stats script output from your Sql Azure database
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:
Download the PerfStat ZIP file from the provided link and extract it to a location of your choice on your system.
After extracting the files, verify that the following files are present, in addition to this README file:
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.