March 8, 2011 at 11:52 pm
Can anyone point me in a direction how I can figure out how much (cpu) resources every database uses on 1 server?
I know I can use perfmon's 'batchreq/sec' as a good indicator for overall server performance, but i does not show me which database uses most performance.
Perfmon has per db a 'transactions/sec' that can be checked, but is this a reliable indicator?
Thanks for your input,
Rookie 😉
March 9, 2011 at 8:45 am
use the SQL 2008 R2 version of SSMS and right click on the server and then activity monitor
March 9, 2011 at 9:05 am
There really isn't a single measure that tells you what database uses the most resources. How can you decide if a database using a large amount of CPU is doing more work than one doing more I/O? Or one that uses more memory?
Your batch requests/sec is not bad for getting an idea of how much activity is going on, but that doesn't tell you what the relative weight is of that activity. One person running one query can use more resources in a database than 25 people running 100. It all depends on what's being run.
Activity monitor is a good start, and it's in 2008. Don't think it's in 2005.
For other monitoring, I might look at the usage of the server overall, and look for what is using the most resources. Glenn Berry has some good queries, might look here:
http://sqlserverperformance.wordpress.com/2010/02/26/dr-dmv-script-for-rocky-mountain-tech-trifecta/
Is this for some type of chargeback? Or are you looking to move databases on/off this instance?
March 9, 2011 at 11:17 am
thanks already for your input Alen, Steve.
As you mention Steve, it could be an option to move a high-resource to a different server. Now I have no idea which of 20 db's is using most resources, then I don't know if it would help to move a db to another instance. Also, our developers could argument requesting a seperate instance for their db. If I can't measure this I can not counter this.
When looking at batchreq/sec overall, whats the limit to stop adding more db's? 3000 batchreq/sec for a 100Mbit network I read.
We're close to upgrading existing 2005 setups to 2008, then your tips on 2008 ActivityMonitor will maybe help.
Brgds,
T.
March 9, 2011 at 12:51 pm
I might look at the plans in cache, and the transactions by database (Databases object) and compare those a bit.
Ultimately if you want to move the high resource items, you will be taking a bit of a guess on the load.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply