
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
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: