SQL Server Deadlocked on Communication Buffer Resources

cbridgman
cbridgman used Ask the Experts™
on
A process that I am running against a SQL Server 2008 R2 DB throws the following message

[sqlits1]Transaction (Process ID 55) was deadlocked on communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

WE have two databases on the sqlits1 server (production and test) and the same message is thrown on both, implying that the problem is not with the individual databases but instead with the SQL Server instance itself.

The process that is throwing this message ran without issue a few months back and to my knowledge, no changes have been made to the server. Data has been added to both databases but no server changes of any kind that I'm aware of.

Any ideas what might cause this error? I'm not even sure how to begin to troubleshoot.

Thanks in advance for your help.

P.S. I am not a DBA nor a SQL Server expert. Just letting you know so that if I ask follow up questions for clarification, you will know why
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
It means that a in parallel executed query has dead-locked internally. To test whether this is the case, run your query with the MAXDOP 1 hint to force sequential execution. When this is the case, then compare the time the parallel query takes running normally in comparison to the time it takes running it sequential. If the difference is small enough than using MAXDOP 1 is already your solution. When the time gap is to large, then try running the query with OPTION RECOMPILE to test whether an outdate query plan is causing the lock. When this does not solve the deadlock issue, then look into the server MAXDOP settings and thresholds and set higher values for parallelized queries.

Author

Commented:
I appreciate your help ste5an, but as I mentioned, I am not a DBA nor a SQL Server expert. What is MAXDOP?
ste5anSenior Developer

Commented:
Per default SQL Server is configured to parallelize queries when certain circumstances are met (number of cores, expected faster exection time). One of those parameter is the degree of parallelism (DOP). MAXDOP is the maximum number of threads used to execute such a parallelized query. Thus MAXDOP 1 restricts this value to one, which is equivalent to sequential execution. See hints for MAXDOP 1 as query hint.

Just a comment: "[..]  I am not a DBA nor a SQL Server expert [..]"
I would seriously consider hiring a DBA, when your system has a business value. Cause this error and still running SQL Server 2008 R2, which lifecycle ends in September, indicates some kind of technical debt.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
ste5an, i agree with you. We need a DBA but until then, I'd like to determine the cause of the problem and see what can be done to fix it. I just took a backup of the database that I ran the process on and restored it to a different server. The process ran without failure on this server. So, that confirms that the problem is at the server level and not the db level. I looked at the properties of both servers and the MAXDOP setting on each is 0. Any idea what else I might look for to figure out what the cause is?
ste5anSenior Developer

Commented:
Well, I already wrote it:

You need to test your actual query first with the MAXDOP 1 query hint and then with the OPTION (RECOMPILE) hint. Then you need to compare the results.
Depending on the findings one of the above measures on the actual query is already the solution.
Otherwise you need to go further and increase the threshold for MAXDOP on the server level. But this can have sever impact in other places. But this requires thorough testing.

In short: there is no easy way here to solve that. I strongly recommend talking to your boss. Cause when the database has business value, then right now it is the correct time to hire an DBA. Cause just add the time already passed on that question.. this is already the equivalent of 4 to 8 hours of an DBA already looking into it.

Author

Commented:
thanks very much ste5an. I will definitely float that out there. But in the meantime, another question:

Would it be dangerous, as a test, to simply change the MAXDOP value from 0 to 1 and see what happens? We can always reset to 0 if problems occur after setting it to 1.

Let me know.
Senior Developer
Commented:
Would it be dangerous [..]
Maybe.. it depends on what systems depend on quick queries. Cause setting MAXDOP 1 for the entire server can slow down important queries which depend on parallel execution. Thus I said: you can do it, but you need thorough testing of the entire system to rule out that this fix for the failing query may not cripple performance at some other place.

Author

Commented:
Thanks again ste5an, I really appreciate your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial