I am in the process of identifying the most active databases on the server and will be moving the databases off the current server to alleviate the load.
The reason to do it is that our servers are hosting databases for out clients (per client 3 databases - 1 Main, 1 Archive and 1 for Login authentication).
I need to find out what databases are used most interms of CPU, Memory and Disk I/O.
What is the best efficient way of doing it ?
What I have tried :
Look at the buffer pool usage :
- Find the highest CPU consuming databases on the server instance.
- Find databases experiencing disk latency.
My questions are :
- Am I in the right direction ? What other things I can consider, so that moving the databases off from this server will alleviate the load on the server.
- How do I tie up menory usage, CPU and disk latency in figuring out the right database to move off the server ?
Any thoughts/suggestions are highly appreciated.
Below are the specs:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: )
3 sql server instances running
98 GB total physical memory. Max memory is 29GB per instance
Logshipping running every min for Main database and every 15 mins for Archive and Authentication databases.