Random timeouts with SPs

[brandonb]

I have a few stored procedures here at work where from time to time they start to time out randomly. The application is using a hard coded db username/password and will start to time out after a certain amount of time (usually days or weeks.) If I log into SQL manager with my own domain username, it always returns in a second calling the same stored procedure. It's like as if the stored procedure is going fubar for the certain login.

If I recompile the stored procedure or just rerun it, the application will respond within a second like the domain username. Is there something I can do to fix this? Or has someone run into this problem before?

 

[imported_Dhaval00]

This has to do with maintenance plans on the SQL Server engine. Specifically, if statistics haven't been updated for some time or indexes and fill factors are out of whack, this will happen. Basically, the engine has SPROCs for a reason - they are compiled entities and will run faster than ad-hoc queries. However, these compilations are still based on statistics which are driven by the Optimizer.

If your issue is related to the explanation above, and assuming you can't rebuild indexes and update statistics (due to load reasons), you can recompile your SPROCs using the query hint: OPTION (RECOMPILE). Look up TABLE HINT and QUERY HINT on MSDN for details. Without the RECOMPILE switch, the engine caches the execution plan and reuses it every time the SPROC runs. Needless to say, you'll be giving up a major advantage of using SPROCs if you go down this path (assuming it works). Nonetheless, you can use HINTs to pin down performance issues - if the HINT works, you can be quite certain that there is something wrong with your indexes and/or your statistics.

 

[brandonb]

Thanks Dhaval. I'll take a look at the statistics. I did update the indexes recently, and changed the stored proc around a bit a few days ago to better use the indexes I created, so it probably had something to do with it.

 

[imported_Dhaval00]

Please share your findings. I run into similar issues from time to time, and wouldn't mind expanding my knowledge base using other people's experiences.

 

[brandonb]

Hmmm. My stats were updated when I redid the indexes it says. But I just made a script to free proc cache, rebuild indexes, rebuild stats. Ran one of my reports that is notorious for time out.

This is how it looks in profiler when I run it through my app (vb.net SQL connection):

Command:
EXEC QueryBalanceConversion 2,null,'4/1/2009','4/30/2009'

Login:
code

CPU:
25313

Reads:
1383730

Writes:
2

Duration:
30102 (timeout)

And this is how it looks in profiler when I run it through SQL managerment studio

Command:
EXEC QueryBalanceConversion 2,null,'4/1/2009','4/30/2009'

Login:
brandonb

CPU:
672

Reads:
16335

Writes:
6

Duration:
1177

----

Makes me scratch my head. There has to be something with the VB.net app the way its got the connection open or something.

 

[JACKDRUID]

nm..

 

[imported_Dhaval00]

Can you connect to Management Studio using the "code" login and then run the SPROC? See if you get different results. As a variation, if it doesn't timeout, run your VB.NET app simultaneously and execute the command from within Management Studio. Finally, also look at the Activity Monitor and see how many "waiting" connections "code" has.

 

[KB]

These types of issues are some of the hardest things to debug. Does the timeout behavior increase with the number of users using the system? I have seen similar behavior in SP code that causes deadlocks. Deadlocks occur when two processes attempt a lock on the same data, but neither can continue until the other process gives up their lock, so both queries timeout.

This article discusses deadlock detection.
http://support.microsoft.com/kb/832524

One way I reduce deadlocks is by adding WITH (NOLOCK) to certain queries, particularly to queries used in reporting. This has a side effect though, the query could miss data that is not yet committed.

i.e SELECT * FROM Table WITH (NOLOCK) INNER JOIN Table2 WITH (NOLOCK) WHERE ....


Good luck!

 

[nakedfrog]

We have a scheduled job that runs weekly and recompiles all the stored procedures.
I'm not sure I'd rule out an application issue in your case, though.

 

[Kntx]

Originally posted by: KB
These types of issues are some of the hardest things to debug. Does the timeout behavior increase with the number of users using the system? I have seen similar behavior in SP code that causes deadlocks. Deadlocks occur when two processes attempt a lock on the same data, but neither can continue until the other process gives up their lock, so both queries timeout.

This article discusses deadlock detection.
http://support.microsoft.com/kb/832524

One way I reduce deadlocks is by adding WITH (NOLOCK) to certain queries, particularly to queries used in reporting. This has a side effect though, the query could miss data that is not yet committed.

i.e SELECT * FROM Table WITH (NOLOCK) INNER JOIN Table2 WITH (NOLOCK) WHERE ....


Good luck!

WITH (NOLOCK) solves just about every problem you will ever have with sql server.

 

[brandonb]

I will write with the NOLOCK. I usually put them on reports but I think with the one I listed above I did not. I wonder if there is something with the data adapter in the code with the rows it was trying to lock a bunch of rows on the db side causing the timeout. I'll try with NOLOCk and look over properties in the sql connection/command/data adapter that might cause the problem. I haven't had much time to look this over, but hopefully I'll get some time tomorrow. Thanks for the responses!

 

[nakedfrog]

Originally posted by: brandonb
I will write with the NOLOCK. I usually put them on reports but I think with the one I listed above I did not. I wonder if there is something with the data adapter in the code with the rows it was trying to lock a bunch of rows on the db side causing the timeout. I'll try with NOLOCk and look over properties in the sql connection/command/data adapter that might cause the problem. I haven't had much time to look this over, but hopefully I'll get some time tomorrow. Thanks for the responses!

What kind of error logging does the application have? How are the connection/command timeouts set?

 

[Kntx]

From your code do you call your SP like this:

SqlCommand C = new SqlCommand("EXEC procSomeProc", TheConnection);
C.ExecuteNonQuery();

Or like this:

SqlCommand C = new SqlCommand("procSomeProc", TheConnection);
C.CommandType = CommandType.StoredProcedure;
C.ExecuteNonQuery();

If you do it the first way, switch to the second. The first way will not use the cached query plan.

 

[brandonb]

Our machines have been having issues, so they've been rebooted on a daily basis so I'm not running into many issues lately.

Dim objl_DA As SqlClient.SqlDataAdapter
Dim objl_DS As New DataSet

' Get results
objl_DA = New SqlClient.SqlDataAdapter(strl_SQL, objg_CONNECTION.Connection)
objl_DA.Fill(objl_DS)

strl_SQL is a statement like "EXEC StoredProc"
I'm looking over the DataAdapter to see if there's more /better properties to use.

 

[Kntx]

DataAdapter has a SelectCommand property. You can do it like this:

DataAdapter A = new DataAdapter("procSomeProc", aSqlConnection);
A.SelectCommand.CommandType = CommandType.StoredProcedure;

 

[brandonb]

Thanks, I will give that a try. The only problem is that for reports, I have a base class which does all DB work, and an abstract class which does custom formatting on grids, generates the SQL statement with parameters, etc. I will have to change that around so instead of having the abstract create a SQL string, it has to create the sqlCommand object. It will require me to do some work, but its needed.

 

[brandonb]

Hey guys. I realize this is about a month old. Did some research.

I think its due to this:

http://sqladvice.com/blogs/gst...edure-Performance.aspx

SET ARITHABORT is ON by default in SQL Server Management Studio and off in ADO... So, I think I have a lead. I thought I'd report on it. I will test it shortly. But I'm guessing it's going to be it. There is alot of pages regarding similar problems as of mine and this option.