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
cbridgmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
cbridgmanAuthor Commented:
I appreciate your help ste5an, but as I mentioned, I am not a DBA nor a SQL Server expert. What is MAXDOP?
ste5anSenior DeveloperCommented:
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.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

cbridgmanAuthor 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 DeveloperCommented:
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.
cbridgmanAuthor 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.
ste5anSenior DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cbridgmanAuthor Commented:
Thanks again ste5an, I really appreciate your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.