A Plan for Troubleshooting Plans

Bert Wagner takes us through a workflow for troubleshooting performance issues in SQL Server using execution plans:

With the query pasted and formatted in my SSMS editor window, I like retrieving the estimated execution plan first, and then pasting the query into a second editor window and executing the query with the “Include Actual Execution Plan” option turned on. For bonus points, I’ll split the SSMS window vertically so I can start looking at the estimated execution plan while the query runs and returns the actual execution plan: I like this combination because I (almost) immediately receive my estimated execution plan and can start looking for problems. Once the query on the right finishes executing and I get the actual plan with all of its lovely run-time stats, I usually switch to that looking at that one.

Even if your approach is quite different, it’s good to compare and contrast.

Related Posts

Batch Mode Normalization

Paul White digs into batch mode normalization and its consequences for performance: I mentioned in the introduction that not all eight-byte data types can fit in 64 bits. This fact is important because many columnstore and batch mode performance optimizations only work with data 64 bits in size. Aggregate pushdown is one of those things. There are […]

Read More

Comparing CAST and CONVERT Performance

Max Vernon runs a performance test of CAST versus CONVERT: This post is a follow-up to my prior post inspecting the performance of PARSE vs CAST & CONVERT, where we see that PARSE is an order of magnitude slower than CONVERT. In this post, we’ll check if there is a similar difference between using CAST or CONVERT. But just to be clear, CONVERT offers […]

Read More

Categories

August 2019
MTWTFSS
« Jul Sep »
 1234
567891011
12131415161718
19202122232425
262728293031