Time Out Errors in Stored Procedures

Question

Time Out Errors in Stored Procedures

mistux

SSC Rookie

Points: 41
More actions
November 9, 2005 at 12:50 pm

#69902

I am using MS SQL 2000 and MS Access 2000 (ADP not MDB) and all of a sudden after weeks of using the application I am starting to get Timeout Errors in stored procedures that previously worked just fine. (I am not using ADO code to run them, rather I am using CurrentConenction.execute() to run them.)
When I go run them in Query Analizer it also gives me a timeout error, unless I let it run for 7.20 minutes.
I went back to an "old" copy of the data and sp and ran one of them and it ran in less then a second, when I run the same sp on the current system I stopped it after 5 minutes. The data all looks good and about the same number of records. I did look at the execution plan for both and they are vastly different. Is there a way to tell SQL Server to "redo the plan?"
How can a sp work one day and the next not work? The one I am working with was used every singe day, then it stopped working.
Another one stoped working then the next day it worked!

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

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

Perry Harts Ten Centuries Points: 1271 More actions · Answer 1

Sorry, I don't have an answer. You would have a better chance of getting an answer from a TSQL genuis by posting your question under TSQL. Remove the references to Access because it does not look like Access is the issue.

DEV LB Newbie Points: 5 More actions · Answer 2

Be sure that all of your indexes are installed, and if SQL Server is not using the indexes properly, you may need to use an index hint. i.e...

select * from MyTable with (INDEX (MyTable_IDX5)) where ......

ernesth SSC Enthusiast Points: 113 More actions · Answer 3

Try using sp_lock and sp_lock2 stored procedures while executing the slow procs,

seems to be that your database is locking

Regards

sholliday SSCrazy Points: 2100 More actions · Answer 4

This is a guess.

But, make sure you don't have any triggers on those tables (are you the only developer?)

Triggers will screw you.

Another area of error, is using cursors inside your procedure. You need to learn about Bulk Updates.

The reason is that .. when using a cursor, everytime you run a UPDATE or INSERT statement, the RDBMS (sql server) will update the indexes.

When you BULK insert or update, this will postpone index updates, until after.

You'll have to do some digging here, but if your procedure has a CURSOR in it, then this may be a trouble area. 99.9% of the time, you can get around using a CURSOR.

If you go here

http://spaces.msn.com/members/sholliday/

and look for

CREATE Procedure dbo.uspExcelImportExample ( @xmlDoc text )

Then, you can see how I am putting the info in a @variableTable first, then running the Bulk INSERT or UPDATE statement.

..

Just take this advice with a grain of salt.

The dblock is a more likely answer.

But triggers or adding a seemingly innocent index on a table can hurt you, especially if you've done some CURSOR coding.

mistux SSC Rookie Points: 41 More actions · Answer 5

Well, I rebooted the server and then ran my sp and it worked in under a second. Go figure?!

ChrisP-226343 Newbie Points: 4 More actions · Answer 6

Had a similarly problem, after some time queries and store procedures began to run slower and cpu load on the server increased.

After adding [exec sp_updatestats;] to the nightly maintains job, everything has been running smoothly since.