Debugging SQL Server Stored Procedures

[Kntx]

I have an stored proc running on sql server that hangs now and then. I havn't been able to find any common thread to give me a clue of why it hangs. The data could be the exact same and it will work one time and not another. There don't seem to be any deadlocks either.

The gist of the stored proc goes like this...

begin transaction
delete some records from table 1
select insert some records from table 2 to table 1 (replacing deleted old data with new data)
delete records from table 2
Commit

both table 1 and table 2 have a self referencing FK. I suspect this has something to do with it, but have no way to prove it.

So what I'm asking, are there some good techniques for debugging hanging stored procs when the problem seems intermittent?

 

[KLin]

Check for blocks in the activity monitor while the SP is running would be the first thing to try. You can debug and step through SP's using Visual Studio.

 

[WannaFly]

What type of SQL? MSSQL? Also, what do you mean by "hangs" - is it preventing other queries from running (deadlock), or just running forever and not finishing?

I typically debug procedures by putting print statements in it and running it in query analyzer, i.e.

print 'being transaction'
<begin trans>
print 'deleting records'
delete from <blah>
print 'moving records'

etc.

 

[Kntx]

It's not being blocked. It just sits there refusing to finish. When it finally times out it seems as though it has completed sucessfully.

I think I will add those print statements. Only problem is that the issue only happens on a production server. Cannot replicate in a test environment. I'll have to add them all secretly or endure yet another talking to by the change management crew.

 

[Snapster]

Have a look with sql profiler and probably also log to file the sql you are executing so you might spot the exact command it's sticking on or at least the row of data.

 

[imported_Dhaval00]

I have had similar issues with debugging SQL CLR SPROCs in the past. Simplest reason was I wasn't on the local instance of SQL Server running as administrator. This is a widely documented issue, so that's the first thing I would check: are you on a local instance of SQL Server, and are you running Visual Studio as admin?

 

[KB]

Are there any other transactions occurring during this time? Use SQL Profiler to determine what is happening at that time. Why are you certain there are no locks? The symptoms sound like a deadlock. Whenever I run into a deadlock I start sprinkling the WITH (NOLOCK) keyword in my SELECT queries that run often. This results in improved performance and usually gets around the lock.

i.e. select insert some records from table 2 WITH (NOLOCK) to table 1

http://www.sql-server-performa...nolock_rowlock_p1.aspx

 

[Kntx]

Hey guys, sorry to have abandoned my own topic but I had to leave this one for a while as other stuff became more important.

Anyway, quarter end is rapidly approaching and before I get assailed by 8 million angry accountants I need to figure this out!

What I've found is that

1) It is not deadlocking
2) There are no blocking processes

It is just taking forever to perform a delete. If left alone for 10 minutes or so it will execute. I can't figure out for the life of me why the delete takes so long.

The one thing that seems suspicious is that the table is a self referencing tree stucture holding multiple trees with all the relationships and constraints that go with it. A record looks like this:

TreeID NodeID ParentNodeID Field_1 ... Field_N

I call a delete like so...

DELETE FROM Tree WHERE TreeID = @ID

Is there anything wrong with doing this?

 

[Markbnj]

If the delete violated a constraint then the procedure should throw an exception.

 

[Kntx]

Which it doesn't. It executes successfully, but takes an extrordinarily long time.

The PK of the table is TreeID + NodeID, and there is a clustered index of TreeID + NodeID. Perhaps if I just created an index on TreeID it would help out.

 

[Kntx]

Adding an index to TreeID seems to have done it. Though I am suspicious as this problem has been intermittant.

 

[MonkeyK]

Are any other queries running against the database at the same time? Especially, say, a cursor on the table being deleted from?

Also, is the table very large (>100K TreeIDs)? If not, another index is not likely to help much.

 

[Kntx]

Nah it's not too large.

108 trees.

~250000 records.

 

[KLin]

Does the table cascade deletes to other tables? Is there a delete trigger that writes to an audit table?

 

[Kntx]

No and No

 

[imported_Dhaval00]

Originally posted by: Dhaval00
I have had similar issues with debugging SQL CLR SPROCs in the past. Simplest reason was I wasn't on the local instance of SQL Server running as administrator. This is a widely documented issue, so that's the first thing I would check: are you on a local instance of SQL Server, and are you running Visual Studio as admin?

WTF was I thinking? Freaking brain farts.