Category Archives: Administration

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

Dedicated Administration Connection – A Knight’s Tale

Few days ago I wrote a logon trigger that prevents unauthorized applications from connecting to the development database server. The trigger code was simple and spans only a few lines of code and I just executed the script. And after a couple of seconds, I realized a logical mistake! Due to the logical error in the code, every user trying to connect was kicked out of the server. And as expected lot of complaints were piled up in front of admin’s desk in no time (unfortunately that’s my desk).

I was thinking of dropping the logon trigger and replied them back, “Don’t worry dude. I’m the admin and I’m here to help you. This will be resolved in no time.”

I tried to connect to the server. Oh my! The server is not accepting even my connection request. So the story begins here.

“Hey, I’m the King, the mighty Administrator. You are not allowing me to enter my castle?” asked the Administrator.

“Hey, King. I’m the Logon trigger, the valiant gate keeper. I’m instructed to prevent anyone who enters

Knight

through the gate even you the King!”

“What! But who instructed you that!”

“You, the King, and your ignorant instructions”

“I, the mighty Administrator, hereby order that, I will give half of my kingdom to the one who kills this Gate keeper!” the mighty Administrator requested for help.

And here comes the valiant knight DAC. The Dedicated Administrator Connection. DAC ingeniously made another way to reach the castle and the King killed the gate keeper. In the end the King, the mighty Administrator, announced the amended instructions to his people, and gave half of the Kingdom to DAC the Knight. The king and the people in the kingdom lived happily thereafter.

Long story short

The dedicated administrator connection is a special connection that allow administrator to enter a running server to troubleshoot, even if the server is unresponsive. The DAC bypasses even the logon trigger. The administrator can connect to the server through DAC by prefixing ADMIN: before server name box in SSMS connect window, or using –A command switch in sqlcmd. Since DAC is a single thread, don’t expect any query parallelism. There many limitations in DAC. The below Microsoft article gives a detailed list on this.

Using a Dedicated Administrator Connection

You can also check whether a DAC scheduler is running or not by querying the DMV sys.dm_os_schedulers. The status column will contain a value ‘VISIBLE ONLINE (DAC)’ that indicates a DAC scheduler is running.

DAC1

CAUTION: Only use DAC if the situation extremely demands.

End of Story: I connected to the server using DAC and dropped the logon trigger. Then corrected the trigger and compiled.

Leave a comment

Filed under Administration, DMV

Default schema for Windows group logins in SQL Server 2012

Alice is a tester in the Acme Corp. Alice has access to the testing database server through the Windows group login for the team [Acme\Testing]. When Alice creates a table without specifying a schema, her login name will be the default schema for the table as below:

[Acme\Alice].OrderDetail

When Bob from the same testing team tries to query the OrderDetail table by executing the following query, he will receive an error saying invalid object name. This is because he has specified the schema as ‘dbo’.

SELECT * FROM dbo.OrderDetail

A frustrated Bob reaches the DBA for help and they discussed the issue. After a few minutes of discussion, the DBA now understood the problem, the requirement, and fortunately the solution.

The problem is when a windows group user creates an object without specifying the schema, the windows login name is consider as the schema name (this is by design and exists there in SQL Server for quite some time). The requirement is to assign a default schema to a Windows group. And the good news is this is now possible in SQL Server 2012.

Using SSMS:

  1. Go to the database
  2. Expand Security and then Users node
  3. Open the Property page of the Windows user or group that you want to assign default schema

Default Schema for Win User in SQL Server 2012

In T-SQL you can use the below script

ALTER USER [Acme\Testing] WITH DEFAULT_SCHEMA=[dbo];

 

Leave a comment

Filed under Administration