Avatar of cbridgman
cbridgman
Flag for United States of America asked on

SQL Server Deadlocked on Communication Buffer Resources

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
DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
cbridgman

8/22/2022 - Mon
ste5an

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.
cbridgman

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

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
cbridgman

ASKER
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?
ste5an

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.
cbridgman

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
cbridgman

ASKER
Thanks again ste5an, I really appreciate your help.