0

(for context, I'm a developer, not a DBA)

Environment

  • Azure Windows VM
  • SQL Server 2016
  • Windows Server 2016 Datacenter
  • 14GB RAM on machine, SQL set to use up to 8GB, but never gets over 2GB
  • Application DB Size 1.3GB
  • TempDB size grows to 400MB
  • all sql db data files (inc tempdb) on one disk and log files on another, disks are SSD
  • This is an operational web app that is used during the 6a - 4p work window M-F.

Summary of Problem

  • In the morning, when activity jumps on the app, tempdb disk bytes IO skyrockets, slowing down db response times for queries, some taking as long as 30sec!
  • Restarting the server fixes issue, and disk bytes IO stays low the rest of the day

Details

I have an issue that popped up only recently in the past couple of weeks with users complaining of our web application running slow. Investigation leads me to believe it is from high tempdb disk I/O. I'm seeing peaks like 22MB/s total (read/write). Disk queue lengths develop, etc.

A restart of the server brings it back down to almost nothing, and stays that way until the following morning, when everyone logs back in and activity jumps.

We do have 2 stored procs that explicitly use #temptables, but I'd think if they were the problem, the high I/O would continue after a restart, but it doesn't.

Any ideas on what type of issue would manifest itself in this manner?

Various Screenshots

This is queue length. Notice where I restarted the server and it stayed low after that until the next day (today). I did not restart today, and you can see, though not peaking as much as yesterday, is still relatively high, and is impacting user experience. enter image description here

enter image description here

This shows the severer response time yesterday during the peak I/O period in the 6a hour. The red lines show the time that I restarted the server. You can see the response times dropped, as did the disk I/O in the previous images, but user requests are the same, so it is not an overall workload issue. Server Response vs Requests

Wow, 8GB of disk I/O? enter image description here

enter image description here

UPDATE 1

Just looked at this...seeing a lot of locks, so maybe other activity is spilling to TempDB? Will look into locks now... enter image description here

  • 5
    I think focusing on the restart is the wrong angle here. You need to use a tool like sp_whoisactive when the database is slow to see what is running, blocking, taking up resources, etc. After you find them, check this post to see how to best ask a performance related question. – LowlyDBA Nov 8 at 20:16
  • 2
    you should try to run sp_blitz or sp_blitzfirst when it's happening instead of restarting the server : brentozar.com/blitz – Danielle Paquette-Harvey Nov 8 at 20:41
  • 2
    @DaniellePaquette-Harvey - thanks, I know restarting was just addressing the symptom, but had to in order to get things back online for the users and wasn't sure what to look at next. I'll take a look at those 2 sp's. – Chad Richardson Nov 8 at 21:25
  • 1
  • 1
    It should be evident in the query plans that are driving the TempDb utilization. – David Browne - Microsoft Nov 9 at 19:17

Your Answer

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

Browse other questions tagged or ask your own question.