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.
Below are some of the steps you could take to determine the disk performance of your new hardware.
Run DBCC DROPCLEANBUFFERS to ensure data is not resident in memory
Do a SELECT COUNT(*) query using the clustered index of a table that is spread across the LUNs you want to test
Run DBCC DROPCLEANBUFFERS again to ensure data is not resident in memory
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.
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.
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:
CPU usage (on client and server)
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.