I have started looking at the some of the sys.dm views - for instance,
sys.dm_exec_query_stats. Also started reading Inside MSSQL server 2005 QUery
Tuning and optimization.

The servers I manage have lots of databases on them, in some cases, I have
the same database but for different business groups.

The view tells me what query are being run, performance etc, but it does not
tell me what database it comes from. I tried some of the reports under SSMS
- Performance - Top Queries by Average CPU time. It has coulmns for database
name & Object iD, but these are not filled in.

So how can I add the database the query was run against?

Thanks

SELECT DB_NAME(p.dbid), s.*
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p


"Angelo" <Angelo@discussions.microsoft.com> wrote in message
news:CCECA505-7A4A-4CED-BD17-B132E68BFB9B@microsoft.com...
>I have started looking at the some of the sys.dm views - for instance,
> sys.dm_exec_query_stats. Also started reading Inside MSSQL server 2005
> QUery
> Tuning and optimization.
>
> The servers I manage have lots of databases on them, in some cases, I have
> the same database but for different business groups.
>
> The view tells me what query are being run, performance etc, but it does
> not
> tell me what database it comes from. I tried some of the reports under
> SSMS
> - Performance - Top Queries by Average CPU time. It has coulmns for
> database
> name & Object iD, but these are not filled in.
>
> So how can I add the database the query was run against?
>
> Thanks
>

ahh, I I know understand why they are Null as well. Thanks Aaron. Spot on.







"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT DB_NAME(p.dbid), s.*
> FROM sys.dm_exec_query_stats s
> CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
>
>
> "Angelo" <Angelo@discussions.microsoft.com> wrote in message
> news:CCECA505-7A4A-4CED-BD17-B132E68BFB9B@microsoft.com...
> >I have started looking at the some of the sys.dm views - for instance,
> > sys.dm_exec_query_stats. Also started reading Inside MSSQL server 2005
> > QUery
> > Tuning and optimization.
> >
> > The servers I manage have lots of databases on them, in some cases, I have
> > the same database but for different business groups.
> >
> > The view tells me what query are being run, performance etc, but it does
> > not
> > tell me what database it comes from. I tried some of the reports under
> > SSMS
> > - Performance - Top Queries by Average CPU time. It has coulmns for
> > database
> > name & Object iD, but these are not filled in.
> >
> > So how can I add the database the query was run against?
> >
> > Thanks
> >
>
>