Prev: seeking set based solution
Next: I assume Row_Number doesn’t act only on rows of the window frame
From: Iter on 30 Apr 2010 13:46
Hi,
Is that way to get query plan for a stored procedure in stead of single
statement in sql server 2008? Thanks.
From: Patrice on 30 Apr 2010 14:14
What have you tried ? Not sure what you mean...

A stored procedure doesn't have a plan. A plan is produced for queries so if
your stored procedure has multiple queries it will produce a plan for each
of those queries...

--
Patrice

"Iter" <Iter(a)discussions.microsoft.com> a écrit dans le message de
news:3C57BBC7-2E7B-4CC4-B27A-0F1E8354A109(a)microsoft.com...
> Hi,
> Is that way to get query plan for a stored procedure in stead of single
> statement in sql server 2008? Thanks.

From: Iter on 30 Apr 2010 15:20
I mean is that a way that I can get each query plans for each statement in
the sp when I run the stored procedure. I know I need to copy that statement
into SSMS and produce the query plan now. Is that a way that I can get all
query plan when I run the sp? Just like sybase does. thank.

"Patrice" wrote:

> What have you tried ? Not sure what you mean...
>
> A stored procedure doesn't have a plan. A plan is produced for queries so if
> your stored procedure has multiple queries it will produce a plan for each
> of those queries...
>
> --
> Patrice
>
> "Iter" <Iter(a)discussions.microsoft.com> a écrit dans le message de
> news:3C57BBC7-2E7B-4CC4-B27A-0F1E8354A109(a)microsoft.com...
> > Hi,
> > Is that way to get query plan for a stored procedure in stead of single
> > statement in sql server 2008? Thanks.
>
> .
>
From: Patrice on 30 Apr 2010 16:01
>I mean is that a way that I can get each query plans for each statement in
> the sp when I run the stored procedure. I know I need to copy that
> statement
> into SSMS and produce the query plan now. Is that a way that I can get all
> query plan when I run the sp? Just like sybase does. thank.

http://msdn.microsoft.com/en-us/library/ms187735.aspx (SET SHOWPLAN_ALL) and
execute the stored procedure rather a single statement ?

Sorry, I'm a bit confused as it seems to me you make a difference between
getting then plan for a single statement and for multiple statement... Once
the option is activated, you'll just execute whatever you want, a single
statement as well as a stored procedure if you wish.

Depending on what you want to analyze you can also :
- use SQL Server Profiler to get the plan of the actual procedures or
statements that runs in your app
- use management views to get some info about query execution :
http://msdn.microsoft.com/en-us/library/ms188068.aspx

--
Patrice



From: BruceL on 30 Apr 2010 16:00
On Apr 30, 3:20 pm, Iter <I...(a)discussions.microsoft.com> wrote:
> I mean is that a way that I can get each query plans for each statement in
> the sp when I run the stored procedure. I know I need to copy that statement
> into SSMS and produce the query plan now. Is that a way that I can get all
> query plan when I run the sp? Just like sybase does. thank.
>
> "Patrice" wrote:
> > What have you tried ? Not sure what you mean...
>
> > A stored procedure doesn't have a plan. A plan is produced for queries so if
> > your stored procedure has multiple queries it will produce a plan for each
> > of those queries...
>
> > --
> > Patrice
>
> > "Iter" <I...(a)discussions.microsoft.com> a écrit dans le message de
> >news:3C57BBC7-2E7B-4CC4-B27A-0F1E8354A109(a)microsoft.com...
> > > Hi,
> > > Is that way to get query plan for a stored procedure in stead of single
> > > statement in sql server 2008? Thanks.
>
> > .

in SQL Query Analyser, use
SET SHOWPLAN_ALL ON

exec mysp
 |  Next  |  Last
Pages: 1 2
Prev: seeking set based solution
Next: I assume Row_Number doesn’t act only on rows of the window frame