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
[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
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.
ASKER
I appreciate your help ste5an, but as I mentioned, I am not a DBA nor a SQL Server expert. What is MAXDOP?
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.
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.
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?
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again ste5an, I really appreciate your help.