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.
ThanksIf 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 identif
y
> 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 Statistic
s
Batch Requests/sec but for a single database?
Thanks
"TheSQLGuru" wrote:

> If you are using sql2005 check out the dynamic management views in BOL. I
f
> 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...
>
>|||... just be aware that the "per sec" counters doesn't reflect per second va
lues in the dynamic
management view. They are accumulative. So to get a per sec value, you need
to know a prior value
and the number of seconds elapsed since you captured that prior value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:D33709DC-EE6D-4CBE-A197-CBD9C0F50B5D@.microsoft.com...[vbcol=seagreen]
> 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:
>|||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_r
eads) 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/.../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 identif
y
> 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