2

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.

Disk queue

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:

Issue occurs:

  • 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

UPDATE

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.

UPDATE (2019-04-25) 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.

  • I don't think we can predict which queries are going to spill in tempdb aggressively. We can pull historical data and fix recurring queries, but this server is "data-warehouse" and users can write whatever they want. – Alex Bochkov Mar 7 at 0:50
  • We have full control over tempdb config. And we have a test server where the issue can be reproduced easily. – Alex Bochkov Mar 7 at 1:13

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.