Please Help!

Question

Please Help!

lle-439036

SSC Enthusiast

Points: 114
More actions
May 10, 2007 at 6:56 pm

#173596

I ran into this weird problem with SQL 2005. I have a SPROC that will take only 1 sec to run in Management Studio and it runs fine through ASPX. However, once in a while I will get a SQL Time Out error for the SPROC on the ASPX, but when I run it in Management Studio it still only takes 1 sec. To fix the SQL Time Out issue, I dropped and recreated the SPROC and the problem goes away.
Does anyone ever have or had the same problem?
TIA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Most likely that a new set of parameters was passed to the stored proc and the cached plan was not optimal for those parameters.

Check out parameter sniffing on this site for more information!

lle-439036 SSC Enthusiast Points: 114 More actions · Answer 2

Thanks for the reply. Any idea how I can avoid this from happening again? It had happened more than once.

Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Search parameter sniffing on this site. There must be 100 threads talking about this. If you still have questions, I'll gladly adress them after you gave this a try a your own.

lle-439036 SSC Enthusiast Points: 114 More actions · Answer 4

I found this great post. I think this is what's happening. Just want to share. Thanks again.

I had the same problem. Seems like the SQL execution plan was corrupted for the stored procedure. I created a new SP and and copied the code to the new SP from the old and ran the application and the data was returned in no time. This proves that this was a SQL server issue and probably the execution plan for the SP was not efficient when it is run from ADO.NET. The reason that the SP worked from the query analyser and not the application using ADO.NET is QA directly executes the SP on the server but ADO.NET internally executes the sp by calling sp_executesql. The dropping and recreating the SP from the database should be a quick fix for the future. It may be worth while to find out WHY this condition happens after a while on the database.

Here are some information from the research I did on this for your reference:

1) “SqlCommand.ExecuteReader executes all commands in the context of the sp_executesql stored procedure.” (http://msdn2.microsoft.com/en-us/library/aa720629(VS.71).aspx)

2) “Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table. You will notice this problem quite often when you are working with temporary tables in SQL Server 7.0 as SQL Server will determine that after 6 modifications to a temporary table any stored procedure referencing that table will need to be recompiled.”

(http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp)

3) “You may have heard about a system stored procedure called sp_executesql. It lets you evaluate dynamic SQL, but it happens to also cache its execution plan.”

(http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/controlling-stored-procedure-caching-with-dyanmic-sql.aspx)

4) “The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is always recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure, because in this case the wrong execution plan will not be used. ”

(http://www.databasejournal.com/features/mssql/article.php/1565961)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

There's much more to parameter sniffing than that.

This can also be related to your problem.

http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp

Try making a copy of the input parameters to local variables and then using those variables in the query plan. That should clear out the problem without recompiling everything. But as I said, there's still a lot more to it than just that. I'd strongly suggest you research this a little more to learn more on the subject.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

Timeouts frequently have nothing to do with duration.... sometimes if tables are locked for update by a long running explicit transaction, you can get a timeout almost immediately.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)