Damian Gardner
asked on
Continuing SQL deadlocks
Hello. We are experiencing an ongoing issue for about 10 days now, where we are seeing the same query show up in the activity monitor, which seems to be the culprit query for the deadlock issue, and our application freezing up. When we kill this query, the system resumes. We are on SQL Server 2008 R2, running on a Windows Server 2008 platform, and the application is called "RFSmart", which is a warehouse mgmt system. Here is the query in question:
@P1 int,@P2 nvarchar(256))SELECT TOP 1 T1.SERVERID,T1.ISDEFAULTRE
We are noticing another common error within the RFSmart application itself, and are not sure if this helpful or not to know. but here's what we see in there, as well (see attachment).
So - we're looking for anything we can try to check, in order to troubleshoot this.
Thanks for your help.
2016-05-16_1128.png
@P1 int,@P2 nvarchar(256))SELECT TOP 1 T1.SERVERID,T1.ISDEFAULTRE
We are noticing another common error within the RFSmart application itself, and are not sure if this helpful or not to know. but here's what we see in there, as well (see attachment).
So - we're looking for anything we can try to check, in order to troubleshoot this.
Thanks for your help.
2016-05-16_1128.png
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ORINETWORKS - thanks for your input. We did initially go to the RFSMart vendor, but they're indicating it's not their tables that are causing it, even though their app is affected. On updates, we are very cautious with applying them to the SQL server, and have not updated it for 2 months now (it's probably time we do so again, however). but its not updates or patches. We have in fact been reindexing everything we can think of, that are involved, as well.
ASKER
one other thing - this table that we see in the query....it only has TWO records in it. its a table that is used to help determine which application server is used in the logic. So we're thinking that this query is maybe misleading us, and that its not the true culprit.
If you run a
EXEC sp_who2 'active'
when this happens do you see which is the culprit SID and what are the blocked processes?
If that query really causes the locks you could use the (nolock) hint rather than kill it:
SELECT TOP 1
...
FROM SRSSERVERS T1 (nolock)
WHERE ((ISDEFAULTREPORTLIBRARYSE
EXEC sp_who2 'active'
when this happens do you see which is the culprit SID and what are the blocked processes?
If that query really causes the locks you could use the (nolock) hint rather than kill it:
SELECT TOP 1
...
FROM SRSSERVERS T1 (nolock)
WHERE ((ISDEFAULTREPORTLIBRARYSE
ASKER
ok - that's an interesting thought.
To have more details from one shot you should download and install Adam Mechanic's sp_whoisactive procedure:
http://sqlblog.com/files/folders/beta/entry42453.aspx
http://sqlblog.com/files/folders/beta/entry42453.aspx
ASKER
ok - we'll take a look at that
we are seeing the same query show up in the activity monitor, which seems to be the culprit query for the deadlock issue, and our application freezing up. When we kill this query, the system resumes.So this relates to a Blocking Process issue and not a Deadlock issue. This is important because they are different issues and have different ways to solve it.
We did initially go to the RFSMart vendor, but they're indicating it's not their tables that are causing it, even though their app is affected.The database has tables that doesn't belong to the vendor? If no then they can't say that.
On updates, we are very cautious with applying them to the SQL server, and have not updated it for 2 months now (it's probably time we do so again, however).If you were cautious then you should reindex the database in a regular basis (at least once by week). Not doing so you're being the opposite of cautious.
ASKER
Vitor - than you for your response. You are right - it is more of a "blocking" issue, although we did see a "deadlock" error show up on one of the computers in our warehouse, when we killing a blocking process. On the RFSmart vendor saying it's not their tables - what they mean is their application works in cooperation with another Microsoft ERP application called Dynamics AX, and they are indicating it is one of those tables that might be the culprit. But it is THEIR application that the users are experiecnign the locking issue in - the Microsoft AX users do not. And as far as being cautious with the updates - the more accurate word is scared & reluctant to apply updates - because 50% of the time, we run into trouble after applying them. We do, however, index our tables every weekend, and are considering doing them even more frequently.
This morning, the locking problem has not surfaced, which is good. We have rebooted everything and reindexed the tables, over the weekend.
This morning, the locking problem has not surfaced, which is good. We have rebooted everything and reindexed the tables, over the weekend.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
is it possible to get the execution plan of the query when it isnt working properly then compare that against an execution plan from when it is working properly?
my guess is that if rebuilding the index fixes it, then maybe at some point sql server thinks a less optimal plan would be more efficient for it, although it may take noticeably longer to execute.
also, and this is a random shot in the dark, is there a slight chance that autogrowth of the database files is on and causing a delay as it grows the files, therby blocking the query until it completes?
my guess is that if rebuilding the index fixes it, then maybe at some point sql server thinks a less optimal plan would be more efficient for it, although it may take noticeably longer to execute.
also, and this is a random shot in the dark, is there a slight chance that autogrowth of the database files is on and causing a delay as it grows the files, therby blocking the query until it completes?
ASKER
sorry gentlemen for the delay. The problem seems to have gone away after doing several things. We reindexed our tables (also setting them to reindex daily, from weekly). we also rebooted several servers. We are not sure what did it, but we're cautious monitoring for the problem to resurface. I will close this out on Tuesday if nothing more happens.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This issue seems to have gone away, so I'm going to close this thread now. thanks to all for your help. the last comment was chosen as "best" because it forced me to choose one. thanks again.
if this is a data/database problem i think it could suddenly be a problem if the indexes on the table are out of date or cant keep up with updates if it is a high activity database. you can try rebuiding the index andgetting the execution plan for that query to see whats taking so lonf and see any additional indexes sql server recommends