capture showplan in profiler

I want to capture showplan for all the queries running on the server at a
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
ThanksHassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>

capture showplan in profiler

What other event classes or columns do I need to view the showplan all event
class in profiler? The textdata seems to be empty. Using SQL Server 2000Binary data column. There is also a pretty cool way of extracting the
information if you save/load the trace into a table here
http://www.umachandar.com/technical/SQL2000Scripts/UtilitySPs/Main9.htm
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uW4ukVqcDHA.652@.tk2msftngp13.phx.gbl...
What other event classes or columns do I need to view the showplan all event
class in profiler? The textdata seems to be empty. Using SQL Server 2000

capture showplan in profiler

I want to capture showplan for all the queries running on the server at a
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
ThanksHassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>

capture showplan in profiler

I want to capture showplan for all the queries running on the server at a
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
Thanks
Hassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>