Baseline your SQL Server
Have you ever got a new server (physical or virtual) and it just hasn't performed as you expected. It looks like it has all the right configurations, but performance is not up to scratch compared to other servers. It might be CPU, disk or memory, but it shouldn't be used until you are satisfied by its' performance. But how can you demonstrate that a server is not performing unless you have baselines.
As part of a large migration project from SQL Server 2000 to SQL Server 2012 we had to produce a performance baseline on every server (non-prod and prod). Luckily we had the Quest Benchmark Factory which we used to run TPC-C, TPC-E and TPC-H tests to information on Max Server throughput userload, TPS, Avg Response Time and Avg Transaction Time. This was run against a number of different server specifications. This allowed us to create a baseline of acceptable performance levels for each server specification.
The next baseline we needed was for IO.
We used the SQLIO Disk Subsystem Benchmark Tool.
This tool has now has been retired and replaced by DiskSpd.exe
Once again we ran a number of tests for the different server specifications . Each test with different threads, run times, outstanding threads and block sizes. There would be different param.txt files for the number of threads and the tests would change for the number of threads. The number of threads equals the number of vCPUs on the VM.
Example Param.txt files 4 threads with a data file size 24576MB (24GB).
D:\SQLDATA\SQLIOTest.dat 4 0x0 24576
Sample of tests run for 4 threads, 300 seconds, 8 outstanding threads, block sizes 8K and 64K.
sqlio -kW -t4 -s300 -o8 -frandom -b8 -BH -LS -FParam.txt
sqlio -kR -t4 -s300 -o8 -frandom -b8 -BH -LS -FParam.txt
sqlio -kW -t4 -s300 -o8 -fsequential -b8 -BH -LS -FParam.txt
sqlio -kR -t4 -s300 -o8 -fsequential -b8 -BH -LS -FParam.txt
sqlio -kW -t4 -s300 -o8 -frandom –b64 -BH -LS -FParam.txt
sqlio -kR -t4 -s300 -o8 -frandom –b64 -BH -LS -FParam.txt
sqlio -kW -t4 -s300 -o8 -fsequential –b64 -BH -LS -FParam.txt
sqlio -kR -t4 -s300 -o8 -fsequential –b64 -BH -LS -FParam.txt
These tests were also run against the different storage offerings that were available through the SAN vendor. This allowed us to create a IO baseline of acceptable performance.
It was a lot of work, but it ensured each server met a minimum performance baseline.
With these metrics you could then go back to the infrastructure team with facts.