Need help obtaining query_plan from T-SQL statement (SQL Server 20

22 Dec 2005 3:41 PM

Mike

Could some please help me in obtaining the query plan by modifying the T-SQL
statement below? Thank you.

select top 50
    sum(qs.total_worker_time) as total_cpu_time,
    sum(qs.execution_count) as total_execution_count,
    count(*) as number_of_statements,
    qs.plan_handle
from
    sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

--
Mike

22 Dec 2005 3:47 PM

John Smith

Ctrl+K

Show quote

"Mike" <M***@discussions.microsoft.com> wrote in message
news:A932FC01-5387-40E2-9607-30AA40454723@microsoft.com...
> Could some please help me in obtaining the query plan by modifying the
T-SQL
> statement below? Thank you.
>
> select top 50
>     sum(qs.total_worker_time) as total_cpu_time,
>     sum(qs.execution_count) as total_execution_count,
>     count(*) as number_of_statements,
>     qs.plan_handle
> from
>     sys.dm_exec_query_stats qs
> group by qs.plan_handle
> order by sum(qs.total_worker_time) desc
>
> --
> Mike

22 Dec 2005 4:06 PM

Mike

I didn't mean the query plan of the query itself, but rather I need to link
the qs.plan_handle column to another table to obtain the queries that are
causing CPU bottlenecks.

Thank you.

--
Mike

Show quote

"John Smith" wrote:

> Ctrl+K
>
>
> "Mike" <M***@discussions.microsoft.com> wrote in message
> news:A932FC01-5387-40E2-9607-30AA40454723@microsoft.com...
> > Could some please help me in obtaining the query plan by modifying the
> T-SQL
> > statement below? Thank you.
> >
> > select top 50
> >     sum(qs.total_worker_time) as total_cpu_time,
> >     sum(qs.execution_count) as total_execution_count,
> >     count(*) as number_of_statements,
> >     qs.plan_handle
> > from
> >     sys.dm_exec_query_stats qs
> > group by qs.plan_handle
> > order by sum(qs.total_worker_time) desc
> >
> > --
> > Mike
>
>
>

22 Dec 2005 5:01 PM

Mike

I was able to look at books online, and figure it out myself. Sorry for the
trouble.
Thank you.

select top 50
    sum(qs.total_worker_time) as total_cpu_time,
    sum(qs.execution_count) as total_execution_count,
    count(*) as number_of_statements,
    (select query_plan from sys.dm_exec_query_plan(qs.plan_handle)) AS Query_Plan
from
     sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

--
Mike

Show quote

"Mike" wrote:

> Could some please help me in obtaining the query plan by modifying the T-SQL
> statement below? Thank you.
>
> select top 50
>     sum(qs.total_worker_time) as total_cpu_time,
>     sum(qs.execution_count) as total_execution_count,
>     count(*) as number_of_statements,
>     qs.plan_handle
> from
>     sys.dm_exec_query_stats qs
> group by qs.plan_handle
> order by sum(qs.total_worker_time) desc
>
> --
> Mike