My SQL Server Data Warehouse Environment needs to be bench marked for Maximum Throughput (MB/sec)
Server: HP DL585 G7 (4P * 12 C) = 48 cores
SAN : EMC VNX5300 (32 * 300 GB 15K disks)
Using SQLIO, I could measure the throughput of the SAN as 2.2 GB/sec.
I would like to measure the maximum throughput that SQL Server can generate by running IO intensive queries. I did a BACKUP to NULL file and could get 1.7 GB/sec. But I want to get the MB/sec that the queries would generate.
1) How to measure the throughput (MB/sec) of a single query?
2) What kind of queries do i need to write to generate a workload of 2GB/sec. Any samples on Adventureworks would also be appreciated.
Please check this article http://sqlserverperformance.idera.com/io-performance/measure-exact-performance-sanraid-array-giving-sql-server/ addressing your question.
AdventureWorks isn't large enough to use to see the throughput available on the SAN. Can you just restore the database onto the server and then run your normal queries on it?
The throughput you will see greatly depends on what type of workload you are running and how your tables are structured. What I typically do when using SQLIO to verify SAN related infrastructure is test the following:
REM test write performance sqlio -kW -t32 -s300 -dV -o1 -frandom -b64 -LS -BH -Fparam.txt >mixed_write.log REM test sequential read throughput sqlio -kR -t32 -s300 -dV -o1 -fsequential -b256 -LS -BH -Fparam.txt >read_throughput.log REM test random IO with 64KB IOs sqlio -kR -t32 -s300 -dV -o1 -frandom -b64 -LS -BH -Fparam.txt >read_random_64k.log REM test random IO with 24KB IOs sqlio -kR -t32 -s300 -dV -o1 -frandom -b24 -LS -BH -Fparam.txt >read_random_24k.log
You'll want to increase the number of threads to match what you think a typical DOP will be for a query. You'll also probably want to change the -d to match your set of drives.