Try Spotlight Cloud ProSign in

      Troubleshoot Running Out of Worker Threads

      By: Gita Sharifi

      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:

      1. Reduce number of connections. Visit the Sessions dashboard to identify the amount of activity:
        Working Threads 1
      2. 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:
        Working Threads 2

        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:
        Working Threads 3


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


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

        Working Threads 5

        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:
        Working Threads 6

        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:
      Try Spotlight Cloud for free!

       

      SQL server performance

      April 9, 2021 1:51:08 PM PDT
      Gita Sharifi

      Written by Gita Sharifi

      Senior Technical Support Engineer at Quest

      Topics