Parallel Query Deadlocks

SSWUGtv – New Show!
Will the cloud HELP our databases? Laura Rose, the news of the day and more. Also, the first in a series of segments on scale up vs. scale out and options that are available.
[Watch the Show]

Interview with Denny Cherry, SQL Server MVP and SSWUG.ORG Class Presenter. We also have the latest news on Silverlight 5 and insights on Big Data from MVP Stephen Wynkoop.
[Watch the Show]

Parallel Query Deadlocks
One of our readers writes in with an error, “Parallel query worker thread was involved in a deadlock”, asking what the cause may be and how to resolve it.

This is one of the true blessings of the multiple core CPU generation, allowing true multi-threading to occur on even commodity hardware. SQL Server will review a query and determine if it can perform some of the steps to return the final answer by splitting up the work into separate queries, and then brining the different pieces back together.

In this case, at least one query in a SQL Batch was a participant in a deadlock event. A deadlock event occurs when two different queries require a resource locked by the other query in order to complete. In this case, unless one of the queries is canceled, neither query may complete, and the locks are held indefinitely.

It is possible that the deadlock occurred in the single SQL Batch between its multiple threads. Another possible cause is that one of the parallel threads conflicted with some other process running at the same time.

Firstly, I recommend “SQL Server DMVs In Action” by Ian Stirk, as a great resource to get a handle on what causes these kinds of issues, how to discover the root cause through the use of DMVs, and different techniques to rectify the problem.

Here are some suggestions to reduce this occurence:

  • Keep Your Transactions Short
  • Keep you locks to the smallest degree of isolation necessary
  • Process the minimum data possible
  • Process Resources in the Same Order
  • Make sure you have Adequate Indexes

There are lots of other resources for dealing with this kind of tuning. Kimberly Tripp, Kalen Delaney, Kevin Kline are a few of the people I remember writing in detail on this topic, and a Google search will return resources from many others as well (if you are one of those others, please forgive my poor grey brain cells for not brining you quickly to mind).

Reader Feedback – Surivor and Three Traits to Fire

I went back and read the article, “Three Types of People to Fire Immediately” three times tonight, thinking I may have missed something based on some reader responses. Frankly, I am more pleased when folks catch my errors or disagree. In this case, I’m not quite sure I understand some of the push back.

IMHO in order for a company to remain viable in the world today they must be innovative. I would include something as simple as a company that performs office cleaning as a serviec that needs innovation. Why? Because there are many companies that perform office cleaning. If the only thing you do that sets you apart is the cost of your service, you simply become a commodity. Your customers will stack you up against your competition to get the very lowest price possible.

Who knows best how to make an office cleaning company stand out from the pack? The people in the trenches know. It is the job of a good leader to bring the innovation to life by encouraging a culture of thought and change.

As I read the article, I didn’t see it as management coming up with the new ideas and dealing with individuals that can’t get on board. I saw it as encouraging the individuals to think about how they can innovate, be willing to fail, and to create company success rather than hold onto the same old way that has worked forever.

The opposite side of innovation are the attitudes that it is too much work, it won’t work, or unwilling to learn something new. Reducing those influences will encourage your team to innovate.

How about a cleaning company that leaves a complimentary jar of lifesavers on your desk for you to share with those who come by? Why not leave a card on a desk after it has been dusted wishing them a good day? Be sure to put trash cans back where you found them so people don’t have to search for them. Air Freshner may be an option…perhaps have check boxes on the card you leave on the desk allowing the person to select a fragrance, or no fragrance at all? Perhaps you offer decorating for holidays? How about special welcome mats branded with your company logo at no cost (a little advertising)?

Enough rant from me…let me share some good comments from others.

I hadn’t read the article "Three Type of People to Fire Immediately" prior to reading your article so I took the opportunity. Having done so, I found the comments made by others more enlightening than the article itself:

Many commentators were derisory about the article, primarily because the article is written from the viewpoint of a senior manager who believes that the only reason his company isn’t successful is because of the people he employs, not that there is any flaw in his business plan, marketing, project management or operations.

The article basically states that problem employees are those that (for one of three reasons) don’t get on board with the manager’s vision:

The Victims could equally be people exploited by the management, rather than (as suggested) people constantly blaming others; The Nonbelievers could equally be people who see a fundamental flaw in the business plan, but whom management fails to consult or from whom it fails to listen to feedback; The Know-it-Alls could equally be people who know their particular department/profession far better than management, but from whom no ideas/feedback is solicited.

All three of these identified groups could be dealt with by a) reducing exploitation, b) listening and c) listening. The fact that the authors only identify firing as a solution for what are, in-essence, management failures indicates that the authors themselves have been unable to deal with these situations in their professional life. So why should we, the readers, give them any credibility?

But what is most disturbing about this article is the presumption that with a can-do attitude any business plan can succeed, no matter how lousey it is. This is pure nonsense.

Seems like the hypothetical employer is altogether too thin-skinned.

Maybe listening to employees who are courageous enough to express different points of view would be a good for the organization.

I’m age 65 and have worked since I was 19, but I’ve met only one manager who had the self-confidence to do this. He was a former military officer and chose to listen to a "naysayer" in his division.

Turns out, this naysayer was right – she blew the whistle on her supervisor, a crook who was stealing from the company. In this case, everyone won, including the company, because the manager listened to his "negative" employee rather than firing her.

Interesting article. I learned a new word: coopitate.

Drop me a note anytime if you have something you want to ask, or something you’d like to share. My email is btaylor@sswug.org.



Featured White Paper(s)
How to Implement an Effective SharePoint Governance Plan
Written by AvePoint

Moving past the "what" and "why" of governance, an even… (read more)