Achieve/Measure Maximum SQL Server Query Throughput (MB/sec)

Question

HI All,

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.

Questions:

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.

 

Thanks,

Ashish

 

Answer 1

Any one willing to help...please?

Answer 2

Hi Ashish,

Please check this article http://sqlserverperformance.idera.com/io-performance/measure-exact-performance-sanraid-array-giving-sql-server/  addressing your question.

thanks,
Jerry

Answer 3

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.