3

I have a simple stored procedure in T-SQL that is instant when run from SQL Server Management Studio, and has a simple execution plan. It's used in a C# web front-end, where it is usually quick, but occasionally seems to get itself into a state where it sits there and times-out. It then does this consistently from any web-server. The only way to fix it that I’ve found is to drop and recreate it. It only happens with a single stored procedure, out of a couple of hundred similar procedures that are used in the application.

I’m looking for an answer that’s better than making a service to test it every n minutes and dropping and recreating on timeout.

Mathew Thompson
  • 55,877
  • 15
  • 127
  • 148
Paul
  • 16,285
  • 13
  • 41
  • 52

6 Answers6

5

As pointed out by other responses, the reasons could be many, varying from execution plan, to the actual SP code, to anything else. However, in my past experience, I faced a similar problem due to 'parameter sniffing'. Google for it and take a look, it might help. Basically, you should use local variables in your SP instead of the parameters passed in.

legendofawesomeness
  • 2,901
  • 2
  • 19
  • 32
  • This is a really wierd experience but it does occur as I have experienced this before but this can cut some serious time off of your stored procedure runtime. – Bill Blankenship Nov 06 '12 at 21:05
3

Not sure if my situation is too uncommon to be useful to others (It involved use of table variables inside the stored proc). But here is the story anyways.

I was working on an issue where a stored proc would take 10 seconds in most cases, but 3-4 minutes every now and then. After a little digging around, I found a pattern in the issue :
This being a stored proc that takes in a start date and and an end date, if I ran this for a year's worth of data (which is what people normally do), it ran in 10 sec. However when the query plan cache was cleared out, and if someone ran it for a day (uncommon use case), all further calls for a 1-year range would take 3-4 minutes, until I did a DBCC FREEPROCCACHE

The following 2 things were what fixed the problem

  1. My first suspect was Parameter sniffing. Fixed it immediately using the local variable approach This, however, improved performance only by a small percentage (<10%).
  2. In a clutching-at-straws approach, I changed the table variables that the original developer had used in this stored proc, to temp tables. This was what fixed the issue finally. Now that I know that this was the problem, I am doing some reading online, and have come across a few links such as http://www.sqlbadpractices.com/using-table-variable-for-large-table-vs-temporary-table/ which seem to correspond with the issue I am seeing.

Happy coding!!

mattr
  • 181
  • 2
  • 10
2

It's hard to say for sure without seeing SP code.
Some suggestions.
SQL server by default reuses execution plan for stored procedure. The plan is generated upon the first execution. That may cause a problem. For example, for the first time you provide input with very high selectivity, and SQL Server generates the plan keeping that in mind. Next time you pass low selectivity input, but SP reuses the old plan, causing very slow execution.
Having different execution paths in SP causes the same problem.
Try creating this procedure WITH RECOMPILE option to prevent caching.
Hope that helps.

a1ex07
  • 36,826
  • 12
  • 90
  • 103
2

Run SQL Profiler and execute it from the web site until it happens again. When it pauses / times out check to see what is happening on the SQL server itself.

There are lots of possibilities here depending on what the s'proc actually does. For example, if it is inserting records then you may have issues where the database server needs to expand the database and/or log file size to accept new data. If it's happening on the log file and you have slow drives or are nearing the max of your drive space then it could timeout.

If it's a select, then those tables might be locked for a period of time due to other inserts happening... Or it might be reusing a bad execution plan.

The drop / recreate dance is may only be delaying the execution to the point that the SQL server can catch up or it might be causing a recompile.

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • It's a select, and there is nothing locking at the time (according to sp_who2). Based on all the responses I'm assuming it's a parameter sniffing problem, so I've put OPTION (RECOMPILE) at the end to see whether it fixes it. – Paul Jul 31 '11 at 23:32
1

My original thought was that it was an index but on further reflection, I don't think that dropping and recreating the stored prod would help.

It most probably your cached execution plan that is causing this.

Try using DBCC FREEPROCCACHE to clean your cache the next time this happens. Read more here http://msdn.microsoft.com/en-us/library/ms174283.aspx

Even this is a reactive step - it does not really solve the issue.

I suggest you execute the procedure in SSMS and check out the actual Execution Plan and figure out what is causing the delay. (in the Menu, go to [View] and then [Include Actual Execution Plan])

Raj More
  • 47,048
  • 33
  • 131
  • 198
0

Let me just suggest that this might be unrelated to the procedure itself, but to the actual operation you are trying to do on the database.

I'm no MS SQL expert, but I would'n be surprised that it behaves similarly to Oracle when two concurrent transactions try to delete the same row: the transaction that first reaches the deletion locks the row and the second transaction is then blocked until the first one either commits or rolls back. If that was attempted from your procedure it might appear as "stuck" (until the "locking" transaction is finished).

Do you have any long-running transactions that might lock rows that your procedure is accessing?

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167