We are experiencing high disk I/O latency in a specific timeframe in SQL Server. Every day at exactly 4:30 AM we see a significant difference in disk I/O and it lasts up to 6:30 or 9:30 AM. I already checked with the development and storage teams. They confirmed that everything is running normally. Storage team declared that there is no issue in LUN and they are blaming SQL Server.

One thing is sure--some job is triggering exactly at 4:30 AM every day, which causes the high disk I/O. But I am not sure how to find that out. I attached the screen shot of the SQL error log at 4:30 AM. All the monitoring tools and the logs are stating that the transactions are taking longer that usual due to high disk I/O. But nowhere is it mentioned what is causing it.

Ours is 3 node Always On environment. P1 - primary, P2 - Stand only, P3 - Read only.

The issue we are facing now is from P3 (Read only node).

Screen shot of the SQL server log

  • 1
    Run an extended event trace to see what is running? – LowlyDBA Feb 7 '18 at 3:49
  • 1
    Are these servers VMs? – Sean Gallardy - Retired User Feb 7 '18 at 3:51
  • What version of SQL are you using? SELECT @@VERSION – StrayCatDBA Feb 7 '18 at 20:01
  • Did you find the offending process(es), Damodara Lanka? – Stephen Falken Feb 9 '18 at 2:33

This is too long for a comment, but there won't be enough in here to provide the answer as to WHAT is causing this, just HOW to work out what IT is.

Firstly: What applications are connecting to databases on that server, and are they running batch processes against databases that have grown faster than you've kept track of, thus extending the time required to complete?

Are any backups being taken at this time? Context switching is really high, so there could be backups conflicting with batch processes running at the same time.

Here's an example of how you could capture the job/query causing your issues:

  1. Download the WhoIsActive stored proc by Adam Machanic from http://whoisactive.com/. Run it against an Administration database you might have on the server, or in the master database.
  2. Create a table that can capture the output of WhoIsActive to a table (http://whoisactive.com/docs/06_options/)
  3. Create a SQL Agent job that runs executes sp_WhoIsActive every 15 seconds (this is my preference, change to suit yours) and log the data to the table you created.
  4. Create a set of PerfMon counters (using https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/) on the SQL Server and log the data to CSV.
  5. If the server is a VM, ask your VM Admin for server stats for the given period. vRealize Operations contains a lot of useful reports for proving a point.
  6. Get some reports from your SAN Admin showing SAN activity during the problem period.
| improve this answer | |
  • Agreed, particular on #5 and #6 - don't accept a simple "Not us", ask for copies of their evidence that it's not them. In return, when they ask, supply them with your evidence that it's not you. Also, kick off an extended events trace for that time period. Check your Windows scheduler and your agent logs. Optionally have a batch file run SQLIO for 2 seconds every 30 seconds during the affected timeframe and just before and just after. Also, ask of the network admins the same thing; maybe there's iSCSI or something involved and it's network issues. – Anti-weakpasswords Feb 7 '18 at 5:58
  • I've always found #4 useful for refuting the "Not us". "Logical Disk" tells no lies. – StrayCatDBA Feb 7 '18 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.