This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
Yesterday, I worked on a customer who experienced a CPU spike that went 90% during the timeframe of 19:00 to 21:00. I would like to share how we identified the queries that potentially caused this CPU spike using the Query Store's Overall Resource Consumption report.
First, we accessed the Query Store feature in SQL Server Management Studio (SSMS). Query Store provides detailed insights into query performance and resource consumption, making it an invaluable tool for troubleshooting performance issues.
We configured the report to display key metrics that could indicate the cause of the CPU spike. Specifically, we selected:
- Execution Count: To see how frequently queries were executed.
- CPU Time: To identify queries consuming significant CPU resources.
- Degree of Parallelism (DOP): To check if high parallelism might be contributing to the CPU load.
Upon analyzing the report, we observed that there was indeed a significant CPU spike during the 19:00 to 21:00 window. The execution count and DOP also showed notable increases during this period.
-
With the list of potential culprit queries, we proceeded to:
- Review the execution plans to identify inefficiencies.
- Check for missing or outdated indexes.
- Analyze the query logic for potential optimizations.
- Consider adjusting the DOP for heavily parallelized queries.
By systematically using Query Store's Overall Resource Consumption report, we were able to pinpoint and analyze the queries causing the high CPU usage. This approach not only helps in resolving the immediate issue but also aids in proactive performance tuning to prevent future occurrences.
Enjoy!