Mesure/Calculate IOPs in the production server?
Question
Tuesday, January 14, 2014 12:20 AM
| We have production SQL Server and need to measure average IOPs so I can say to possible SAN vendors what is the minimmum IOPs for my server. - What is the calculation/measurement procedure: Can I use PerfMon and if I can what are the specific counters and how to use it to make the calculation - Can I use any other tool for mesurement - Or there are many other factors that are affecting on the server performance. Simple: SAN vendor asks us what RAID we want and what number of IOPs we need for our SQL Server. |
ApolloDB
All replies (5)
Tuesday, January 14, 2014 12:48 AM ✅Answered | 2 votes
Hello,
You can use perfmon and add the Disk Transfers/Sec counter. This counter is the number of Read and Write operations that have taken place per second for the last x sample time. Alternatively you could add the reads/sec and writes/sec and sum them, that would also give you a good idea on workload split.
It is also possible (for the most part) to do this inside of SQL Server. The sys.dm_io_virtual_file_stats() DMF can be recorded and then at time T intervals or some other time later it can be recorded. Differencing them would give you the number of IOPS (when you calculate it out) per database, per file.
Sean Gallardy | Blog | Twitter
Tuesday, January 14, 2014 3:31 AM ✅Answered
Read another article written by Brent http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Tuesday, January 14, 2014 2:43 AM
Sean, that's a great response, using perfmon and virtual_file_stats.
I also just saw this excellent writeup:
http://www.brentozar.com/archive/2013/09/iops-are-a-scam/
I'd add one more thing, that the high-end SANS have all these tiered IO tricks, moving files from disk to SSD to RAM, so they can deliver humongous IOPS for certain benchmarks and not so much others.
I guess IOPS always matter at some point (especially logs and tempdb) but my mantra for SQL Server remains, "More RAM More RAM More RAM!"
Josh
Tuesday, January 14, 2014 3:25 AM
Josh,
it's becomes even more convoluted than that. For example, IBM sans can have SVC's (San Volume Controllers) that have SSDs and RAM local for caching. That's in addition to the SAN Ram that also does caching... Add the automatic tiered storage with it, or like with an XIV where it constantly shifts everything around and you really don't have too much control over things.
In terms of Brent's post, he's correct but makes it sound like it's not worth anything. You can calculate the average latency and IOPS size form the DMVs, perfmon also has those counters which would be more accurate. In terms of SLQ Server we're going to have things like 8k, 64k, 128k sizes. Since most IOPS are given in terms of 4k sizes this, overall, does little to help us. Couple that with the items from perfmon and we get a slightly clearer picture. In the end, though, we ultimately have no control over this so using IOPS as a best guess is ideal over someone saying "Well, gee, I dunno... enough so it runs fast"
Sean Gallardy | Blog | Twitter
Thursday, January 16, 2014 7:33 AM
Thanks for the help. I managed to find the needed information.
ApolloDB