Does anyone have documentation for "right-sizing" an SQL Server 2022 instance on ESXi 7.0 U2?

Posted by Sunsparc@reddit | sysadmin | View on Reddit | 8 comments

I've been put in charge with building out a new SQL server to replace an old one and instead of just assigning the same resources as the old one, they want to right-size it. I've read through a lot of VMWare documentation and it boils down to "use Aria to figure out the usage". I don't have access to Aria, so right now I'm doing my best guess.

Reply to Post

8 Comments

[-]

mark_rees@reddit

You'll really need to work with a DBA. Basically, SQL Server is going to load as much of a database into memory as it can when queries are run. The more of the database in memory, the more responsive queries will be and this will, in turn, make applications more responsive. When there's more data needed that can't fit into memory, disk I/O becomes critical for performance. If you have slow disk access and you hit this, you will immediately see performance of queries drop like a stone. Having data stored in memory is so critical to SQL performance that later versions of SQL server even let you specify certain data tables that will ALWAYS be available from RAM. The other thing to remember is your disk layout. SQL will read and write to multiple files at the same time, so putting it all on the same physical disk can greatly affect performance. This is especially true for things like tempdb, logs, and data files. You can even segment data for the same database into multiple files stored on multiple disks to increase performance. So, if you really want SQL server to run well, there are more questions than just "how much RAM and how many cores do I need?"...Work with a DBA.

[-]

Sunsparc@reddit (OP)

Yeah the problem is the DBA isn't really a DBA. He "has some SQL experience", so I'm basically positing this question to the entire dev team and letting them sort it out. Push comes to shove, I'll just allocate the same resources to the new one as the old one, then detach/reattach the disks on the new one, and let it fly while adjusting resources up and down as needed. On your point about disks, that's already accounted for. Current server has something like 12 disks which each have a separate function for SQL.

[-]

bob_cramit@reddit

Start with 2 cores and 32gb ram and give it more until performance is good enough or you run out of resources. The sql dba will always want more.

[-]

Cooleb09@reddit

Remember if SQL is using all the RAM on the server, you don't have enough and need to add more.

[-]

jcwrks@reddit

Don't forget that more cores equals more money to MS for licensing, so use the least amount possible instead of just tossing more cores into the vm. If you're planning on a db cluster then that's additional costs unless you buy Enterprise from the get go. Make sure you set sql max mem to at least 8GB less than total assigned memory so that the OS won't get bogged down.

[-]

mobz84@reddit

Is the old server to slow? Start low and measure, and go up from there. It is not rocket science, depending of how complex and how much/many people is using the server. Make certain it will not be Slower then the old one, and keep measure some Key Parameters. You could in advance run simulations, do give you a pretty good hint of what to expect. As you do not say that you are not buying any new hardware for the server, you are ultimately locked with what IOPS you have.

[-]

Caldazar22@reddit

This should be a collaborative effort between you and the DBA. Since you already have the existing server running, this should be fairly straightforward. On your side: - Monitor core VM metrics, i.e. CPU utilization, memory utilization, per-LUN read and write IOPS and throughput, and network IO inbound and outbound - On the SQL side, have the DBA track things like cache hit ratios, how long pages are staying in cache, performance of expensive or long-running queries, and other such things - Track over time. If you know there's a month-end job that runs, be sure to capture data over that time period. Capture performance during backup windows. Catch all the intermittent activity that you know about. Having a proper monitoring solution in-place helps here.

[-]

StaffOfDoom@reddit

"Best guess" for a critical SQL system isn't a good plan. Ask whoever 'they' are to get you the proper tools to do the job correctly.