We have a SQL Server 2017 (RTM-CU15) AG group (3 servers, 2 HA synchronous) that has had performance issues for the past two months. During the resolution of an issue yesterday we found that the instance was struggling with the tempdb, as per the logs:
A time-out occurred while waiting for buffer latch type 2, bp 000001C9A41D4A40, page 4:40616, stat 0x810b, database id: 2, allocation unit Id... Not continuing to wait
There were no mention of storage issues in Event Viewer system logs.
Upon inspection, we found that tempdb had 8 data files each of 250MB (2GB in total), even though they were set to autogrow unlimited. This is small in my opinion.
Based on the size of the other DBs on the instance (about 1.5TB) and the time it's taken for some of my own BI queries (30 minutes) I would have expected the tempdb to be way over 2GB.
No one was monitoring the page life expectancy, but I suspect the instance was not able to manage/auto grow its tempdb files. Once we increased the tempdb size, we no longer had performance issues.
I was surprised to find both HA nodes had the same tempdb sizes of 2GB (both have run the production services for some time).
I haven't found this issue online. Is this an issue with this CU or with 2017 in general? What could have prevented the tempdb from auto growing? Should we have looked elsewhere for the performance issue?
Instant file initialization is not enabled:
The AG logs don't have anything useful. The AG did break with the tempdb error that occurred though.
The tempdb database had two months to grow, but it didn't. It stayed at the initial size of 2GB. It should have grown by itself based on the autogrow settings on each file, which were 100MB, unlimited?