22 January,2013 by Jack Vamvas
How much resource is each SQL database using?. There are different types of resource on the database server. Memory , disk, IOPS, CPU.
The main difficulty with defining the resource usage for a specific database is the wide impact of one query across multiple databases. For example a query with a SORT may spill into the TempDB.
Other examples , are joins and transactions across more than one database
These links to post showing techniques to isolate different resource usage. Analysing the resource usage from different angles , should give you a rough idea of usage . Use this information to make decisions such as – add another database ? or move a database ? or add more resource to the server.
If you’re tuning – tuning at the database level can be frustrating – which is why it’s more effective at the query level .
Measure current read & writes . Do the server drives have capacity?
This one is difficult – I’ve achieved the best results using the DMV - sys.dm_exec_query_stats . Read more on SQL Server – Find high impact queries with sys.dm_exec_query_stats