Tools I Used During Query Performance Troubleshooting
1. Execution Plan
- An execution plan in SQL Server is a detailed roadmap created by the SQL Server Query Optimizer to determine the most efficient way to execute a query. It shows which operators being used (e.g. scans, seeks, nested join, hash match, ...), data volume, access order, indexes being used, row estimates, and other valuable information that helps understand how the optimizer views and optimizes queries.
- There are two types of execution plan: estimated execution plan and actual execution plan. An Actual Execution Plan includes both estimated and run time properties, thus I prefer using actual execution plans for troubleshooting. An actual execution plan allows me to identify if there is a discrepancy in the estimated number of rows and the actual value.
- To view actual execution plans, we enable Include Actual Execution Plan on the tool bar and execute queries.
- However, if we encounter a situation when we can't run queries to obtain actual execution plans (e.g. we can't run queries that perform update/insert/delete on a production server), Estimated Execution Plan is an alternative since we don't need to run the query to obtain the plan. But again, with this plan, we can't see the actual row count and other runtime properties that can be useful for troubleshooting.
SELECT
FROM sys.dm_exec_cached_plans cp
CROSS apply sys.Dm_exec_query_plan_stats(cp.plan_handle) AS qp
WHERE qp.objectid = Object_id('Store procedure name')
2. SET STATISTICS IO, TIME ON
By wrapping a query with SET STATISTICS TIME, IO ON/OFF, we can view three important performance metrics: the number of logical reads, CPU time, and duration. This allows us to compare query performance and determine how slow or inefficient a query may be.
3. Statistics Parser
When a query involves many tables, the output from SET STATISTICS can be difficult to read and comprehend.
Fortunately, with the Statistics Parser, you can copy the entire statistics message, paste into the app, and it will present data in a table format. The tool also calculates the total number of logical reads for us.
4. Query Store
Query store is one of the tools that I use often to identify which queries are problematic in the first place.
You can query queries' plan, runtime stats, and wait stats from DMVs or you can use the Query Store reports built into SQL Server. Checkout this article for more details about different performance reports provided by Query Store.
6 reports that I often use are:
- Regressed queries: column statistics or index changes can cause SQL Server to recompile queries and use different plans to execute the queries. The new query plans may sometimes result in degraded performance. Those regressed queries are problematic and thus require tuning.
- Top resource consumption queries: your monitoring tool may tell you that you have high CPU usage or high disk latency, this report allows you to identify queries with highest CPU time and logical reads to investigate.
- Queries with forced plans: since SQL Server 2019, you can enable automatic plan forcing or you can force a plan temporarily. With this report, you can unforce the plan in the future and spend more time on tuning the queries.
- Queries with high variation: to identify queries with inconsistent performance due to reason such as parameter sniffing.
- Query Wait Statistics: to identify the most prevalent wait on the database and which queries that contribute to the wait.
- Tracked queries: if you have the query id of the problematic query, you can view query plan and runtime stats overtime through this report.
For example, someone tells you that a store procedure running slow. What you can do is identify the store procedure ID and use it to get the query ID, then use the Tracked queries report to troubleshoot:
DECLARE @sp_id AS BIGINT = SELECT object_id('Store procedure name');
SELECT qsp.query_id
FROM sys.query_store_query qsq
WHERE qsq.object_id = @sp_id;
Comments
Post a Comment