• Adam Thurgar

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

https://www.microsoft.com/en-au/download/details.aspx?id=20163

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.


6 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t