Sproc performance degrades over time
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.
This seems like parameter sniffing to me.
Your 15 minute re-indexing (do you need that!?) will cause the dependant procedure to be recompiled. Sometimes when that happens it will so happen that the parameter values passed at the next execution are sub optimal for the general case. You can use OPTIMIZE FOR to prevent this from happening.
This looks like to be caused by parameter sniffing. Here is a nice explanation:
I Smell a Parameter!
SQL Garbage Collector: Parameter Sniffing & Stored Procedures Execution Plan
链接地址: http://www.djcxy.com/p/62242.html上一篇: 如何在没有事务的情况下执行sproc？
下一篇: 随着时间的推移，Sproc性能会下降