Compiled Plan/ Execution Plan/ Query Plan: Are All Same or Different?

We all have worked with performance tuning and troubleshooting at different levels. As a result I assume we all worked with execution plans or query plans. When we talk about this subject more, it is noted that authors are using many terminologies sometime confusing when cross referencing the articles and try to correlate one another. So thought about publishing a blog post on this.  

So question is compiled plan, execution plan and query plan are all same or different. 

What is Compiled Plan?

It is a known fact that when SQL Server executes a query / statement / batch, it first creates something called a plan. What is this plan? Is it a compiled plan or execution plan?

To understand this, we need to think more about query execution. Any query before it executes, it has to pass compilation stage. The output of this stage is compiled plan. Compiled plan is generated and stored in plan cache. (Plan cache is a separate area of SQL Server memory)

You see the compiled plans created by SQL Server using the following DMV. The plan_handle is used to identify a compiled plan uniquely.

sys.dm_exec_cached_plans

What is Execution Plan?

After compilation stage, then comes the execution stage. This is the stage actual execution of the query occurs. So it substitutes the run time parameter values and create a execution plan. Therefore execution plan is a run time object.

To create a execution plan, there should be a compiled plan. So that execution plan is a dependent object of compiled plan.

The beauty of of the execution plan is, SQL Server generates an execution plan using the compiled plan, each time when the query executes. Then that execution plan has runtime parameter values. So unlike compiled plans execution plans are single use. When N users are executing the same batch, there will be N no.of execution plans. However as per MS, the generation of an execution plan is not that costly operation like generating a compiled plan.

When each user executes a batch, the parameter values are different than compiled plan. So that the user specific data should be maintained separately. The data structure uses for this purpose is called, Execution Context. The msdn definition is as below:

“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.”

Figure:1 Execution Context (courtesy of msdn)

So sounds like Execution plan and Execution context similar? (I do not know the answer yet)

What is Query Plan?

As msdn states, I believe query plan is another name for compiled plan. The definition given as below;

“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.”

Conclusion

I referred the following links to understand the concepts and more into internal stuff. Unfortunately, I did not find much places to see more detailed explanations about the topic. I appreciate the readers input into the topic if they know more about it or have some contradicting information in the content.

Cheers.

References

http://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx

http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/09/1-0-structure-of-the-plan-cache-and-types-of-cached-objects.aspx

Identifying the root blocker and the blocking chain

As a DBA, identifying blockings and waitings is one of the primary tasks. I searched in the Google but did not find a useful script. So I came up with one and thought to share it with others.

;WITH requests (session_id, start_time, status, blocking_session_id, 
      database_name, 
      command, sql_text) 
     AS (SELECT session_id, 
                start_time, 
                status, 
                blocking_session_id, 
                Db_name(database_id), 
                command, 
                sql_text = Cast(text AS VARCHAR(max)) 
         FROM   sys.dm_exec_requests WITH (nolock) 
                CROSS apply sys.Dm_exec_sql_text (sql_handle) 
         WHERE  status <> 'Background'), 
     blocking (session_id, start_time, status, blocking_session_id, command, 
     sql_text, rownum, levelrow) 
     AS (SELECT r1.session_id, 
                r1.start_time, 
                r1.status, 
                r1.blocking_session_id, 
                r1.command, 
                r1.sql_text, 
                Row_number() 
                  OVER ( 
                    ORDER BY r1.session_id), 
                0 AS LevelRow 
         FROM   requests r1 
                INNER JOIN requests r2 
                        ON r1.session_id = r2.blocking_session_id 
         WHERE  r1.blocking_session_id = 0 
         UNION ALL 
         SELECT r3.session_id, 
                r3.start_time, 
                r3.status, 
                r3.blocking_session_id, 
                r3.command, 
                r3.sql_text, 
                b.rownum, 
                b.levelrow + 1 
         FROM   requests r3 
                INNER JOIN blocking b 
                        ON r3.blocking_session_id = b.session_id 
         WHERE  r3.blocking_session_id > 0) 
SELECT * 
FROM   blocking 
ORDER  BY rownum, 
          levelrow 

Alternatively you can write the output of this to a table as a schedule job and the results can be analyzed later. This is really useful to identify any blocking patterns of the database server and fix them if they persist.

SQL Server 2014 changes ACID behavior

Any database professional know what it means by ACID. They are some golden properties of relational systems.

A – Atomicity

C – Consistency

I  - Isolation

D – Durability

SQL Server supports ACID properties fully until SQL Server 2012.  In SQL Server 2014 (still in CTP level) DBA has the option to control the D of ACID. Till SQL Server 2012, the log records need to be flushed to the disk before a transaction can be declared committed. However in SQL Server 2014, this statement is not 100% valid with the introduction of new feature called “delayed durability”.

With this new option, SQL Server transactions can be either fully durable or delayed durable. BOL states;

“Fully durable transaction commits are synchronous and report a COMMIT as successful and return control to the client only after the log records for the transaction are written to disk. Delayed durable transaction commits are asynchronous and report a COMMIT as successful before the log records for the transaction are written to disk. Hardening transaction log entries to disk is required for a transaction to be durable. Delayed durable transactions become durable when the transaction log entries are flushed to disk.”

As it obvious thing, the delayed durability comes with a cost that, there could be a data loss in the event like fail over. The advantages of this option are, reduce contention and increase throughput.

Use of delayed durability option is not suitable for all the cases. This can not be used in situations where you cant tolerate a data loss.

Delayed durability option can be applied at Database level, Atomic code block level and Commit level.

It is worth to be considered why Microsoft changed the ACID behavior after 22 years of SQL Server history. I believe this is an response to the highly volatile database technology market which we are seeing today, specially with the widely use of NoSQL technologies. NoSQL technologies are using “BASE” (Basically Available, Soft state, Eventual Consistency) against the ACID in relation systems. With the introduction of BASE the NoSQL vendors claim that the support for big data and huge performance gain in OLTP as opposed to the relational databases.

Finally, I believe it is a great option and DBAs will like the new feature. However, SQL Server 2014 is still in CTP which means it is subjected to change in future releases, sometimes this could be completely removed.

For more details, How to: Control Transaction Durability