Troubleshoot Running Out of Worker Threads

As your instance experiences higher traffic along with concurrent usage, it may run out of worker threads needed to process session requests. This means that sessions will be waiting for worker threads to be assigned to process the requests. It would most likely lead to slower processing times and result in encountering wait types such as SOS_SCHEDULER_YIELD, THREADPOOL, and CXPACKET.

Typically, the issue is caused by one of the following:

Many active connections
Many long running queries
Queries using parallelism
Server CPU not powerful enough for the workload

The most common remedies are:

Reduce number of connections
Identify and tune long running queries
Identify and tune queries with usage of high degree of parallelism
Identify and tune queries with CPU wait type
Identify and tune queries with CXPACKET wait type

The SQL - Running Out of Threads alarm of Spotlight Cloud notifies the user when instance is running out working threads to process session requests.

Working Threads 8

Use the Spotlight Cloud diagnostic dashboards to troubleshoot the issue:

Reduce number of connections. Visit the Sessions dashboard to identify the amount of activity:
Identify and tune long running queries:

a. Use the Workload Analyzer dashboard to see the top SQL by Active Time. Click the SQL Statements dimension and sort queries by Active Time metric:

b. Use the Workload Analyzer dashboard to see the top SQL by degree of parallelism. Tune queries or use query hints within query statements to reduce use of parallelism degree.

I. Highlight the SQL Statement dimension from the tree and click the hamburger icon to open the metrics selector:

II. Locate Degree of Parallelism field and enable it. Use the mouse to drag the field towards the top of the list of fields

III. Sort by Degree of Parallelism column to identify queries with highest threads:

c. Use the Workload Analyzer to filter top SQL by CPU wait type. Click the CPU wait type icon on the banner and expand SQL Statements dimension. Tune queries accordingly:

d. Use the Workload Analyzer dashboard to filter top SQL by Other wait type. Click the Other wait type icon on the banner. Expand SQL Statements dimension. Use the Resource Breakdown pane to identify queries with CXPACKET wait type. Tune queries accordingly: