Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gmaruna
Starting Member
3 Posts |
 Posted - 2008-09-23 : 13:47:29
|
| We have a simple insert sproc we have keeps timing out and behaving badly. table has been reindexed/stats. we recompile the sproc, runs fine for a while, then more timeouts. we recompile using "WITH RECOMPILE" and the procedure works "fine" (just slower due to constant recompiles). This just started about a month ago. We reindex/stats yesterday, dumped the proc cache yesterday (DBCC FREEPROCCACHE) and it ran great for about 15 minutes, then timeouts again. Ran fine until while testing, we removed the -WITH RECOMPILE and 30 reuses, then timeout again.Where and what to check to find why this has begun to happen? SQL 2005 64-bit with custom .NET application & other sprocs making calls to sproc in question. Thanks! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-23 : 15:49:30
|
| Can you specify Timeout setting in your Application? ALso donot run 2 Jobs with 1) Rebuild index 2)Update statistics. As Rebuild index also updates stats also. After you Rebuild index, Just Recompile one time to catch new execution plan. DONOT put WITH RECOMPILE in your SP as it will recompile everytime it runs and slows down.You need to trackit down what is causing your stored procedure to slow down/Recompile.1)Interleaving DDL and DML commands2)Are schema changed frequently3)Is it doing Bulk_insert. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
gmaruna
Starting Member
3 Posts |
Posted - 2008-09-23 : 16:45:05
|
| It was the case the the application was slower and SSMS showed no lag at all. (I dont have access to the source code and the timeout for long running queries is 600 seconds on the server). We do run the exact same code into the sproc and find it now lags even in the SSMS (1 second to up to 40 seconds). Application time out earlier. I am getting permission before I can post code.Also no DDL/DML interleave, only select/insert commands. No new schema changes, no bulk insert. The sproc inserts a comment for specific actions taken by the program or input by user. 3 million rows in the destination table.Execution plans don't always show for application in SQL Profiler, they get hung up and do not match what is in SSMS when run side-by-side. |
|
|
|
gmaruna
Starting Member
3 Posts |
Posted - 2008-09-23 : 16:55:16
|
| also, without the WITH RECOMPILE, the procedure will begin to time out and throw errors. At least it runs, even if fractionally slower. |
|
|
|
|
|
|
|
|
SPROC Times Out when allowed to cache