Execution plan

Transact-SQL
1

Hi,

I have a SP which has 2 execution plan in the cache..1 plan for execution form SSMS and 1 from UI.

The SSMS one executes in milli seconds while the UI one takes for ever.

How do I find out whats the difference between the SSMS and UI execution?

I took the executed SP call when executed from UI (using profiler) and ran the same in SSMS..that again uses the SSMS execution plan and runs in milli seconds.

Thanks
Sam

2

compare the options on the execution plans. There's probably a difference which is messing you up

3

Adding to what gbritton said, the options are the SET options for the session. SSMS has different options than the app, typically.

For instance, I worked on a performance problem this morning. I grabbed the user's SET options by running a quick trace with the "existing connections" event and filtering on the userid. Here are its settings:

-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

So I added those to my SSMS session and then ran the stored proc that I was troubleshooting. I then can get the same execution plan as the app.

You can also tweak your SSMS sessions in the options GUI, but I just grab the app's options when I need it.

4

Please note that the options I posted above could be different than the options your UI is using. You need to grab the UI's options and use those in SSMS.

5

Thanks Tara and Gerald..will try these options and get back..

6

Just to add more to it, since you are using profiler to capture the execution plan, you can just compare the plan for both runs and see what is the differences, I would say most likely is the stats, but that is just a guess, so do compare the plan and see if that helps.

7

Hi,
How can I get the execution plan from my trace?
Thanks
Sam

Update : The issue got resolved on its own :slight_smile:

8

Sounds like stats got updated, indexes rebuilt, etc...and it got a new plan.

9

It could also have been the bad form of parameter sniffing.