Microbenchmarking

Microbenchmarking is the art of testing your machine's basic configuration like disk latency, memory latency, CPU performance using series of tests. You can find more details at:

Types of benchmarks

How do you assess the performance of a new machine with SQL Server? Do you run industry standard benchmarks like TPCC and TPCH? If you cannot run those yourself then what are the tests that you run. What are some of the tests that can can assess metrics like disk IO performance, CPU speed or memory speed? You could use simple TSQL batches to access some of the metrics without running your workload. Below are some of the simple tests.

Disk Performance

Below are some of the steps you could take to determine the disk performance of your new hardware.

1. Run DBCC DROPCLEANBUFFERS to ensure data is not resident in memory
2. Do a SELECT COUNT(*) query using the clustered index of a table that is spread across the LUNs you want to test
3. Run DBCC DROPCLEANBUFFERS again to ensure data is not resident in memory
4. Next do a SELECT COUNT(*) query from the clustered index of a table that is spread across the LUNs you want to test, but now with a DOP 1

Memory Subsystem Performance

To determine the performance of your memory subsystem, you can run the query with MAXDOP 1 as referenced in step #4 above after all of the data you scan is resident in memory. This will give you the memory subsystem performance.

CPU Performance

To determine the CPU difference, you can select the same single row from a table in a loop or do some simple increment operation in a loop. This should also be done when all of the data you scan is resident in memory.

Network Performance

To determine the network latency, you can run the same CPU test from a remote client and send requests from the client in a tight loop.

During all of these tests, monitor the following using the PerfMon counters:

1. Response Time
2. CPU usage (on client and server)
3. IO throughput
4. Network throughput

These are simple tests to perform and metrics to keep track of could be a valuable tool in your toolkit. Each individual test above and also in comparison with each other constitutes a micro benchmark and could be a useful guide in optimizing queries and making tradeoffs.
One test missing form above is something that tests seek performance of the disk subsystem. There are several tests that can be added.

What other tests do you use and why? What metrics do you keep track of? Please share them with us and the rest of the SQL community.

-- Ashit

Comments

• Anonymous
  September 17, 2008
  I am interested in measuring the network latency and so I used this construct: SELECT TOP 1 Name from Employees Is this really enough? Can you elaborate on a client side script for this type of test?