Determine the right size based on the workload

The following are the guidelines on how to monitor the metrics to decide the correct size of the infrastructure. The metrics to be monitored on database server via Performance monitor app on your system when the workload is in progress.

• CPU Usage

• Network (in/out traffic)

• Data, Log, and TempDB disc drive IOPS and Throughput
  (it is observed that the IOPS and Throughput load is more on the TempDB).

Based on the performance monitor readings,

If the CPU average usage is more than 80% continuously when typical workload is in progress, consider upgrading the CPU to next level.

• If the IOPS and Throughput usage is more than 80% of the average, then upgrade to the next level.

Use the SQL Server Query Store

If you are using SQL Server 2019, you can use the SQL Server Query Store to identify the resource constraints.

In the Query Wait Statistics, if:

• The query is waiting on CPU /memory, then you may have to increase the CPU size to the next level.

• The query is waiting on other disk I/O, then check the usage of IOPS and Throughput of the individual drives that contain the Data, Log, and TempDB. If it is under pressure, you may have to increase the IOPS and Throughput of that disk drive. It is mostly observed in the TempDB drive.