select statman killing performance
Posted on 2008-10-04
My problem emerges after a maintenance plan runs. I think the plan blows away the statistics or something, but not everytime it runs (only sometimes--maybe every tenth time). The maitenance plan runs once a month.
The issue affects only one stored procedure. The produre is fairly normal, but it does contain a cursor, uses the goto statement and has single temp table.
I read that I should remove all temp tables and dynamic sql from the stored procedure. I did this (except for the one temp table I couldn't get rid of) and it didn't work. The only thing that seems to work is time, i.e., after about three days everything goes back to normal. The query goes from taking 300 MINUTES to run back down to its normal 30 minutes.
already 24 hours into this :-(