Insert query stalled with high SLEEP_TASK wait time on SQL Server (Performance Insights)

Ordinarily, SLEEP_TASK is a nonspecific wait type in SQL Server which occurs when a task sleeps while waiting for a generic event to occur, according to Microsoft documentation.  This wait type can usually be safely ignored, however on some occasions it can happen when a script does not execute completely or hangs up for long periods of time.

The SLEEP_TASK wait means that a thread is waiting on a resource or waiting for some event to occur, and it could indicate background task scheduling, a query plan exchange operator that isn’t tracked by CXPACKET, or it could be a hashing operation that spills to tempdb.

Hash spills mean there is not enough memory to execute the query, and data has to be spilled into tempdb in order to execute. This usually results in slower performance and space consumption in tempdb. In order to determine if hash spills are occurring, you can run SQL Profiler and enable Hash Warning Events. For more information on that, you can refer this document: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/hash-warning-event-class.

You can analyse and review the execution plan for the data load of the query to identify inefficiencies. Also, ensure that the query is optimised and check the involved tables for over-indexing and foreign keys to determine if there can be optimisations made on that end. You can consider performing inserts in batches as it would be more efficient instead of executing it all at once as a single large query.

You can use database management tools or queries to identify locks and blocking sessions at the time you observed the issue. Also, you can have a look at memory, throughput and I/O usage on your RDS instance to check if there is any resource throttling and identify potential bottlenecks.

I would also suggest taking a look at RDS SQL Server best practices, while setting some performance parameters and configuring tempdb: https://aws.amazon.com/blogs/database/best-practices-for-configuring-performance-parameters-for-amazon-rds-for-sql-server/

Additionally, you can look at document for troubleshoot queries that seem to never end in SQL Server: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-never-ending-query?source=recommendations&tabs=2008-2014