We are having an issue with aggressive tempdb spills that significantly effect overall server performance and, in rare cases, cause AlwaysOn failures.
- Tested on SQL2016 and SQL2017 Enterprise (recent CU).
- AWS server with 2Tb of memory and 64 vCPU
- 8 tempdb files located on local SSD drive.
- SSD drive is healthy. Tested on different instances and different instance types. Disk performance is reasonable.
- trace flag 3427 is on for SQL2016 (as far as I know, it is not required for SQL2017).
- performance for other tempdb operations is excellent (table creation, sort for index rebuild, etc.)
We found that tempdb spill process for heavy queries overwhelms local SSD drive, creates very large disk queue and basically brings server on its knees.
I understand that the issue can be fixed for every individual query but is there a way to change how SQL Server writes data in tempdb during a spill and make it less aggressive for all queries?
Over time we will walk through each query and, hopefully, fix it. However, I don't think that it is an expected behavior when server is getting unusable because of a large spill.
Issue does not occur when query has small memory grant. Here is an example:
- by default query has 78Gb memory grant.
- query execution time ~37 minutes
- tempdb spills are happening every minute. 15Gb each time
Issue doesn't occur:
- query has 0.8Gb memory grant
OPTION (max_grant_percent = 1)
- tempdb spills are happening very frequent without significant pressure on SSD drive
- query execution time ~35 minutes
We were able to decrease the impact on the server drastically by reducing max memory grant percent in Resource Governor to 1%. It had no negative impact on overall server performance. Disk queue on SSD now spikes up to 300-500, which is still bad but it doesn't bring the server down.
ALTER WORKLOAD GROUP [default] WITH (request_max_memory_grant_percent=1)
Wish we could go lower but 1% is the minimum SQL Server accepts.
We found that aggressive spills are happening even when tempdb is on shared drives (remote Storage, in our cases AWS EBS volumes) and
MAXDOP <> 1.
So the only case when it works fine - is when tempdb on shared volumes and MAXDOP = 1 (which is true for our case, don't ask why :) ).
We are working with Microsoft to get to the bottom of the issue.