Execution plans in Microsoft SQL server

Estimated Execution Plan

This execution plan demonstrates the SQL optimizer perspective. It estimates the query execution procedure to obtain query results. It is a constructed plan, implying that the query is not run to obtain the execution plan.

To obtain an estimated execution plan for a query, perform the following steps:
 

• Switch to SSMS (SQL Server Management Studio)
• Highlight the query
• Click on Query
• Click Display Estimated Execution Plan
   

                                                         Source

The shortcut for this is Ctrl+L. If you want to execute a simple query, then select the query, and after that, press Ctrl+L. The execution plan will be displayed without a result window (i.e., the query does not need to be executed to obtain the execution plan), as seen below.

                                                               Source 

Actual Execution Plan

The Actual Execution Plan is also compiled, but this time with the execution context. The execution plan will be presented only when the query has been completed. This plan includes both actual and estimated results.

To obtain the actual execution plan of a query, perform the following steps:

• Switch to SSMS (SQL Server Management Studio)
• Highlight the query
• Click on the Query
• Click Include Actual Execution Plan

                                                                Source

Write a simple select query, then press Ctrl+M and F5 to execute it. The execution plan will be displayed alongside the result window (i.e., the query was conducted to obtain the execution plan), as seen below.

To get the actual execution plan, choose the highlighted option from the toolbar and press F5.

                                                               Source

Most of the time, the estimated and actual execution plan outcomes for basic queries are the same, but they may differ for complicated queries.

Cached Execution Plan

When a query is performed for the first time, it is compiled, and a query plan is generated. This query plan has been saved in the query plan cache of the SQL server. As a result, when the same query is conducted again, SQL does not need to recreate the query plan and instead uses the query plan from the SQL cache. As a consequence, query performance improves. The SQL server takes less time to execute the query than the first time.

SQL Server Management Studio has certain views and procedures for determining what is in the Cached Execution Plan. The cache query plan may be seen using the views and functions below.

• sys.dm exec cached plans (View)
• sys.dm exec sql text (Function)
• sys.dm exec query plan (Function)

Let's use these views and methods to get some cached data. Create a basic select query and run it. Refer to the image below:

                                                                        Source

The first line in the above image is for the query we executed, and the second entry is for the query we executed to acquire the cached data itself.

1. Usercounts 

Since we ran the first query twice, the value of the usercounts field is two.

2. Objtype

This column describes the object's kind.

3. Text 

This column represents the query that we executed.

4. Query plan 

The query execution plan is displayed in this column. You will be sent to the execution plan window when you click on this.

Clear the cache

To clear the cache, use the following command.

DBCC FREEPROCCACH

By clearing the cache, you can obtain the true time of query execution once more.

Frequently Asked Questions

What is SQL Server?

SQL Server is a Relational Database Management System (RDBMS) application software by Microsoft that can be used for designing, maintaining, managing, and deploying RDBMS systems. It is a widely used application because it allows numerous users to work on database systems simultaneously, with users ranging from small office workstations to massive Internet-based servers.

What are some of the common performance issues faced by users?

The common performance issues in SQL Server are as follows:

• Fragmentation
• Input/Output bottlenecks
• Blocking Queues
• Deadlocks
• Missing and unused indexes

Conclusion

In this blog, we have extensively discussed the execution plan of the Microsoft SQL Server. We have also discussed the different types of execution plans of Microsoft SQL Server, namely Estimated Execution Plan, Actual Execution Plan, and Cached Execution Plan. 

We hope that this blog has helped you enhance your knowledge regarding the topic of Execution Plans in Microsoft SQL Server, and if you would like to learn more, check out our articles on our platform Coding Ninjas Studio. 

Also Read - TCL Commands In SQL

Refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Data Structures and Algorithms, Competitive Programming, JavaScript, System Design, and many more! If you want to test your competency in coding, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio! But if you have just started your learning process and are looking for questions asked by tech giants like Amazon, Microsoft, Uber, etc; you must look at the problems, interview experiences, and interview bundle for placement preparations.

Nevertheless, you may consider our paid courses to give your career an edge over others!

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!