Hello,


I have a sql 2000 box that has roughly 15 datbases. How do i determine how
much memory/cpu a particular db is using?


thanks!
Calvin

thank you for the email Mike, i will review...
[quoted text, click to view]
DBs don't use CPU, client connections do. You can correlate CPU use =
for current connections by doing a little extrapolation from the =
master.dbo.sysprocesses table. That table lists all current connections =
and each row specifies which DB is currently being used (dbid) by the =
connection and how much CPU time that connection has consumed. (Take =
this with a grain of salt though because a SPID can change DB simply by =
running a USE statement, so there's no guarantee the SPID has been using =
the same DB its whole life.) This is a cumulative value so if a client =
has been connected to the server by the same SPID for a long time, the =
cumulative CPU will be high compared to a relatively new SPID. Just =
bear that in mind when extrapolating - you might do well to divide the =
CPU figure by the number of hours, "datediff(hh, login_time, =
getdate())", (or minutes or days or...) the SPID has been alive for to =
get an hourly average CPU - slightly more helpful.

As for memory, it's very hard to tell what percentage of memory is =
attributable to each DB. SQL Server simply caches data pages =
(regardless of which DB they belong to) when they're accessed. The =
execution plans stored in the procedure cache can involve objects in =
many databases so you can't really attribute them to any particular DB. =
You can see a little bit of information about the buffer cache by =
running:

DBCC MEMUSAGE

This will show you a little info about the top 20 objects in terms of =
cache use. It lists the dbid (what you're interested in), the objectid =
& indexid from that DB (this will be the id of the associated =
table/index) and the number of buffers (8K pages?) being used by that =
object in the buffer cache. NB/ Microsoft recommend not using DBCC =
MEMUSAGE and using the related perfmon counters instead (see =
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/instsq=
l/in_backcomp_992x.asp) but I find DBCC MEMUSAGE still slightly helpful, =
although I suppose you could get the perfmon data you're after (that =
replaces DBCC MEMUSAGE) from querying the master.dbo.sysperfinfo table =
and correlating that data.

--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@mallesons.nospam.com | W =
http://www.mallesons.com=20



Calvin Do wrote:=20
Hello,


I have a sql 2000 box that has roughly 15 datbases. How do i determine =
how
much memory/cpu a particular db is using?


thanks!

DBs don't use CPU, client connections do. You can correlate CPU use for
current connections by doing a little extrapolation from the
master.dbo.sysprocesses table. That table lists all current connections
and each row specifies which DB is currently being used (dbid) by the
connection and how much CPU time that connection has consumed. (Take
this with a grain of salt though because a SPID can change DB simply by
running a USE statement, so there's no guarantee the SPID has been using
the same DB its whole life.) This is a cumulative value so if a client
has been connected to the server by the same SPID for a long time, the
cumulative CPU will be high compared to a relatively new SPID. Just
bear that in mind when extrapolating - you might do well to divide the
CPU figure by the number of hours, "datediff(hh, login_time,
getdate())", (or minutes or days or...) the SPID has been alive for to
get an hourly average CPU - slightly more helpful.

As for memory, it's very hard to tell what percentage of memory is
attributable to each DB. SQL Server simply caches data pages
(regardless of which DB they belong to) when they're accessed. The
execution plans stored in the procedure cache can involve objects in
many databases so you can't really attribute them to any particular DB.
You can see a little bit of information about the buffer cache by running:

DBCC MEMUSAGE

This will show you a little info about the top 20 objects in terms of
cache use. It lists the dbid (what you're interested in), the objectid
& indexid from that DB (this will be the id of the associated
table/index) and the number of buffers (8K pages?) being used by that
object in the buffer cache. NB/ Microsoft recommend not using DBCC
MEMUSAGE and using the related perfmon counters instead (see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_992x.asp)
but I find DBCC MEMUSAGE still slightly helpful, although I suppose you
could get the perfmon data you're after (that replaces DBCC MEMUSAGE)
from querying the master.dbo.sysperfinfo table and correlating that data.

--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com



[quoted text, click to view]