SQL server disk I/O related question

Question

SQL server disk I/O related question

Oracle_91

SSChampion

Points: 14548
More actions
April 3, 2014 at 5:42 am

#290000

Hi Experts,
I have a question on disks.
How much I/O can a normal disk and a SAN disk can handle?
I have heard about SAN disks should be able to handle upto 200-300 mb/sec, how much does a normal disk can handle or do we need to decide this based on baseline values?
I wanted to know this because while troubleshooting sql server I/O issues , I want to isolate if SQL Server is posting too much of that a Disk is not able to handle that much load or a disk (I/O path) itself is bad so that I an engage storage team.
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

Check the following SQL Server perfmon counters to find any I\O issues with the storage system. Are you experiencing any IO issues ?

Avg. Disk sec/Read

Avg. Disk sec/Write

Current Disk Queue Length

http://www.mssqltips.com/sqlservertip/2460/perfmon-counters-to-identify-sql-server-disk-bottlenecks/

--

SQLBuddy

Oracle_91 SSChampion Points: 14548 More actions · Answer 2

Hi sqlbuddy,

Thanks for reply.

I wanted to setup monitoring for I/O issues.

Are there any benchmark values of how much a normal disk can handle and how much a SAN disk can handle?

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Oracle_91 (4/3/2014)
Hi sqlbuddy,
Thanks for reply.
I wanted to setup monitoring for I/O issues.
Are there any benchmark values of how much a normal disk can handle and how much a SAN disk can handle?

I think by normal disk you meant DAS (Direct attached Storage). SAN is storage on Network. Both has their own pros and cons.

To check how much load they can handle, you should do I\O testing using SQLIO tool prior to SQL Server installation.

But when it comes to SQL Server benchmark, Check those counters.

Avg. Disk sec/Read

Avg. Disk sec/Write

If they are < 10ms then you are have a good storage subsystem.

--

SQLBuddy

Oracle_91 SSChampion Points: 14548 More actions · Answer 4

Yeah. I mean normal disk as DAS.

Thanks for pointing out of SQLIO tool.

Is there anyway I can import results of sqlio and load it into sql server table and perform aggregations to get a report in an understandable format ?

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

Oracle_91 (4/3/2014)
Yeah. I mean normal disk as DAS.
Thanks for pointing out of SQLIO tool.
Is there anyway I can import results of sqlio and load it into sql server table and perform aggregations to get a report in an understandable format ?

Check the following .. I like the 1st one better

http://blogs.technet.com/b/sqlpfeil/archive/2012/12/04/working-with-sqlio-and-analyzing-it-s-output.aspx

http://www.toadworld.com/platforms/sql-server/w/wiki/10406.san-performance-tuning-with-sqlio.aspx

--

SQLBuddy

Oracle_91 SSChampion Points: 14548 More actions · Answer 6

Hey, thank you very much.

I would go with second one which is from Brent ozar. I am much more comfortable with TSQL than Excel stuff. 🙂

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 7

You are welcome, Oracle_91. Sounds good to me 🙂

--

SQLBuddy