performance problem

every 3-4 days I have to restart the sql service because some users are
complaining of poor performance in a specific form ( where they are
intensively add some small info but constantly with speed, and the process
of introduction is always behind where it should be ).
I made a test db where I restored the original db and I noticed that it's
working with normal speed in this parallel database.
My initial supposition was that it should work with the same low speed in
this new database, but it doesn't.
So I think this situation it's somewhat related with the database that it's
intensively worked with, and not with the memory of the server.
In 3-4 days the sql server process grows to about 500MB and if I let the
process for a week it goes to 800MB.
So to resume :
there is poor performance in 3-4 days from restart on a database that is
intensively worked on ,
but a normal performance on a duplicate database on the same server.
The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
storing the databases.
Thanks for any clues
Fatboyslimro wrote:
> every 3-4 days I have to restart the sql service because some users
> are complaining of poor performance in a specific form ( where they
> are intensively add some small info but constantly with speed, and
> the process of introduction is always behind where it should be ).
> I made a test db where I restored the original db and I noticed that
> it's working with normal speed in this parallel database.
> My initial supposition was that it should work with the same low
> speed in this new database, but it doesn't.
> So I think this situation it's somewhat related with the database
> that it's intensively worked with, and not with the memory of the
> server.
> In 3-4 days the sql server process grows to about 500MB and if I let
> the process for a week it goes to 800MB.
>
> So to resume :
> there is poor performance in 3-4 days from restart on a database that
> is intensively worked on ,
> but a normal performance on a duplicate database on the same server.
>
> The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> storing the databases.
> Thanks for any clues
Memory issue sounds fine. SQL Server uses memory as needed up to its set
maximum or what's available in the OS and does not release memory
without a struggle. As more data is read into cache, the memory
footprint will increase. If you are running into a situation where the
OS needs more memory or you have other applications running on the same
server (lie IIS) and they are struggling, you need to set SQL Server to
only use a maximum amount of memory. You can do this easily from SQL
Enterprise Manager.
There could be other problems that are causing the slowness, however.
Report back to us whether you have other application running on the
server besides SQL Server.
David Gugick
Imceda Software
www.imceda.com
|||The server is also a file server and a DC.
But the thing is that in the same time that this database is running slow,
on a duplicate database on the same server the applications are running ok.
the sql server is limited to 800MB of RAM from a total of 1280MB
( before I made this duplicate database I thought that when it's going slow
on a database it will run slow on all the databases on the server - and I
wanted to do some maintenance operations on the duplicate db to see if this
is the problem, but if it runs slow on all the databases it isn't a problem
of indexing or something like this ).
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e7H8kE64EHA.2964@.TK2MSFTNGP15.phx.gbl...
> Fatboyslimro wrote:
> Memory issue sounds fine. SQL Server uses memory as needed up to its set
> maximum or what's available in the OS and does not release memory
> without a struggle. As more data is read into cache, the memory
> footprint will increase. If you are running into a situation where the
> OS needs more memory or you have other applications running on the same
> server (lie IIS) and they are struggling, you need to set SQL Server to
> only use a maximum amount of memory. You can do this easily from SQL
> Enterprise Manager.
> There could be other problems that are causing the slowness, however.
> Report back to us whether you have other application running on the
> server besides SQL Server.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Fatboyslimro wrote:
> The server is also a file server and a DC.
> But the thing is that in the same time that this database is running
> slow, on a duplicate database on the same server the applications are
> running ok.
> the sql server is limited to 800MB of RAM from a total of 1280MB
> ( before I made this duplicate database I thought that when it's
> going slow on a database it will run slow on all the databases on the
> server - and I wanted to do some maintenance operations on the
> duplicate db to see if this is the problem, but if it runs slow on
> all the databases it isn't a problem of indexing or something like
> this ).
>
By adding a second database (for testing?) you are putting further
strain on you memory resources. Why clog up the cache with duplicate
data from a second database?
You may want to update statistics on the database in question. It's not
clear what's going on, but it is clear that you are using your server
for file services and a domain controller and SQL Server and only have
1.2GB RAM. That may be enough (don't know your network and SQL specs),
but why not add more memory since it's so cheap. Putting 2GB RAM in
there would give you a little more headroom and then you could give the
server a little more than 400MB (which is not very much for a server).
David Gugick
Imceda Software
www.imceda.com
|||I don't think the problem is related to how much memory is in the server,
since 4 months ago when the server had only 512MB of RAM everything was ok.
Also, I don't think the problem is related to how much memory is in the
server because the same thing is happening when there is a lot of unused
memory ( say sql with 400MB and the rest for the system - which is a lot ).
I think we got to focus on the main issue: why on all the databases is
working ok, and only on one of them is working slowly after a couple of
days.
thanks for reply
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e2EoHkm5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Fatboyslimro wrote:
> By adding a second database (for testing?) you are putting further
> strain on you memory resources. Why clog up the cache with duplicate
> data from a second database?
> You may want to update statistics on the database in question. It's not
> clear what's going on, but it is clear that you are using your server
> for file services and a domain controller and SQL Server and only have
> 1.2GB RAM. That may be enough (don't know your network and SQL specs),
> but why not add more memory since it's so cheap. Putting 2GB RAM in
> there would give you a little more headroom and then you could give the
> server a little more than 400MB (which is not very much for a server).
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

performance problem

every 3-4 days I have to restart the sql service because some users are
complaining of poor performance in a specific form ( where they are
intensively add some small info but constantly with speed, and the process
of introduction is always behind where it should be ).
I made a test db where I restored the original db and I noticed that it's
working with normal speed in this parallel database.
My initial supposition was that it should work with the same low speed in
this new database, but it doesn't.
So I think this situation it's somewhat related with the database that it's
intensively worked with, and not with the memory of the server.
In 3-4 days the sql server process grows to about 500MB and if I let the
process for a week it goes to 800MB.
So to resume :
there is poor performance in 3-4 days from restart on a database that is
intensively worked on ,
but a normal performance on a duplicate database on the same server.
The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
storing the databases.
Thanks for any cluesFatboyslimro wrote:
> every 3-4 days I have to restart the sql service because some users
> are complaining of poor performance in a specific form ( where they
> are intensively add some small info but constantly with speed, and
> the process of introduction is always behind where it should be ).
> I made a test db where I restored the original db and I noticed that
> it's working with normal speed in this parallel database.
> My initial supposition was that it should work with the same low
> speed in this new database, but it doesn't.
> So I think this situation it's somewhat related with the database
> that it's intensively worked with, and not with the memory of the
> server.
> In 3-4 days the sql server process grows to about 500MB and if I let
> the process for a week it goes to 800MB.
>
> So to resume :
> there is poor performance in 3-4 days from restart on a database that
> is intensively worked on ,
> but a normal performance on a duplicate database on the same server.
>
> The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> storing the databases.
> Thanks for any clues
Memory issue sounds fine. SQL Server uses memory as needed up to its set
maximum or what's available in the OS and does not release memory
without a struggle. As more data is read into cache, the memory
footprint will increase. If you are running into a situation where the
OS needs more memory or you have other applications running on the same
server (lie IIS) and they are struggling, you need to set SQL Server to
only use a maximum amount of memory. You can do this easily from SQL
Enterprise Manager.
There could be other problems that are causing the slowness, however.
Report back to us whether you have other application running on the
server besides SQL Server.
David Gugick
Imceda Software
www.imceda.com|||The server is also a file server and a DC.
But the thing is that in the same time that this database is running slow,
on a duplicate database on the same server the applications are running ok.
the sql server is limited to 800MB of RAM from a total of 1280MB
( before I made this duplicate database I thought that when it's going slow
on a database it will run slow on all the databases on the server - and I
wanted to do some maintenance operations on the duplicate db to see if this
is the problem, but if it runs slow on all the databases it isn't a problem
of indexing or something like this ).
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e7H8kE64EHA.2964@.TK2MSFTNGP15.phx.gbl...
> Fatboyslimro wrote:
> > every 3-4 days I have to restart the sql service because some users
> > are complaining of poor performance in a specific form ( where they
> > are intensively add some small info but constantly with speed, and
> > the process of introduction is always behind where it should be ).
> > I made a test db where I restored the original db and I noticed that
> > it's working with normal speed in this parallel database.
> > My initial supposition was that it should work with the same low
> > speed in this new database, but it doesn't.
> > So I think this situation it's somewhat related with the database
> > that it's intensively worked with, and not with the memory of the
> > server.
> > In 3-4 days the sql server process grows to about 500MB and if I let
> > the process for a week it goes to 800MB.
> >
> >
> > So to resume :
> >
> > there is poor performance in 3-4 days from restart on a database that
> > is intensively worked on ,
> > but a normal performance on a duplicate database on the same server.
> >
> >
> > The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> > storing the databases.
> >
> > Thanks for any clues
> Memory issue sounds fine. SQL Server uses memory as needed up to its set
> maximum or what's available in the OS and does not release memory
> without a struggle. As more data is read into cache, the memory
> footprint will increase. If you are running into a situation where the
> OS needs more memory or you have other applications running on the same
> server (lie IIS) and they are struggling, you need to set SQL Server to
> only use a maximum amount of memory. You can do this easily from SQL
> Enterprise Manager.
> There could be other problems that are causing the slowness, however.
> Report back to us whether you have other application running on the
> server besides SQL Server.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Fatboyslimro wrote:
> The server is also a file server and a DC.
> But the thing is that in the same time that this database is running
> slow, on a duplicate database on the same server the applications are
> running ok.
> the sql server is limited to 800MB of RAM from a total of 1280MB
> ( before I made this duplicate database I thought that when it's
> going slow on a database it will run slow on all the databases on the
> server - and I wanted to do some maintenance operations on the
> duplicate db to see if this is the problem, but if it runs slow on
> all the databases it isn't a problem of indexing or something like
> this ).
>
By adding a second database (for testing?) you are putting further
strain on you memory resources. Why clog up the cache with duplicate
data from a second database?
You may want to update statistics on the database in question. It's not
clear what's going on, but it is clear that you are using your server
for file services and a domain controller and SQL Server and only have
1.2GB RAM. That may be enough (don't know your network and SQL specs),
but why not add more memory since it's so cheap. Putting 2GB RAM in
there would give you a little more headroom and then you could give the
server a little more than 400MB (which is not very much for a server).
David Gugick
Imceda Software
www.imceda.com|||I don't think the problem is related to how much memory is in the server,
since 4 months ago when the server had only 512MB of RAM everything was ok.
Also, I don't think the problem is related to how much memory is in the
server because the same thing is happening when there is a lot of unused
memory ( say sql with 400MB and the rest for the system - which is a lot ).
I think we got to focus on the main issue: why on all the databases is
working ok, and only on one of them is working slowly after a couple of
days.
thanks for reply
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e2EoHkm5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Fatboyslimro wrote:
> > The server is also a file server and a DC.
> >
> > But the thing is that in the same time that this database is running
> > slow, on a duplicate database on the same server the applications are
> > running ok.
> >
> > the sql server is limited to 800MB of RAM from a total of 1280MB
> >
> > ( before I made this duplicate database I thought that when it's
> > going slow on a database it will run slow on all the databases on the
> > server - and I wanted to do some maintenance operations on the
> > duplicate db to see if this is the problem, but if it runs slow on
> > all the databases it isn't a problem of indexing or something like
> > this ).
> >
> By adding a second database (for testing?) you are putting further
> strain on you memory resources. Why clog up the cache with duplicate
> data from a second database?
> You may want to update statistics on the database in question. It's not
> clear what's going on, but it is clear that you are using your server
> for file services and a domain controller and SQL Server and only have
> 1.2GB RAM. That may be enough (don't know your network and SQL specs),
> but why not add more memory since it's so cheap. Putting 2GB RAM in
> there would give you a little more headroom and then you could give the
> server a little more than 400MB (which is not very much for a server).
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

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

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 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...
> >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
>
>|||... just be aware that the "per sec" counters doesn't reflect per second values 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...
> 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|||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

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