In a recent interview, I had the opportunity to interact with several candidates who asked thought-provoking questions. While it is common for interviewees to ask questions, it is equally important for candidates to inquire about the interviewer’s experiences. One candidate asked me about the usage of Actual Execution Plans versus Estimated Execution Plans in SQL Server.
When it comes to execution plans, I always prefer using the Actual Execution Plan as it provides a more accurate representation of how a query will be executed. The Estimated Execution Plan, on the other hand, is not always reliable and can sometimes lead to incorrect assumptions. By running the query and examining the Actual Execution Plan, I can ensure that I have the correct and accurate plan.
If you want to quickly view the Estimated Execution Plan or include the Actual Execution Plan in SQL Server Management Studio (SSMS), you can use the following shortcuts:
- Shortcut for Display Estimated Execution Plan: CTRL + L
- Shortcut for Include Actual Execution Plan: CTRL + M
These shortcuts can save you time and provide valuable insights into query performance.
In SQL Server 2016, Microsoft introduced a new feature called “Compare Showplan” that enhances the analysis of execution plans. This feature is particularly useful for DBAs and developers involved in performance tuning and query optimization activities.
To use the “Compare Showplan” feature in SQL Server 2016, follow these steps:
- Select the “Compare Showplan” option.
- Select the execution plan generated from your test environment.
- Both the execution plans will be displayed side-by-side, allowing you to compare them easily.
One interesting aspect to note is the purple color on the nodes in the execution plans, which can provide additional insights into query performance.
Exploring execution plans is a crucial aspect of SQL Server performance tuning. By understanding how queries are executed and analyzing the execution plans, you can identify bottlenecks, optimize queries, and improve overall database performance.
What are your thoughts on this blog post? Have you used the Actual Execution Plan or Estimated Execution Plan in your SQL Server environment? Share your experiences and insights in the comments below!