Monthly Archives: November 2014

Intra-Query Parallel Deadlock

A deadlock happens when two tasks permanently block each other when one task is trying to get a resource to lock but the resource is already locked by the other task. SQL Server will chose one of the task (session) that has lower rollback cost, as a deadlock victim and rollback that transaction. The error message may look like this.

Msg 1205, Level 13, State 45, Line 5

Transaction (Process ID 52) was deadlocked on lock resources with

another process and has been chosen as the deadlock victim.

Rerun the transaction.

The deadlock may usually happen when queries in two sessions fight for resources each other in a cyclic manner. In this post I’m not going in deep on deadlock analysis or fixing. The post describes a special case of deadlock called ‘Intra-Query Parallel Deadlock’ that I encountered very recently.

Intra-Query Parallel deadlock happens on parallel query plan executions. One of our clients reported a frequent deadlock occurrence on a procedure. I ran scripts to query deadlock graph and the graph below looks different from usual deadlock graph.

(For clarity I’ve omitted some of the nodes and reformatted)

victim-list

victimProcess id=”process5554c8″/>

victim-list

process-list

process id=”process5554c8″

process id=”process4c434c8″

process id=”process55fdc8″

inputbuf

Proc [Database Id = 7 Object Id = 532912970]

resource-list

pagelock pageid=”5259244″ objectname=”” mode=”U”

owner-list

owner id=”process55fdc8″ mode=”U”

waiter-list

waiter id=”process5554c8″ mode=”U” requestType=”wait”

pagelock pageid=”5258622″ objectname=”” mode=”U”                     owner-list

owner id=”process5554c8″ mode=”U”

waiter-list

waiter id=”process4c434c8″ mode=”U” requestType=”wait”

pagelock

exchangeEvent id=”Pipe1b6aa3a50″ WaitType=”e_waitPipeGetRow” nodeId=”5″

waiter-list

waiter id=”process55fdc8″

The exchangeEvent node (marked in red above) is the key discussion point here. When a parallel query operator scans the data using multiple concurrent threads, an exchange operator is used to “glue” the rows at the end of execution of threads. The parallelism operator in the below plan is an exchange operator.

Parallel Plan

 

(You can read the a detailed discussion on Parallelism operator or exchange operator here)

In some rare scenarios the Parallelism operator cause deadlock to the input threads. From Craig Freedman’s presentation here(http://blogs.msdn.com/b/craigfr/archive/2007/04/17/parallel-query-execution-presentation.aspx), the scenarios are

  1. Two merging exchanges separated by order preserving operators
  2. A merge join with merging exchanges on both inputs
  3. Merging exchange above index seek with multiple ranges

Ultimately the query will deadlock due to the execution of the query itself!

The e_waitPipeGetRow is the wait type inside the exchange event. You can read more on this here.  Apart from exchangeEvent sometimes you can see threadpool also.

SQL Server will raise an error saying;

Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused your server command (process ID #51) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1)

As the message suggests, setting MAXDOP 1 (maximum degree-of-parallelism to 1), may solve the issue by using a single execution thread (a serial plan). However setting MAXDOP is not a good solution in all scenarios. Rewriting the query or defining proper indexes may avoid the parallel plan. As suggested by Microsoft, sometimes installing service packs may fix this issue.

The problem database server was SQL Server 2008 R2. I created an index and this lead to a serial plan and prevent the intra-query parallel deadlock!

Ultimately the problem sorted out and everyone was happy. This was a good learning experience for me.

Here are some resources you can read and enjoy!

Today’s Annoyingly-Unwieldy Term: “Intra-Query Parallel Thread Deadlocks”

Degrees of Parallelism and a Degree of Uncertainty

Handling Deadlocks in SQL Server

Understanding and Using Parallelism in SQL Server

 

Advertisements

Leave a comment

Filed under Administration, Internals, Performance

Introducing SQL Server Extended Events

Extended Events (XE or XEvents) is a diagnostic feature that was introduced with SQL Server 2008. This is one of the significant additions to SQL Server. However I feel this is the least used feature so far. This post is a brief introduction to extended events and its significance for diagnosing SQL Server and performance monitoring.

What happened to the good old SQL Profiler and Server side trace?

For a quite few years diagnosing problems spin around SQL Profiler tool or Server side trace feature (and trace flags and performance counters). But ask any SQL Server DBA this question.

“Do you use these tools in production?” The answer is a big “NO”.

SQL Profiler and server side trace are resource and memory intensive. Whatever filter you apply, they process huge volume of trace data and then apply the filter. Definitely this is not an ideal method for tracing in production environment.

More over SQL Profiler and server side tracing functionalities are deprecated in SQL Server 2012.

What are Extended Events?

SQL Server Extended Events are a highly scalable, event driven data collection infrastructure for SQL Server and databases. The term “Events” can be correlated to “action to be taken when an event fires”. The XEvents has far less overhead than the legacy server side trace or SQL Server profiler mechanisms.

Here is an excerpt from the book “Professional SQL Server 2012 Internals and Troubleshooting” by Christian Bolton et al.

Microsoft has measured just how lightweight the events architecture that Extended Events use actually is, and determined that 20,000 events per second fi ring on a server with a 2 GHz Pentium CPU and 1GB of memory consumed less than 2% of the CPU’s resource — and that’s on a very old specification server!

Why this is called Extended Events?

Windows operating systems (from Windows Server 2000) has a high-performance troubleshhoting feature called Event Tracing for Windows (ETW). You can read on ETW from here and here. Microsoft has Extended this Event tracing feature to use in SQL Server. So it has got the name Extended Events.

Is it possible migrate old SQL trace to new Extended Events?

If you still want to migrate your legacy SQL trace files here is a method. Mike Wachal from Microsoft has developed an SQL CLR procedure to do the migration. You can find the information here.

What’s next?

This post is just an introduction to XEvents to make the feet wet. I’m planning to write more posts on XEvents in the coming days. If you want to jump to the water here are some links.

Using SQL Server 2008 Extended Events

SQL Server Extended Events How-to Topics

 

Leave a comment

Filed under Administration, Extended Events, Performance