January 9, 2012 at 11:40 pm
Hi Experts,
I’ve just started in a new role, for a company that is using a 3rd party product for our key systems. For the first time in nearly 20 years working with SQL, I am a 'team' of 1, with Network maintenance provided by a 3rd party. There are numerous configurations that I need to question, but the one troubling me is – how can I determine the number of IOPS that SQL is generating?
The databases reside on a SAN, that is not ideally configured [there are many logical drives all mapped to the same physical disks, which are in a RAID 5 array]. What I would like to provide are some figures detailing the transaction throughput, instead of just saying ‘trust me, the disk architecture sucks’ so I can start conversations around the fact that we need to revisit the disk configuration.
The DMV sys.dm_io_virtual_file_stats contains details on the number of Reads & Writes per file, would these figures equate to IOPS?
We are using SQL 2008.
Thank you for your advice,
January 10, 2012 at 1:54 am
Hello,
You can use both read/sec and write/sec available from Physical Disk and Logical Disk counter sets.
Have a look here for extra guidance and information:
http://blogs.technet.com/cotw/archive/2009/03/18/analyzing-storage-performance.aspx
http://technet.microsoft.com/en-us/library/cc300400.aspx
Note: be careful interpreting the results correctly depending upon the type of storage: DAS, SAN, RAID... See details in the links above
January 10, 2012 at 11:29 am
Get SQLIO out and push the storage to see what you can and cant get out of it. this forms the base of what you can provide performance wise
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply