Re: memory usage
From: Mike Hodgson (mike.hodgson_at_mallesons.nospam.com)
Date: 03/01/05
- Next message: Bijupg: "MSDTC IN WIN 2003 CLUSTER"
- Previous message: Calvin Do: "Re: MSDTC question"
- In reply to: Calvin Do: "memory usage"
- Next in thread: Calvin Do: "Re: memory usage"
- Reply: Calvin Do: "Re: memory usage"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 01 Mar 2005 14:27:19 +1100
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 Calvin Do wrote: >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 > > > >
- Next message: Bijupg: "MSDTC IN WIN 2003 CLUSTER"
- Previous message: Calvin Do: "Re: MSDTC question"
- In reply to: Calvin Do: "memory usage"
- Next in thread: Calvin Do: "Re: memory usage"
- Reply: Calvin Do: "Re: memory usage"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
