(for context, I'm a developer, not a DBA)
- 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
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?
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.
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.