What I learned writing about how to diagnose and troubleshoot high CPU in Azure SQL Database

Writing helps me learn. In my job as a Content Developer, this is more true than ever: there’s a fantastic group of folks, both in the Database Docs team and in the Microsoft Data Platform engineering team, who review and contribute to content.

I’ve just had the pleasure of publishing my first new article in the Microsoft Docs, Diagnose and troubleshoot high CPU on Azure SQL Database.

This article isn’t really “mine” – anyone in the community can create a Pull Request to suggest changes, or others at Microsoft may take it in a different direction. But I got to handle the outlining, drafting, and incorporation of suggested changes for the initial publication.

It was a ton of fun, and I learned a lot about Azure SQL Database in the process.

You can query your vCore count

Azure SQL Database lets you choose between two purchasing models: vCore and database transaction unit (DTU).

One benefit of the vCore model is that understanding and controlling the number of virtual cores allocated to your database is pretty straightforward: there’s a slider for the number of cores.

I did learn, however, that it can be worthwhile to query your virtual core count with Transact-SQL. That’s because:

• You might have more vCores than you think. For databases using Gen4 hardware, the number of schedulers in sys.dm_os_schedulers may be double the number of vCores specified at database creation and shown in Azure portal.
• If you are using the DTU purchasing model, you don’t get to specify the number of cores available.

We’ve got a simple query in the article which will return your current vCore count for a database.

You can switch between insights in the Azure portal and Query Store

There’s a nice area in the Azure portal called Query Performance Insight. You can see how much CPU was used over time along with top queries that ran in that time period.

I like this view and find it pretty easy to navigate. I had a theory that the query information was coming from Query Store, but I wasn’t certain. I found that in fact, it is – you can take the query ids from the portal and plug them into the graphic Query Store tools in SQL Server Management Studio (SSMS) and dig into query execution plans and historic execution stats that way. I like that these can work together.

“In flight” execution statistics are enabled by default in query plans

This is something that’s been around for a little while, but I missed that this became available by default. There’s a DMV, sys.dm_exec_query_statistics_xml, which returns “in flight” statistics for execution plans for currently running queries.

It used to be that you could only see what SQL Server estimated for queries that are currently running. But sometimes estimates are way off – sometimes the optimizer thinks it’s only going to get one row back, but it turns out to be millions. Or the opposite might happen. In-flight statistics help you see how many rows have flowed through different parts of a query plan so far.

In-flight statistics rely on something called lightweight query execution statistics profiling. Earlier versions of lightweight profiling can be enabled on SQL Server 2014 or 2016.

The latest version of lightweight query execution statistics profiling is enabled by default on Azure SQL Database and in SQL Server 2019.

You can get query compilation rates from Query Store

There are a few things in SQL Server which I’m used to collecting with perfmon counters. That can be handy if you’re used to it, but it lacks a level of detail: you get aggregate information about compilation overall, but you don’t know which queries were compiling or re-compiling. (And honestly, understanding what gets counted as a compile vs a recompile in perfmon counters is pretty confusing.)

In researching for the article, I found that you can query compilation rates from Query Store. This is great, because you can dig in and see which queries are being compiled most frequently during different time periods. You can query this with Transact-SQL.

Have suggestions for the article?

We’d love to read it! There’s two great ways to submit suggestions:

• Select the ‘Edit’ button at the top right of the page and submit a suggestion via a Pull Request in GitHub. This can be done entirely in the browser, no major GitHub experience is required (although you’ll need a free account)
• Head down to the ‘Feedback’ section at the very bottom of the article and select ‘This page’. This will take you to a screen where you can submit a GitHub issue detailing your thoughts. Again, you’ll need a free GitHub account for this, but no experience with GitHub is required.