SQL 2016 Optimization ...
-
Hello, I have two SQL 2016 Always On nodes - (VMware Virtual Machines). Each node has 250GB RAM, 46 vCPU @2.5GHz. Each of the MS SQL VM is dedicated to 1 ESXi Host so there is no resource contention. The application that would connect to the Database is expected to be: 70% WRITE and 30% READ. I need advise on the best way to: (1.) Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally. (2.) Perform Write and Read as fast as possible using all the hardware resources. (3.) I have configured Always On Read Routing, how can I test it? Thanks.
-
Hello, I have two SQL 2016 Always On nodes - (VMware Virtual Machines). Each node has 250GB RAM, 46 vCPU @2.5GHz. Each of the MS SQL VM is dedicated to 1 ESXi Host so there is no resource contention. The application that would connect to the Database is expected to be: 70% WRITE and 30% READ. I need advise on the best way to: (1.) Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally. (2.) Perform Write and Read as fast as possible using all the hardware resources. (3.) I have configured Always On Read Routing, how can I test it? Thanks.
-
Hello, I have two SQL 2016 Always On nodes - (VMware Virtual Machines). Each node has 250GB RAM, 46 vCPU @2.5GHz. Each of the MS SQL VM is dedicated to 1 ESXi Host so there is no resource contention. The application that would connect to the Database is expected to be: 70% WRITE and 30% READ. I need advise on the best way to: (1.) Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally. (2.) Perform Write and Read as fast as possible using all the hardware resources. (3.) I have configured Always On Read Routing, how can I test it? Thanks.
Member 13806085 wrote:
Perform Write and Read as fast as possible using all the hardware resources.
"As fast as possible" will take some experimenting and reading, will depend on your data-structure, indexes, triggers, and a bunch more - a bit much for a single forum-post.
Member 13806085 wrote:
I have configured Always On Read Routing, how can I test it?
Check if the routing-URL is invoked and run a SQL-trace. Configure Read-Only Routing for an Availability Group (SQL Server) | Microsoft Docs[^]
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
