We help IT Professionals succeed at work.

SQL Server Deadlocked on Communication Buffer Resources

932 Views
Last Modified: 2019-02-08
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

ste5anSenior Developer
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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.

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions