Sproc performance degrades over time

Hi, this is my first post so if you need clarificatrion on anything then just let me know.

My server details are as follows: - Windows 2008 Datacentre edition
SQL 2008 standard edition (10.0.1600)
12GB Ram
Quad core single processor machine

The problem
I have a stored procedure that runs and when I have just started SQL up, it takes around 1/10th of a second to run. After an amount of time, it takes around 3 seconds to run the same query.

I originally assumed it was the indexes that were causing issues but if I make an exact copy of the sproc and run that copied version then that query now only takes 1/10th of a second again and the original one still takes 3 seconds.

I am now assuming that it is something to do with the execution plan of the sproc being cached and when the sproc is run again then it is messing the execution plan up.

Things I have tried so far
I currently have a maintenance plan that runs every 15 minutes that re-indexes a small table and for some reason the times of execution on my sprocs drop back to normal levels but then the times suddenly go back up again.

Created a copy of the sproc to test it and that one runs at 1/10th of a second and the original one still takes a long time.

Ran the "update stats" sproc to make sure all the stats are up to date.

Ran SQL query profiler to see if it makes any suggestions on other indexes that should be on tables, it ended up making some suggestions that increased my index and db size to over 70gb and the performance increase was negligble.

Other information to note
The db is spread accross two dbs in the same instance, one contains product information, the other contains customer information.

One of the joining tables is 130 million lines long.

The db is an upgrade from 2005 to 2008.

I perform the re-index every 15 minutes so that the queries start performing properly again. They drop down to 1/10th of a second again once the small re-index has taken place? This is what is making me believe it is something to do with the execution plan or some kind of parameter caching

Chris Lomax 2010-09-27 12:58:21

@Chris - I'd ditch the reindexing and use the `OPTIMIZE FOR UNKNOWN` hint.

Martin Smith 2010-09-27 13:00:21

Thanks Martin, I am going to give that a go and see if I see any performance increases. Or rather, no degradation.

Chris Lomax 2010-09-27 13:16:27

Everything seems good so far, I have disabled the 15 minute maintenance plan now and I keep checking the site. So far I have had no degradation on the site and the implementation was easy. The explanation also makes perfect sense. I'll mark the answer once I have done 2 hours testing.

Chris Lomax 2010-09-27 13:53:11

Perfect, its been 5 hours now and none of the queries have degraded. This new found wisdom about the query planner has spurred me to re-visit some of my older queries to make sure they are all up to scratch. Thanks!

Chris Lomax 2010-09-27 18:43:09

I do have quite a lot of parameters in the sproc already and the shape of the query doesn't actually change for different parameters. The parameters on the query are dates, customer id, category id etc. I will look into that article further in a moment.

Chris Lomax 2010-09-27 12:54:56

@Chris - It's not about the shape of the query. The problem is, for example, that when the procedure is compiled with unusually selective parameters you might get a different plan with index seeks and bookmark lookups that is not optimal for less selective cases.

Martin Smith 2010-09-27 12:55:57

ansaurus

tags:

views:

answers:

Sproc performance degrades over time

related questions