Load per database

Question

Load per database

mysticslayer

Old Hand

Points: 319
More actions
October 23, 2009 at 8:19 am

#385640

Hi there all,
I know this maybe an easy question, but I couldn't find the answer yet:
I want to check the load per database and which database has the most load and which one the least.
How can I pull out this information?
Thanks in advance.

Viewing 3 posts - 1 through 3 (of 3 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Run a trace on batch completion. Include the database ID and/or name in the trace. Pretty quickly, you'll have data on which databases are used the most, and how frequently and in what ways. Over time, you'll have data on exactly how often each database is accessed.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

It depends on what kind of load you are after. The trace method is one way to go about it, but if you are targeting a specific load point, like IO, you can find it other ways as well. The sys.dm_os_virtual_file_stats view breaks IO load down by database file and can be useful in targeting which database is the most IO heavy out of the bunch. You could have a high batch load to a database that is still a low IO consumer depending on a number of factors.