execution of stored procedure has become extremely slow!!

Hello,

i am not sure if i am posting this problem in the right forum. please forgive my ignorance, if indeed i am doing so.
i am having a bizarre problem with the execution of a stored procedure on SQL server. up to now, the execution time was taking maximum ten minutes and then suddenly since a few days ago, it takes more than 40 minutes, no exceptions!!
i have tried to diagnose this problem with some tools that are provided out there such as executing a stored procedure called "proc track_waitstats" from which i learned that the “max degree of parallelism”. needed to be set (i set it to 3, it used to be zero. there are 4 processors). after rerunning the diagnosis, my SQL server seemed very happy with wait times. zero for almost everything.
but my stored procedure is still running as slow.
what is going on? how come it was running so much faster before and now it just doesn't?! (the data volume to process has not changed. i am using a test database with static date. so there is no increase in the load of data to be processed by the stored procedure)

any help would be greatly appreciated
First, try posting this in the TSQL forum. You will probably get more answers.

Second, what is your SP doing? Is it a simple select or complex? It could be your index stats are not good. Try recreating the stats and see if that helps.

What changed? Did you add a bunch of records or a service pack or something?|||Hello,

thanks for your reply.
regarding the index stat, how can i recreate the stats?!
my SP is a combination of two complex SELECTS. both involving a few (6-8) JOINS (inner and left joins).
the first select returns about 6000 rows and the second select takes each returned row as a parameter and runs the second select on it. no change in SP and no change in data (i am using static data at the moment). ere has been no update on the SQL server either. the SP used to take 9-10 minutes (solidly) and now it takes 39-40 minutes! (solidly!) i can't think of a reason.|||

I'm not sure what your procedure is doing. There are some cases when using SQL Server the execution plan that gets cached for the procedure doesn't pertain to the parameter values being entered. One thing you may try it to create the procedure so that it recompiles each time it is run.

|||
Value Average

Application Profile Statistics
Timer resolution (milliseconds) 0 0
Number of INSERT, UPDATE, DELETE statements 0 0
Rows effected by INSERT, UPDATE, DELETE statements 0 0
Number of SELECT statements 2 2
Rows effected by SELECT statements 4 4

Number of user transactions 378 378

Average fetch time 0 0
Cumulative fetch time 0 0
Number of fetches 0 0
Number of open statement handles 0 0
Max number of opened statement handles 0 0
Cumulative number of statement handles 0 0

value Average
Network Statistics
Number of server roundtrips 3 3
Number of TDS packets sent 3 3
Number of TDS packets received 52 52

Number of bytes sent 238 238

Number of bytes received 202720 202720

Time Statistics
Cumulative client processing time 458 458

Cumulative wait time on server replies 75904 75904|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1005277&SiteID=1