July 27, 2011 at 9:28 am
Is is possible (using Profiler, DMVs or anything else) to see which cached plan has been chosen for a particular SP call?
The reason I'm asking is that I occasionally see a situation where the calls made from our website are taking forever to execute, but the same calls with the same parameters executed from Management Studio run instantly. Both calls are executed as the same user so I would expect them to use the same cached plan, but now I've started looking into how cached plans work it seems that you get different plans cached for different combinations of SET options. The issues are always solved by sp_recompile but I'd really like to get to the bottom of why it's happening at all.
I'm trying to put together some scripts to help me check out which plans are being chosen for each call, to see if I can spot any patterns in why this is happening.
Any help is much appreciated.
July 28, 2011 at 2:10 pm
Adam McArdle (7/27/2011)
Is is possible (using Profiler, DMVs or anything else) to see which cached plan has been chosen for a particular SP call?The reason I'm asking is that I occasionally see a situation where the calls made from our website are taking forever to execute, but the same calls with the same parameters executed from Management Studio run instantly. Both calls are executed as the same user so I would expect them to use the same cached plan, but now I've started looking into how cached plans work it seems that you get different plans cached for different combinations of SET options. The issues are always solved by sp_recompile but I'd really like to get to the bottom of why it's happening at all.
I'm trying to put together some scripts to help me check out which plans are being chosen for each call, to see if I can spot any patterns in why this is happening.
Any help is much appreciated.
It sounds like different server options may be used on the different connections, e.g. ANSI_NULLS ON/OFF, QUOTED_IDENTIFIERS, ANSI_PADDING, etc. Different options can cause very different plans, even with the same input parameters. What is the client language of the website?
You can setup a trace to capture the execution plan of the query as it's run from the site. Reference Article
Post the Actual Execution plan when run from both SSMS and from the website to this thread and we'll have a look.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 29, 2011 at 4:55 am
Thanks a lot for that, ShowPlan XML should be enough for me to investigate. It's an intermittent problem, so at least now I'm prepared for the next time it happens!
July 29, 2011 at 5:27 am
The most likely culprit is the SET ARITHABORT option.
By default in SSMS, this is OFF. Most applications will run with ARITHABORT ON. You will see very different execution plans between the same query run in the different modes.
July 29, 2011 at 10:20 am
Philip Yale-193937 (7/29/2011)
The most likely culprit is the SET ARITHABORT option.By default in SSMS, this is OFF. Most applications will run with ARITHABORT ON. You will see very different execution plans between the same query run in the different modes.
SSMS defaults to ON, but you're right, that is a usual suspect.
If it's only periodic it could be something else too like parameter sniffing or old statistics affecting the plan. We need to see the Actual Execution plans from both clients to really go further.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 29, 2011 at 5:54 pm
Doh - wrong way round! Not thinking straight today ...:crazy:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply