Individual Database Memory usage

Question

Individual Database Memory usage

David H

SSC Enthusiast

Points: 166
More actions
April 3, 2006 at 2:47 pm

#152881

Hopefully I haven't missed an easy answer to these questions, I have spent a long time looking, and I think in the right places, so here goes...
Background:
My client has a database server running 2000 standard edition. This has got 60+ individual database on it. One major database - for my system, and lots of others for other systems I don't control, or really know what they do.
I've got some stats out of sysperfinfo that show one of these other databases has a much faster growing Transactions/sec than my database.
My questions are:
1) How can I tell how much of the memory the server has (2GB) is being used by each database?
2) Does Transactions/sec really tell me much, if one of the databases is mostly insert (I think), and the other has everything happening to it?
Any help would be much appreciated.
Thanks, David

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 1

I think answer to question "How can I tell how much of the memory the server has (2GB) is being used by each database?" is "not possible" and I am not aware of any third party product. I would like to see if someone can come up with an answer.

Kindest Regards,

Amit Lohia

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Not possible to tell. SQL uses its memory for all databases as required. memory is divided into areas, but not per database. Things like buffer cache, lock cache, procedure cache, etc.

I wouldn't worry about high tran/sec unless you're picking up performance problems - long query waits, deadlocks, high blocking or anything like that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 3

I agree with the above. The transactions/sec is not indicative of a problem. It shows the amount of work being done in that database. And your application shouldn't be competing on the amount of work being done. It should do its job and work or not and if not, you figure out why.

David H SSC Enthusiast Points: 166 More actions · Answer 4

Hmm. Thanks for the feedback.

The thing is, that we are experiencing performance issues when we shouldn't be, and I'm trying to work out whwther these are databases are using/locking resource that should be available to my system. I know disk access is quite high, and think one of the big other databases may be hindering mine.

I was quite suprised that the other database had a 100K change in Transactions/Sec vs 25K in mine over 1.75 hours. This seemed a high ratio considereing my databse is the main one for the server.

Cheers

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 5

If you really want to analyze the impact on the server - your db vs. others, get ahold of the PSSDiag and Read80Trace utilities from Microsoft (search the KB). They should tell you exactly which queries are consuming the most resources (except for memory 😉

David H SSC Enthusiast Points: 166 More actions · Answer 6

Thanks Fred, I've now got the tools, so time to see what I can get out of them...

Cheers