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 |
|
shaggy
Posting Yak Master
248 Posts |
 Posted - 2014-01-22 : 00:17:09
|
| I've got one SP in a intermitting times it is taking very long time to execute but when I recompile the SP it is executes fast which is normal coz it is a very lightweight query and query condition also very straight the query has 1 IF and 1 else block joined with 5 tables and in select there is 2 User defined functionsby looking plan it is ok coz all tables are going to seek and cost aso very less I could not able to figure out where the problem is can any one put some light in this.Note: index and stats are uptodate ,no large data movemnent asusal normal transaction data. ,no new index added.Thanks in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-01-22 : 01:29:11
|
| Sounds like a parameter sniffing issue or out-of-date statistics. If you are using SQL Server 2012, you could add a plan guide for the affected query in the stored procedure. I feel your pain. My most critical system struggles with this. We've got 6 plan guides deployed so far, 2 more in the works.On our older hardware, I wrote code that would check for a CPU condition and when it exists it would start recompiling stored procedures until the issue was resolved. On our new hardware, we never reach the CPU thresholds even when this bad plan issue exists. We never get over 35% CPU.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2014-01-22 : 01:45:10
|
| thanks tkizerCan this will be helpful define a local variable and assign the relevant parameter to that and then use the local variable in the selection. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-01-22 : 02:46:38
|
| That is one old technique to try to workaround the parameter sniffing issue. We don't even bother with that anymore now that SQL has plan guides.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2014-01-22 : 03:26:27
|
| Since my client sticking with 2008 I have to follow the same so any other workaround is highly appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-01-22 : 12:10:44
|
| Plan guides were actually available in 2008 too: http://technet.microsoft.com/en-us/library/ms190417(v=sql.105).aspxYou need to determine what is causing the bad plan. Have you captured the good plan and the bad plan? Can you post its XML?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
sp recompile