Sql-server – How to see which database on a Server uses most resources

There's a ton of dynamic management views (DMVs) you can use to get the data, but the easiest way is to use Adam Machanic's sp_WhoIsActive stored proc. Here's a video on how to use it:

http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

And you can download it from here:

http://whoisactive.com/

The output includes columns for the database name, CPU cycles used, query duration, and more.

Sorry, not to disparage Thomas' advice, but please take "general rules" with a grain of salt, or just throw them out the window altogether.

Baseline.

What is normal for your system? Is the system currently responding ok?

If there is no performance issue, don't try to compare your system to some number someone plucked out of the air or potentially based off some very specific system and workload years ago, and drop everything to try to "fix" it.

Specifically, batch requests and compilations don't have a very nice and handy correlation in ALL scenarios. You need to understand your workload before you start panicking because your counters hit some threshold someone put in a post somewhere. If all of your batches consist of exactly one statement, then yes, having more compilations/sec than batch requests/sec might seem out of the ordinary (but still might not indicate a problem). In most cases, you are sending more than one statement in a batch. If this is the case - and particularly if you are using things like ORMs or a lot of highly variable dynamic SQL, where you will be suffering from a high number of compilations - I would really not be surprised to see one counter higher than the other.

Whether you need to do something about that, in that case, is a completely different problem.