Essential Tips for Virtualizing SQL Server

Very good article on Vitalizing SQL Server. Read Essential Tips for Virtualizing SQL Server

ALTER PARTITION FUNCTION Causes Blocking?

Recently I’ve experienced a situation where one of the partition maintenance jobs created a blocking. At this point the database had high t-log usage and backup log was also executing. It was clear some kind of large transaction has occurred during a partition maintenance operation. All inserts into the table which was being partitioning, got blocked too. 

MSDN states:

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

ALTER PARTITION FUNCTION (Transact-SQL)

So it is important to select a proper partition key so that when achieving using sliding window concept, it works without impacting users.

Cheers.

Registry Check Pointing in a Windows Cluster to Bring SQL Server Online

Check this out!

Registry Check Pointing in a Windows Cluster to Bring SQL Server Online

How To Find Object Name For “wait_resource”

When querying sys.dm_exec_requests dmv, you can see the waiting requests and the resource those requests are waiting for. Sample of such values are shown in Figure 1 below.

Figure 1 – List of wait resources

How do you interpret these values? It has the following format in this case: (It is not always the same format. Depending on the values you see in wait_resource the interpretation would be different)

[database id] : [file id] : [page id]

However still the information is not sufficient because knowing the page id does not give much details related to the issue your troubleshooting. So we need to figure it out the table related to the above page. For that you need to use some undocumented DBCC commands. (Please be careful when running them in production system)

DBCC TRACEON (3604)
GO
DBCC PAGE (5, 20, 56792898)
GO
DBCC TRACEOFF (3604)

You get an output similar to below;

Figure 2 – Output of DBCC PAGE command

Refer the objectId value. That is the object Id of the page belongs to. After that it is simple to find the object name using OBEJCT_NAME() T-SQL function.

Cheers.

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