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

  • 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.


    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.





    Sunday, October 23, 2011 3:53 PM

All replies

  • Any one willing to help...please?
    Wednesday, October 26, 2011 12:01 PM
  • Hi Ashish,

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


    Thursday, October 27, 2011 8:26 AM
  • 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.

    Friday, October 28, 2011 2:03 PM