Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lazerath
Constraint Violating Yak Guru
343 Posts |
 Posted - 2010-09-24 : 18:04:35
|
| Hi SQL Server Gurus!So, we are having a problem whereby a vendor based program runs over 50 stored procedures to process a single incoming record. These stored procedures are relatively simple. What is happening is they run great for a while and then performance degrades significantly. Freeing the procedure cache produces an immediate speed increase.Now, I've seen this happen numerous times before in other scenarios that lead me to believe parameter sniffing is the culprit. I am wondering what other possibilities could cause an outdated/inefficient execution plan to continue to be used.As data grows, I believe there is also a possibility that statistics change and the execution plans can become outdated. Does this theory hold any water? Anything else that could be contributing?Thanks in advance for any assistance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2010-09-24 : 18:32:25
|
| Thanks for the response Tara.I am not sure at the moment how often update stats run because I am not the DBA for this server. I am working with the DBA to resolve this, but wanted to be sure not to misspeak. The other thing I was thinking was that freeing the procedure cache would free more space for buffer cache. I am just not certain how much effect this would really have to increase performance. This server has a large SAN backend, most of these queries are writing, and the tables involved are not huge (AFAIK).Conceptually, I just wanted to get an idea of all the scenarios that would cause a stored execution plan in cache to differ so drastically from one that would be generated at a later point in time. I know that IF...THEN...ELSE, statistics changes and parameter sniffing can do this.... any others? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-09-24 : 18:38:46
|
| Here are some of the things that I've come across either through experience or through reading people's posts:1. Parameter sniffing2. IF logic in stored procedures with many branches3. Out of date statistics due to lack of auto update stats and manual update stats4. Large data modification that causes out of date statistics (run update stats after large data mods)5. Table variables (no stats on these)6. Varying input parameters (may want to recompile stored proc on every exec)That should get you started. Let us know what you find.Can you post the offending sproc?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2010-09-24 : 18:43:05
|
| Tara, I can't yet. I'll let you know on Monday once I can dig into this more. Thanks again for your help! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Outdated Execution Plan in Proc Cache