August 6, 2019 at 12:11 am
This has cropped up on one of the servers I maintain. The end user will report a problem, I'll use a query to check running queries which will show a fair number of blocked queries. The head blocker however, does not show a query. In doing further (and better) research on this today when it happened, the head blockers are executing a stored procedure that updates a table.
The table itself has no triggers and minimal indexing (a clustered index on a numeric(19,0) column, named OID) The clustered index key is being used in the where clause of the update, which does have one SET that I just realized might be a bit odd. It's trying to set the OID equal to itself.
This is a vendor application, so I can't make changes to the database without losing support. This issue also does not happen in our QA environment, although there is virtually no usage of QA (it's more for patch testing for this application.) I've also never heard a peep from the users after I kill the offending sessions causing the blocking (and yes, sometimes I'll have two or three blocking sessions with a stack of sessions waiting on them.)
The application we're having the problems with is Tripwire Enterprise.
August 6, 2019 at 11:47 am
I'm not familiar with the app, so no help there.
When you say you don't see it in your QA environment, my inclination would be to first ask what differences are there between the two environments. Not simply the physical hardware, but the server settings such as MaxDop and Cost Threshold, database settings and all the rest. It's possible a simple change could help. However, I'd also ask, what does the load on your QA system look like compared to production. How likely is it that you're going to get a blocking scenario? Do you have a simulated load that would produce a similar set of behaviors? Without that, you might not see this in QA.
The query itself, have you looked at the execution plan to see what it's doing and how? I get that changing the database violates the rules, but, in your situation, I've occasionally changed the database. I just kept scripts on the side so I can revert it as needed for audits, support calls, what have you.
Check all the settings and look at the execution plan. That ought to get you some more data to help with finding a solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 6, 2019 at 5:51 pm
I had a similar issue - which turned out to be an issue in how the application was managing the connection on the web server. The transaction was open, retaining the locks but was no longer processing. The connection on the web server was hung on an application pool that was attempting to recycle.
The application pool hit a threshold and tried to recycle - causing new connection requests to be directed to a new process. The existing connections remained on the old process which could not be closed because it had an open connection to the database - which could not be completed because the web server process was being shut down...
The vendor had to track down the offending code and change the parameters on when and how the application pools were cycled.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 6, 2019 at 8:03 pm
In doing further (and better) research on this today when it happened, the head blockers are executing a stored procedure that updates a table.
If it's a joined update, please post the code. I might know what the problem is if it is a joined update.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2019 at 12:52 am
jasona.work wrote:In doing further (and better) research on this today when it happened, the head blockers are executing a stored procedure that updates a table.
If it's a joined update, please post the code. I might know what the problem is if it is a joined update.
I checked, the updates don't have joins in them, just plain, old "update table set @x = x where @y = y"
Right now, we've got it kicked over to the vendor and I'm keeping an eye out for any more of the blocking.
Grant: Yeah, the workload difference between QA and prod is, significant, shall we say. I did check, and MaxDOP and Cost Threshold are set the same between the two. Next up, setting up to capture, preferably, the query plan for the update.
August 7, 2019 at 11:11 am
The differences in load is likely the differences in behavior (not necessarily, but, you know how it goes). Those settings were just a couple of examples. I'd examine both sets of server & database settings. It's still possible it's something along those lines (not the most likely though). Have you looked to see if the execution plan is in cache? You might not need to recapture it. It may just be waiting for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply