Troubleshooting blocking
-
Hi,
I have a blocking scenario that kills the entire server each time it happens. There is a process on the top of the blocking tree, which is in sleeping state and "AWAITING COMMAND". Each time this happens there is the same SP for this process if you run "DBCC INPUTBUFFER".
This SP is pretty complex and I am looking for a way to find out where exactly it gets stuck. "fn_get_sql" does not work in my case as the process is "sleeping".
I am also not sure how to correctly interpret the output for "DBCC INPUTBUFFER". BOL says: "Displays the last statement sent from a client to Microsoft SQL Server". Does that mean that statement (SP) was run successfully and then it died or it died somewhere in the middle while executing the SP?
Microsoft states that for "idle" and "awaiting command" scenarios it waits for some input from the user, but SPs typically do not expect any input.
Any ideas are greatly appreciated.
Thanks.
-
If you’ve got it narrowed down to a stored procedure, as complex as it may be, you’d benefit from knowing what resources the SP has locked at the time of the problem. Regardless of which line of SP code it is, you already know it is the SP that is the problem. The next time you have the problem, run SQLDiag.exe right away. This will give you detailed locking and process information that you can use to drill down to the possible locking in-efficiencies in your SP.
-
Thanks for the SQLDiag hint. Will definetly do. As for the resources, the resource looks like this:
KEY: 13:101575400:1 (e901b18a7966)
So 101575400 is a specific table in my database. My block tree lookes like this:
Spid Blocked Status Command Open tran Wait resource DBCC SRT 67 0 sleeping AWAITING COMMAND 1 sp_get_attachment;1 67 163 67 sleeping CONDITIONAL 1 KEY: 13:101575400:1 (e901b18a7966) sp_resume_action;1 0067:0163 102 163 sleeping SELECT 1 KEY: 13:101575400:1 (e901b18a7966) sp_open_folder;1 0163:0102 105 163 sleeping UPDATE 2 KEY: 13:101575400:1 (e901b18a7966) sp_cancel_action;1 0163:0105 116 163 sleeping SELECT 0 KEY: 13:101575400:1 (e901b18a7966) f2_GetNextAvailableSettlement;1 0163:0116 126 163 sleeping SELECT 1 KEY: 13:101575400:1 (e901b18a7966) sp_open_folder;1 0163:0126 130 163 sleeping SELECT 1 KEY: 13:101575400:1 (e901b18a7966) sp_update_action;1 0163:0130 150 163 sleeping SELECT 1 KEY: 13:101575400:1 (e901b18a7966) sp_open_folder;1 0163:0150 158 163 sleeping SELECT 0 KEY: 13:101575400:1 (e901b18a7966) f2_GetNextAvailableSettlement;1 0163:0158 185 163 sleeping SELECT 1 KEY: 13:101575400:1 (e901b18a7966) sp_update_action;1 0163:0185 190 163 sleeping SELECT 1 KEY: 13:101575400:1 (e901b18a7966) sp_update_action;1 0163:0190 So this is the same page all processes want to get access to. Is there a way to find out what data is in e901b18a7966 page? BTW the SP does not touch the table in the "Wait resource" column. Could it be something wrapped around the SP? Like
"Begin tran
exec sp_getattachment
-- wait for user input
Commit tran"
?
I also noticed that the WaitType for the process which is on the top of the blocking tree is "NETWORKIO". I think this could be waiting while sending output data to the client while client has suddenly disconnected.
-
Setup the sp_blocker_pss80 procedure as described in the KB article,
http://support.microsoft.com/default.aspx?scid=kb;EN-US;271509
--------------------
Colt 45 - the original point and click interface
-
As a best practise it's always better to keep user interactions out of the transaction scope. That way you may be able to keep transactions shorter and smaller. And they're better when they are small
Zubeyir
-
From a quick look, it looks like the sp_get_attachment stored proc is beginning a transaction and not committing or rolling it back. A stored proc should always commit or roll back any transactions it starts.
Take a look at the code, see if anything stands out, or post it here and we'll look at it.
To find the table, run SELECT Object_name(101575400). e901b18a7966 is not a page ID. It's a key in the specified table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I've got the table name, and this SP does not touch the table at all, that is why I thought I could be wraped around into a transaction which first queries the table and then runs sp_getattachment, and then waits for some user response. If the problem is not in the sp_getattachment SP, than I have to find that particular transaction, which may be difficult, as the software was written by a third party company.
-
Run profiler for a while. When you get the blocking, check the spid and refer back to profiler to find out what the spid did before calling the get_attachment.
Possible (frightening) possibility is that n earlier proc began a tran and didn't commit it, then all procs after will be within the same transaction.
Suggested profiler events:
RPC started (under stored proc)
SQL Batch started (under T-SQL)
SQLTransaction (under Transactions)
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply