SQL Server Performance Forum – Threads Archive

Stored Procedure problems

We have a VB app. that is sending parameters to a Stored Procedure in SQL Server 7.0. The problem we can’t figure out is the stored procedure keeps timing out and thus not completing it’s task. The SP has worked for 4 years now and has only started messing up in the last month or two. We’ve tried to set the timeout to larger and larger values, but this doesn’t help. We’re not even close to using our SQL Server system to it’s full potential. But yet it doesn’t seem to have time to run a simple Stored Procedure. We’ve noticed that the CPU is running at around 40-50%. Is that normal? We just don’t know where to start. We were running a lot of DB backups that was using a lot of system resources, but have since canceled them and we’re still having the same problems with this SP not completing it’s job. HELP!!

Look at the tables it’s updating. Run UPDATE STATISTICS tablename for the tables in the query. Run sp_recompile on the query (although you shouldn’t have to). If that doesn’t work (these are quick fixes that sometimes might work), then actually look at what’s going on. In the execution plan, do you see any table scans, or do the proper tables have indexes? If you need to, create indexes. Also, look at the statement in Profiler. Where is your slow time coming from? Sometimes a 1 row difference can make a big difference in how SQL Server creates the execution plan and runs a query. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

In addition to what Derrick referred, review
http://www.sql-server-performance.com/stored_procedures.asp
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>