AI 助理
备案控制台
开发者社区 数据库 文章 正文

SQLIO测试 SAN

2011-09-16 612
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
简介： Downloading and Configuring SQLIO SQLIO Video Tutorial： http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO [Download SQLI...

Downloading and Configuring SQLIO

SQLIO Video Tutorial： http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO

[Download SQLIO from Microsoft]

Notice that I said SQLIO, not SQLIOSIM. Experienced database administrators will often direct you to SQLIOSIM because it's easier to use and mimics SQL Server's disk activity patterns. Here's the catch: it won't necessarily test your SAN to its maximum potential. Your SAN team may indicate that if your SQLIOSIM results aren't fast enough, it's a SQL-related problem, not a SAN-related problem. They may use testing utilities from vendors that mimic results closer to what SQLIO will give you. We want to push the SAN's pedal to the metal and find out how fast it'll go in any situation.

After installing SQLIO, edit the param.txt file and change these two parameters:

  • First parameter - the physical location of the testing file. Change the drive letter to point to the SAN drive you want to test, like T:\testfile.dat.
  • Last parameter - the size of the testing file in megabytes. Increase this to 20480 or larger. Ideally, you want it to be larger than your SAN's cache, because your real databases will be larger than the SAN's cache.

After saving param.txt, run this at the command line in the same directory where SQLIO is installed in order to create the test file:

sqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt timeout /T 10

When it finishes, your test file is created, and it's time to run our real tests.

Testing Your SAN Performance

Instead of picking and choosing individual parameters to use, I like to take the shotgun approach: try every possible combination of random versus sequential, low and high numbers of threads, read versus write, etc. The below batch file will take all of the possibilities and run 'em all. Copy/paste this into a text file called SANTest.bat:

sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kW -t4 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kW -t8 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kW -t16 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kW -t32 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kW -t64 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kR -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kR -t4 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kR -t8 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kR -t16 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kR -t32 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kR -t64 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat

sqlio -kW -t2 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

sqlio -kW -t4 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

sqlio -kW -t8 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

sqlio -kW -t16 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

sqlio -kW -t32 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

sqlio -kW -t64 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

sqlio -kR -t2 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t2 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

sqlio -kR -t4 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t4 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

sqlio -kR -t8 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

sqlio -kR -t16 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

sqlio -kR -t32 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

sqlio -kR -t64 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat

Whew! And that's just one pass - if you want to do multiple passes of the same tests for consistency's sake, like to eliminate the chance that other servers are running on the same SAN and affecting your performance results, you would want to paste that same set of 200+ lines multiple times into your batch file.

Let's take the first line of the batch file and analyze what it's doing:

sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat

The most important parameters are:

  • -kW means writes (as opposed to reads)
  • -t2 means two threads
  • -s120 means test for 120 seconds
  • -dM means drive letter M
  • -o1 means one outstanding request (not piling up requests)
  • -frandom means random access (as opposed to sequential)
  • -b64 means 64kb IOs

Do a find & replace in your text file and replace -dM with the drive letter of your choice. If you're testing on your S drive, for example, you would replace -dM with -dS.

Then go to the command prompt in the same directory as SQLIO is installed and type:

SANTEST.BAT > RESULTS.TXT

This will run our newly created batch file and dump the results into a text file. This will take a long time, like six hours or more, and it will be hammering your SAN. Don't run this on a production server, and don't even run it on the same SAN as a production server when the production server is under load because it may time out. I've had instances where this batch file has actually caused a SAN to restart itself, so use this with caution - preferably in a test lab or before your SAN goes live.

Hours later, when it finishes, you'll have a RESULTS.TXT file with lots of juicy metrics about your storage performance.

Importing SQLIO Results into SQL Server

Those text file results are cryptic - time to bring them into our favorite data analysis platform, Microsoft Access. Wait, I'm kidding, put the axe down - we'll import them into SQL Server.

Script to Create the Tables and and ETL Stored Procedure

Before we start, create a database that you'll use for SQLIO data storage or designate an existing utility database that you'll use. This script requires SQL Server 2005 or newer, since it uses the varchar(max) field.

In that database, run the below script to create the tables for results storage:

001. SET ANSI_NULLS ON
002. GO
003. SET QUOTED_IDENTIFIER ON
004. GO
005. CREATE TABLE [dbo].[SQLIO_Import](
006. [RowID] [int] IDENTITY(1,1) NOT NULL,
007. [ParameterRowID] [int] NULL,
008. [ResultText] [varchar](max) NULL,
009. CONSTRAINT [PK_SQLIO_Import] PRIMARY KEY CLUSTERED
010. (
011. [RowID] ASC
012. ))
013. GO
014. CREATE TABLE [dbo].[SQLIO_TestPass](
015. [TestPassID] [int] IDENTITY(1,1) NOT NULL,
016. [ServerName] [nvarchar](50) NOT NULL,
017. [DriveQty] [int] NOT NULL,
018. [DriveRPM] [int] NOT NULL,
019. [DriveRaidLevel] [nvarchar](10) NOT NULL,
020. [TestDate] [datetime] NOT NULL,
021. [SANmodel] [nvarchar](50) NOT NULL,
022. [SANfirmware] [nvarchar](50) NULL,
023. [PartitionOffset] [int] NULL,
024. [Filesystem] [nvarchar](50) NULL,
025. [FSClusterSizeBytes] [int] NULL,
026. [SQLIO_Version] [nvarchar](20) NULL,
027. [Threads] [int] NULL,
028. [ReadOrWrite] [nchar](1) NULL,
029. [DurationSeconds] [int] NULL,
030. [SectorSizeKB] [int] NULL,
031. [IOpattern] [nvarchar](50) NULL,
032. [IOsOutstanding] [int] NULL,
033. [Buffering] [nvarchar](50) NULL,
034. [FileSizeMB] [int] NULL,
035. [IOs_Sec] [decimal](18, 0) NULL,
036. [MBs_Sec] [decimal](18, 0) NULL,
037. [LatencyMS_Min] [int] NULL,
038. [LatencyMS_Avg] [int] NULL,
039. [LatencyMS_Max] [int] NULL,
040. CONSTRAINT [PK_SQLIO_TestPass] PRIMARY KEY CLUSTERED
041. (
042. [TestPassID] ASC
043. ))
044. GO
045.  
046. CREATE PROCEDURE [dbo].[USP_Import_SQLIO_TestPass]
047. @ServerName         NVARCHAR(50),
048. @DriveQty           INT,
049. @DriveRPM           INT,
050. @DriveRaidLevel     NVARCHAR(10),
051. @TestDate           DATETIME,
052. @SANmodel           NVARCHAR(50),
053. @SANfirmware        NVARCHAR(50),
054. @PartitionOffset    INT,
055. @Filesystem         NVARCHAR(50),
056. @FSClusterSizeBytes INT
057. AS
058. SET nocount off
059.  
060. IF @TestDate IS NULL
061. SET @TestDate = Getdate()
062.  
063. /* Add a blank record to the end so the last test result is captured */
064. INSERT INTO dbo.SQLIO_Import
065. (ParameterRowID,
066. ResultText)
067. VALUES
068. (0,
069. '');
070.  
071. /* Update the ParameterRowID field for easier querying */
072. UPDATE dbo.sqlio_import
073. SET    parameterrowid = (SELECT   TOP 1 rowid
074. FROM     dbo.sqlio_import parm
075. WHERE    parm.resulttext LIKE '%\%'
076. AND parm.rowid <= upd.rowid
077. ORDER BY rowid DESC)
078. FROM   dbo.sqlio_import upd
079.  
080. /* Add new SQLIO_TestPass records from SQLIO_Import */
081. INSERT INTO dbo.sqlio_testpass
082. (servername,
083. driveqty,
084. driverpm,
085. driveraidlevel,
086. testdate,
087. sanmodel,
088. sanfirmware,
089. partitionoffset,
090. filesystem,
091. fsclustersizebytes,
092. sqlio_version,
093. threads,
094. readorwrite,
095. durationseconds,
096. sectorsizekb,
097. iopattern,
098. iosoutstanding,
099. buffering,
100. filesizemb,
101. ios_sec,
102. mbs_sec,
103. latencyms_min,
104. latencyms_avg,
105. latencyms_max)
106. SELECT   @ServerName,
107. @DriveQty,
108. @DriveRPM,
109. @DriveRaidLevel,
110. @TestDate,
111. @SANmodel,
112. @SANfirmware,
113. @PartitionOffset,
114. @Filesystem,
115. @FSClusterSizeBytes,
116. (SELECT REPLACE(resulttext,'sqlio ','')
117. FROM   dbo.sqlio_import impsqlio_version
118. WHERE  imp.rowid + 1 = impsqlio_version.rowid) AS sqlio_version,
119. (SELECT LEFT(resulttext,(Charindex(' threads',resulttext)))
120. FROM   dbo.sqlio_import impthreads
121. WHERE  imp.rowid + 3 = impthreads.rowid) AS threads,
122. (SELECT Upper(Substring(resulttext,(Charindex('threads ',resulttext)) + 8,
123. 1))
124. FROM   dbo.sqlio_import impreadorwrite
125. WHERE  imp.rowid + 3 = impreadorwrite.rowid) AS readorwrite,
126. (SELECT Substring(resulttext,(Charindex(' for',resulttext)) + 4,
127. (Charindex(' secs ',resulttext)) - (Charindex(' for',resulttext)) - 4)
128. FROM   dbo.sqlio_import impdurationseconds
129. WHERE  imp.rowid + 3 = impdurationseconds.rowid) AS durationseconds,
130. (SELECT Substring(resulttext,7,(Charindex('KB',resulttext)) - 7)
131. FROM   dbo.sqlio_import impsectorsizekb
132. WHERE  imp.rowid + 4 = impsectorsizekb.rowid) AS sectorsizekb,
133. (SELECT Substring(resulttext,(Charindex('KB ',resulttext)) + 3,
134. (Charindex(' IOs',resulttext)) - (Charindex('KB ',resulttext))- 3)
135. FROM   dbo.sqlio_import impiopattern
136. WHERE  imp.rowid + 4 = impiopattern.rowid) AS iopattern,
137. (SELECT Substring(resulttext,(Charindex('with ',resulttext)) + 5,
138. (Charindex(' outstanding',resulttext)) - (Charindex('with ',resulttext)) - 5)
139. FROM   dbo.sqlio_import impiosoutstanding
140. WHERE  imp.rowid + 5 = impiosoutstanding.rowid) AS iosoutstanding,
141. (SELECT REPLACE(CAST(resulttext AS NVARCHAR(50)),'buffering set to ',
142. '')
143. FROM   dbo.sqlio_import impbuffering
144. WHERE  imp.rowid + 6 = impbuffering.rowid) AS buffering,
145. (SELECT Substring(resulttext,(Charindex('size: ',resulttext)) + 6,
146. (Charindex(' for ',resulttext)) - (Charindex('size: ',resulttext)) - 9)
147. FROM   dbo.sqlio_import impfilesizemb
148. WHERE  imp.rowid + 7 = impfilesizemb.rowid) AS filesizemb,
149. (SELECT RIGHT(resulttext,(Len(resulttext) - 10))
150. FROM   dbo.sqlio_import impios_sec
151. WHERE  imp.rowid + 11 = impios_sec.rowid) AS ios_sec,
152. (SELECT RIGHT(resulttext,(Len(resulttext) - 10))
153. FROM   dbo.sqlio_import impmbs_sec
154. WHERE  imp.rowid + 12 = impmbs_sec.rowid) AS mbs_sec,
155. (SELECT RIGHT(resulttext,(Len(resulttext) - 17))
156. FROM   dbo.sqlio_import implatencyms_min
157. WHERE  imp.rowid + 14 = implatencyms_min.rowid) AS latencyms_min,
158. (SELECT RIGHT(resulttext,(Len(resulttext) - 17))
159. FROM   dbo.sqlio_import implatencyms_avg
160. WHERE  imp.rowid + 15 = implatencyms_avg.rowid) AS latencyms_avg,
161. (SELECT RIGHT(resulttext,(Len(resulttext) - 17))
162. FROM   dbo.sqlio_import implatencyms_max
163. WHERE  imp.rowid + 16 = implatencyms_max.rowid) AS latencyms_max
164. FROM     dbo.sqlio_import imp
165. INNER JOIN dbo.sqlio_import impfulltest
166. ON imp.rowid + 20 = impfulltest.rowid
167. AND impfulltest.resulttext = ''
168. WHERE    imp.rowid = imp.parameterrowid
169. /*AND (SELECT Substring(resulttext,(Charindex('size: ',resulttext)) + 6,
170. (Charindex(' for ',resulttext)) - (Charindex('size: ',resulttext)) - 9)
171. FROM   dbo.sqlio_import impfilesizemb
172. WHERE  imp.rowid + 7 = impfilesizemb.rowid) > 0 */
173. ORDER BY imp.parameterrowid
174.  
175. /* Empty out the ETL table */
176. DELETE dbo.sqlio_import
177.  
178. SET nocount off
179. GO

The script creates three things:

  • A table called SQLIO_Import where our results will first be dumped from the text file before they're processed
  • A table called SQLIO_TestPass where our results will be permanently stored in a report-friendly format
  • A stored procedure called USP_Import_SQLIO_TestPass. The stored procedure takes the data from our imported text file, parses it, and inserts records into the SQLIO_TestPass table.

The stored procedure expects these parameters, which it uses when inserting into SQLIO_TestPass. None of these have to be formatted a specific way - as long as they fit the SQL Server field definitions, they're fine:

  • @ServerName NVARCHAR(50) - the name of the server you're using for testing. This is mostly useful for servers with locally attached storage as opposed to SAN storage.
  • @DriveQty INT - the number of drives in the array you're testing.
  • @DriveRPM INT - you might be testing 10k and 15k variations of the same setup. I suggest lumping drives into categories - don't try to differentiate between drives that report 10,080 RPM or other odd numbers - just stick with 5400, 10000, 150000, etc. For SSD, I prefer to use a number for the generation of SSD, like 1 or 2.
  • @DriveRaidLevel NVARCHAR(10) - raid 5, raid 0, raid 10, raid DP, etc. (Yes, there are vendor-specific RAID implementations that use letters instead of numbers.)
  • @TestDate DATETIME - the date you're running the tests. I include this as a parameter because sometimes I've run the same tests on a quarterly basis and I want to track whether things are changing over time.
  • @SANmodel NVARCHAR(50) - the type of SAN, such as an IBM DS4800 or EMC CX300.
  • @SANfirmware NVARCHAR(50) - the version of firmware, which can impact SAN performance.
  • @PartitionOffset INT - Windows systems can use DISKPART to offset their partitions.
  • @Filesystem NVARCHAR(50) - usually NTFS. Can be used to track testing different filesystems.
  • @FSClusterSizeBytes INT - the file system cluster size.

Now that our framework is in place, let's import our first round of results.

Importing the Text File into SQL Server 2005

  • In SQL Server Management Studio, right-click on the database where you want to store the SQLIO performance data and click Tasks, Import Data.
  • For Data Source, choose "Flat File Source". Browse to your results.txt file, and set the Format to Delimited, Text Qualifier to None, Header row delimiter to {CR}{LF}, and Header Rows to Skip to 0.
  • Click on the Advanced tab on the left, and there should only be one column, Column 0. Set the DataType to text stream. Click Next.
  • Your database server and storage database should be shown. Click Next.
  • For the Destination Table, choose SQLIO_Import and click Edit Mappings. Set the Column 0 destination to be ResultText. Click OK, and click Next.
  • Click Next until the wizard finishes and imports the data, and then close the wizard.

Open a new query and verify that the data was successfully imported by typing:

1. SELECT * FROM dbo.SQLIO_Import

If there's no rows, something went wrong with the import process. Stop here and troubleshoot. Otherwise, execute the stored procedure to move the data into the reporting table, like this:

01. EXECUTE [dbo].[USP_Import_SQLIO_TestPass]
02. 'MyServerName'
03. ,10
04. ,15000
05. ,'RAID 10'
06. ,'2008/5/6'
07. ,'IBM DS4800'
08. ,'6.62'
09. ,1024
10. ,'NTFS'
11. ,'64000'

The data will be parsed and inserted into the reporting table, which we can query:

1. SELECT * FROM dbo.SQLIO_TestPass

Now, the data is arranged in a way that's easier for reporting.

Analyzing the SQLIO Results

Start by analyzing the data to find the fastest SAN throughput:

1. SELECT * FROM dbo.SQLIO_TestPass ORDER BY MBs_Sec DESC

That column is the SAN's throughput. Notice the LatencyMS_Avg column, though, which indicates the milliseconds of latency. A high throughput number is not necessarily good if the system is taking a long time to respond. Look for five or so of the highest throughput numbers that represents a good balance of high throughput and low latency.

Then, look at the parameters that were used in order to achieve that throughput. Make a note of these, because if you need to do repeated throughput testing on your SAN to test different configurations, you can use these high-throughput configurations to run a much shorter testing pass. Instead of testing your SAN overnight, you can get a quick idea in a matter of minutes because you'll know what parameters tend to drive very high throughput on your SAN. You'll still want to test all parameters when possible, because changes to the SAN may affect how it handles other parameters, but this will give you a quick estimate of whether things are getting better or worse.

More Reading About SQLIO

Here's a few interesting links about other bloggers' experience with SQLIO:

文章标签：
云数据库 RDS SQL Server 版
测试技术
数据库
SQL
Windows
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
fanr_zh
目录
相关文章
吃番茄长大的西红柿
|
5月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
吃番茄长大的西红柿
425 2
游客j4mujezz7vm7y
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
游客j4mujezz7vm7y
36 0
sunrr
|
4月前
|
SQL 存储 监控
SQL Server的并行实施如何优化？
【7月更文挑战第23天】SQL Server的并行实施如何优化？
sunrr
114 13
小王老师呀
|
4月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能，可使用`generate_series`函数生成整数序列，例如：`SELECT value FROM generate_series(1, 10)。此外，`date_bucket`函数能按指定间隔（如周）对日期时间值分组，这些工具结合窗口函数和其他时间日期函数，能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
小王老师呀
74 9
以山向海
|
4月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
以山向海
61 6
小王老师呀
|
4月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符（1-8000），适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符，适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好，空间固定。 - VARCHAR(max) 对于大文本更合适，但可能影响性能。 - 选择取决于数据长度预期和业务需求。
小王老师呀
380 1
VipSoft
|
3月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
VipSoft
477 0
源码星辰
|
4月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统（DBMS）而异，以下将以MySQL、SQL Server和Oracle为例，分别概述其安装部署的基本步骤。请注意，由于软件版本和操作系统的不同，具体步骤可能会有所变化。
源码星辰
308 3
1436047922066202
|
4月前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境： 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库，SQL Server数据库存放在D盘分区中。 SQL Server数据库故障： 存放SQL Server数据库的D盘分区容量不足，管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错，连接失效，SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
1436047922066202
82 1
以山向海
|
4月前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
以山向海
78 2