1

Environment:

  • Azure Windows 2016 VM with SQL Server 2016 Standard installed
  • Machine Size: DS12_v2 (previously DS3_v2, which is the same except for less RAM)
  • 4-cores
  • 28 GB RAM
  • 14 GB RAM for SqlServer
  • Premium Storage
  • Data files on P40
  • Log files on P30
  • tempdb on P10

Every night we perform backups of our production system and then restore them to the troubled server. Backups are compressed. Everything had been running fine until a few weeks ago when we started receiving buffer latch (type 2) errors during the restore process. These are accompanied by "I/O requests taking longer than 15 seconds to complete" messages as well. Those messages are on various databases.

A time-out occurred while waiting for buffer latch -- type 2,...

SQL Server has encountered 4 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [{myPath}\ReportServer.mdf]...

The vast majority of the buffer latch issues are reported against [ReportServer] or [ReportServerTempDB] but a few have come against [tempdb].

I created a ticket with our Azure support folks who reported that our log disk and VM are both being throttled due to throughput limits and that is why we are seeing these messages.

What I am confused about is to why all of the sudden this would start happening, why SQL would suddenly try to push more data through during the restore than it had previously forcing Azure to throttle back. Our databases do not grow that much in size, as we have archiving in place for our more active tables, and we restore the databases one at a time. Unfortunately, we do not have a tracking mechanism in place to monitor data and log file space utilization.

No changes were made to the VM or SQL Server in the 3 weeks prior to this starting to happen, including installing updates.

QUESTION:

I know that sometimes SQL query performance can "fall off a cliff" even with the smallest change to the dataset that it is consuming, but is the same type of thing possible for I/O? Could it be that a little bit of growth pushed the server to the point where it is trying to push more data through than it did on previous restores?

Things I Have Tried:

  • Disabling all SQL CEIP services
  • Added more RAM (was actually to resolve memory pressure in SSRS)
  • Added exclusions to Windows Defender for the data, log, and tempdb folders as well as the sqlserver process.
  • Changed execution time of daily job [syspolicy_purge_history_schedule] to run well before our backup/restore processes.

!! EDIT:

As an additional piece of information, all of our log files have at least 90% free space and our data files have a minimum 25% free space, as we grow them out ahead of time to minimize autogrowth events. So when I was thinking that maybe data growth could be a culprit, I am not so sure now. The size of the files that this process has been restoring would have been the same for a few years now.

0

Given the fact that we have no history of SQL file usage over time, I can only assume that we crossed some line as far as file space consumption that is forcing SQL to try to push more data through than it was before. This is not a solid answer, but all I feel anyone can offer given our situation.

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.