Performance monitoring specific database

I have a server with over a hundred databases, let's say I want to identify
which databases are the most active and which ones use the more resources
(CPU, IO and memory). What counters or at what data should I look at?
I don't need anything very precise, simply an overall view and I will then
look more precisely at individual databases.
Thanks
If you are using sql2005 check out the dynamic management views in BOL. If
2000, your easiest bet would be to purchase a third-party monitoring tool.
You could set up a profiler run and perform some statistical analysis off of
the results, or store snapshots of sysprocesses over time and analyze that
perhaps.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"olig" <olig@.discussions.microsoft.com> wrote in message
news:1C1EF508-97B3-48C8-8A12-EB20540D9D3E@.microsoft.com...
>I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks
|||Maybe this could help you to get started
select *
from sys.dm_os_performance_counters
where object_name = 'SQLServer:Databases'
and counter_name = 'Transactions/sec'
order by cntr_value desc
sys.dm_os_performance_counters is a DMV on SQL Server 2005. You should use
the sysperfinfo system table for SQL Server 2000. In any case change
object_name for named instances.
Then you can use some other DMVs or tools to focus on specific databases.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"olig" wrote:

> I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks
|||I'm using sql2005 and I know about dynamic management views but I don't
really know which one to look at. I think most of the data show of snapshot
of what is going on right now, for example the list of query executing in
each database. Are any of them cumulative? E.g. number of requests executed
in a database since last server restart of something like that.
One possible way I am thinking using the System Monitor is to start a
counter log and log some counters to a database for a period of time (let's
say an hour). I could then query that table to get some average or maximum
values.
I guess I could monitor the SQLServer:Databases Transaction/sec counter, but
I'm not sure that the number of transaction is that much a good indicator of
activity in a database. Can I have the equivalent of SQLServer:SQL Statistics
Batch Requests/sec but for a single database?
Thanks
"TheSQLGuru" wrote:

> If you are using sql2005 check out the dynamic management views in BOL. If
> 2000, your easiest bet would be to purchase a third-party monitoring tool.
> You could set up a profiler run and perform some statistical analysis off of
> the results, or store snapshots of sysprocesses over time and analyze that
> perhaps.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "olig" <olig@.discussions.microsoft.com> wrote in message
> news:1C1EF508-97B3-48C8-8A12-EB20540D9D3E@.microsoft.com...
>
>
|||You can do something like this:
select db_name(qp.dbid)
, sum(total_worker_time) as CPU
, sum(total_elapsed_time) as Duration
, sum(total_logical_reads+total_physical_reads) as IO
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
group by db_name(qp.dbid)
order by 3 desc
If you have memory pressure or a plan reuse problem a trace would probably
be better.
select db_name(databaseid), sum(cpu), sum(reads), sum(duration)
from YourTrace
group by db_name(databaseid),
order by 2 desc
Jason Massie
Web: http://statisticsio.com
RSS: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"olig" <olig@.discussions.microsoft.com> wrote in message
news:1C1EF508-97B3-48C8-8A12-EB20540D9D3E@.microsoft.com...
>I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks
|||I have a SQL Server DBA Dashboard that has some of those things you are
looking for. You can download the dashboard from website below.
If you are looking for SQL Server examples or a free SQL Server DBA
Dashboard tool check out my Website at http://www.sqlserverexamples.com
"olig" wrote:

> I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks