I/O requests taking longer than 15 seconds to complete

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » I/O requests taking longer than 15 seconds to...

23 posts, Page 1 of 3

1 2 3 »»»

I/O requests taking longer than 15 seconds to complete

Rate Topic

Display Mode

Topic Options

Author

Message

Markus

Posted Tuesday, November 06, 2012 7:17 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 1,046, Visits: 2,212

This is a Win2008R2 SQL2008R2 SP2 active/passive cluster.

I am getting these messages when I run an UPDATE STATS on a large table consistently. SQL Server has encountered 907 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [V:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb2.ndf] in database [tempdb] (2). The OS file handle is 0x00000000000011D0. The offset of the latest long I/O is: 0x00000139500000

The storage guys have specifically showed me that the storage is responding to these requests in less than 10ms so it isn't the storage. I know it isn't the storage so it has to be something in between but what? Anyone have this issue or have ANY ideas of what I can do to further narrow down what is causing this? I have four other SQL Servers with this issue as well... however, we have some quite sizeable SQL Servers here that do not have the problem so it isn't for every one.

Any help would be appreciated.

Post #1381559

GSquared

Posted Tuesday, November 06, 2012 8:24 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 15,442, Visits: 9,571

I recently got a bunch of those, that were caused by a network issue in the connection to the SAN.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1381598

Markus

Posted Tuesday, November 06, 2012 8:58 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 1,046, Visits: 2,212

Interesting. However, the SAN team tells me.. if we had problems with the SAN or connectivity many other larger systems not on SQL Server would be complaining up and down and there has been none of that.

Post #1381617

Perry Whittle

Posted Wednesday, November 07, 2012 4:28 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 5,201, Visits: 11,153

What monitoring stats have you collected so far to supply to your SAN and infrastructure teams.

In between the SAN and the SQL Server you have all the other software\hardware components, these all need to be checked. Capture disk latency and throughput as a starter and discuss these with the SAN\Infrastructure teams.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1381908

Markus

Posted Wednesday, November 07, 2012 4:39 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 1,046, Visits: 2,212

When I run Update Stats on large tables and Integrity checks is the only time I see these. In Activity Monitor the DataFile I/O Response time in MS goes way up in spikes. I have shown them this. However, they show me the monitor from the SAN showing disk response of 8MS and under. They say the SAN is performing great and other Oracle and ESSBASE systems that hit the SAN harder are not having any issues. I just don't know what else to show them to convince them there is an issue and what to even look at.

Post #1381909

Perry Whittle

Posted Wednesday, November 07, 2012 5:50 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 5,201, Visits: 11,153

As i said, you have all the components in between the SAN and the SQL instance.

HBAs
HBA drivers
multipath drivers
Fibre channel switches
Etc, etc

Initially, check the Queue depth settings on the HBA(s)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1381926

Markus

Posted Wednesday, November 07, 2012 5:55 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 1,046, Visits: 2,212

Perry Whittle (11/7/2012)

They told me they checked all of that and all was OK....

Post #1381929

Charles Hearn

Posted Wednesday, November 07, 2012 6:32 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 557, Visits: 4,821

Have a look at the URL below.

http://www.brentozar.com/sql/sql-server-san-best-practices/

I would highlight the following section

http://www.brentozar.com/archive/2009/05/san-multipathing-part-2-what-multipathing-does/

Post #1381946

Markus

Posted Wednesday, November 07, 2012 6:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 1,046, Visits: 2,212

Thanks for the ammunition. I will see what I can find out about this.

Post #1381950

Markus

Posted Wednesday, November 07, 2012 7:02 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 1,046, Visits: 2,212

So I am using PerfMon to monitor

physical disk performance Obj

avg dis sec/read counter
avg disk sec/write counter
avg dis sec/transfer counter

based on this article:
http://blogs.technet.com/b/askcore/archive/2012/02/07/measuring-disk-latency-with-windows-performance-monitor-perfmon.aspx

but I cannot figure out what the information is telling me.... what is good and what is bad to take to our SAN/Server engineers.....

Can anyone help with this?

Post #1381963

« Prev Topic | Next Topic »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions