Server segregation

Question

Experts,

I have one VM with MS-SQL hosted on it having 24 databases on it each one connected to their applications respectively. Based on requirement have to segregate the 24 databases onto 3 different SQL Instances on 3 different VM's , 8 each. Is there a way or formula to calculate the compute capacity for the 3 VM's(CPU and Memory) to avoid contention , as different databases have different workloads

Answer 1

Thanks Carrin. Taking into consideration How to analyze SQL Server database performance using T-SQL for CPU, Memory and disk script output , some more guidance :

1. For the Query : CPU – Identify workload -> can we say that the database MES_DB2_PROD is utilizing the max CPU and hence the max CPU is to be aligned to the VM where this database is hosted. On a very high level say the initial 1 VM has 12 vcores , the maximum core VM would be the one having MES_DB2_PROD database.
2. For the Query : Buffer pool memory management -> The memory utilized defined by the BufferPageCnt. Can we sum up the BufferPageCnt and divide it on the basis of databases to calculate the memory utilized on a high level.
3. Disk I/O Management : Defines the disk utilization , but is there a way to calculate the IO's required to calculate the requirement

Answer 2

Have a look at SQL Governor. This is a product that could help you.

Note: I am not affiliated with this company, but I know their CTO and I have friendly relations with him.

Answer 3

Hi @Vishu ,

Welcome to Microsoft Q&A!

For SQL Server, you could get the script from SQL Server workload script and How to analyze SQL Server database performance using T-SQL to calculate each database, and according to this result you could estimate the compute capacity for the 3 VM's. And you could get the result like below:

If I have misunderstand your question, please let me know.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.