Have to Recreate SP (periodically) To Prevent Timeout Error 2

[JohnBates]

hi everyone,

We have an ASP page that sometimes starts giving a 'Timeout Expired' message.

Thru trial-and-error, I've discovered that by simply dropping and recreating the stored procedure, that resolves the issue and (for a few days) the web page no longer gives this error.

We know that tables needs frequent maintenance (rebuild indexes etc), but why would a stored proc have to be recreated every few days ?

This is SQL Server 2000, IIS 5 and ASP 3/Classic.

Thanks!
John

 

[LFCfan]

It's been a while since I've had much to do with SQL Server, but if memory serves, it may be that the execution plan of the SP becomes "obsolete" after a while, especially if a lot of data has been added/removed/changed in the underlying tables.
So the SP is no longer using the best plan for the needs of the query.
You could use the WITH RECOMPILE option I believe, to get over this problem

_{~LFCfan
Who is relieved Friday has finally arrived}

 

[SQLDenis]

update statistics on the tables

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[JohnBates]

Thanks LFCfan and SQLDenis.

I run dbreindex on these tables and update the statistics every weekend, then I updateusage on the database.

I will add WITH RECOMPILE and try that for awhile. This proc gets called maybe 50 times per day so it may slow the web user down a bit if it has to recompile each time it is executed, but it's worth a try.

I appreciate the tips!
Thanks. John

 

[JohnBates]

..... Today the web page was timing-out again. I recreated the stored proc and it still timed-out.

Then I deleted, recreated the main view and that resolved the problem-the web page renders the data within 6 seconds.

But it's not a permanent fix - in a day or 2 the ASP page will be timing-out again.

I'm thinking of scheduling a delete and recreate of the view and the stored proc - to run each night. I don't have to do that on any other server, so I'm (stubbornly) trying to find a better soultion. I already run table reindexing and update stats on the weekends.

OK I'll stop complaining now
Thanks, John

 

[vbkris]

>>Then I deleted, recreated the main view and that resolved the problem-the web page renders the data within 6 seconds

its not an indexed view right? just wanted to know...

Known is handfull, Unknown is worldfull

 

[JohnBates]

No, the view is not indexed.
Thanks/