Introduction to SQL Server Execution Plans
Any query run against SQL Server instance will be internally compiled and an execution plan will be generated which gets executed to satisfy the query and generate the required output. When a Query is submitted to the SQL Server instance, it determines different ways of completing this query and then chooses the best way of executing that particular query. To further improve the performance, then generated execution plan is saved and is reused if the same query comes again, thus saves the time taken to generate an execution plan.
If a particular query is reported to be running slow or taking long time to complete, then execution plans are the best way to look and understand what all operations the query is performing. SQL Server database engine uses various metrics like Statistics, Indexes to create all possible execution plans to satisfy the query and then chooses the best plan which would be the fastest. SQL Server uses cost based method to determine which execution plan is the better one compared to other plans possible for the same query. It estimates certain cost for each operation like certain cost for performing logical or physical IO, cost for Memory, CPU, Sort, Scan, etc.
There are different types of execution plans which SQL Server can generate which include Estimated plans and Actual Execution Plans. SQL Server has a pool of memory that is used to store both execution plans and data buffers. The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. The part of the memory pool that is used to store execution plans is referred to as the procedure cache.
SQL Server execution plans has the following two main components, which are Query plan and execution context.
Query Plan – The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. This is referred to as the query plan. No user context is stored in the query plan. There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. The parallel copy covers all parallel executions, regardless of their degree of parallelism.
Execution Context – Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. This data structure is referred to as the execution context. The execution context data structures are reused. If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.