Very high IO_COMPLETION waits, but ms/Read, ms/Write counters under 10 ms

Question

Post reply

Very high IO_COMPLETION waits, but ms/Read, ms/Write counters under 10 ms

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
August 12, 2011 at 12:39 pm

#242135

I'm having a very peculiar problem.
An hourly job creating a database snaphot from a mirror started taking a lot longer to complete last night: 30-40 minutes from about 1 minute previously.
I noticed that the job was waiting exclusively on IO_COMPLETION waits.
However, the ms/Read, ms/Write counters were at normal levels: under 10-20 ms.
I have been having a lot of trouble convincing our server admins that there is something wrong.
What proof can I provide them with, other than some obscure waittype that only DBAs understand?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

40X slower is not enough for them?

sp_update_resume, sp_distribute.

In the mean time, what changed on that server or the db sine the last successful 1 minute exec?

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

Ninja's_RGR'us (8/12/2011)
40X slower is not enough for them?
sp_update_resume, sp_distribute.
In the mean time, what changed on that server or the db sine the last successful 1 minute exec?

The hourly snapshot-refresh job is now mysteriously back at normal levels: < 1-2 min

I will be monitoring for any other activity on the server, as this is a VM and there may have been a monstrous SQL query running for a report that caused this.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You can get sql monitor for free for 14 days, that should give you your answer...

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

Ninja's_RGR'us (8/12/2011)
You can get sql monitor for free for 14 days, that should give you your answer...

Thanks, we are using SQL Sentry and IGNITE for monitoring. But thanks for the input.

Marios

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

And they can't tell you what happened or what's hapenning during the issue?

Thanks for the tip!

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

Ninja's_RGR'us (8/12/2011)
And they can't tell you what happened or what's hapenning during the issue?
Thanks for the tip!

Not fully; SQL Sentry is showing the high IO_COMPLETION waits and the perfmon counters at normal levels, as mentioned in my OP.

This was independently confirmed through some of my own DMV queries and perfmon collection.

But 3rd-party tools can only go so far; to dig deeper, one needs to have the knowledge and expertise these tools cannot provide.

I do have some indication from SQL Sentry that some pretty heavy queries were running on the server at the time, but I started the monitoring fairly late in the process; if this happens again, I will have more historic info to go with.

Most likely, this is an issue of unusually heavy resource contention.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ok, if you have 2 minutes you can see what SM has to offer (live previous of the servers hosting ssc.com)

http://monitor.red-gate.com/?utm_source=ssc&utm_medium=peel&utm_content=corner_peel201104&utm_campaign=sqlmonitor

aaron.bertrand Ten Centuries Points: 1260 More actions · Answer 8

Marios, what version of SQL Sentry are you using? With 6.x you should certainly be able to dig deeper into the individual queries where IO_Completion is high - for example the new query analysis features should let you drill in to the costly operators and help you spot potential statistics or other issues leading to sub-optimal plans.

However keep in mind that IO issues may just be a symptom of an overwhelmed disk subsystem - it's not necessarily going to lead you to a quick solution based on SQL or application code. Unfortunately if you are using a SAN 3rd party tools for the most part can only see so far into the disk subsystem... if there truly is a configuration or resource issue with the SAN, and it's not just because of a spike due to known activity variance, it is likely going to require expert assistance.