Post reply

SQL Server Worker Thread Alert

  • May 2, 2019 at 12:00 am

    #3542133

    Comments posted to this topic are about the item SQL Server Worker Thread Alert

  • June 12, 2019 at 1:29 pm

    #3652742

    Edward hi!

    thanks for sharing the alert, but there are couple of thoughts:

    1) the following two selects can be written in one statement

    Select @workers_waitingForCPU = sum(runnable_tasks_count) from sys.dm_os_Schedulers where status='VISIBLE ONLINE'
    Select @Request_waiting_ForThread = sum(work_queue_count) from sys.dm_os_Schedulers where status='VISIBLE ONLINE'


    Select @workers_waitingForCPU = sum(runnable_tasks_count), 
    @Request_waiting_ForThread = sum(work_queue_count) 
    from sys.dm_os_Schedulers where status='VISIBLE ONLINE'

    2)  what is the magic number 545 ?

    If @availableThreads <=545

  • June 12, 2019 at 1:47 pm

    #3652747

    Andy, Thanks for the update. In most cases I am under the gun to toss code together and not taking the time to maybe make trhe most efficient I can. I put this together quickly as we were smoke testing a Auto seeding\TDE process I put together for a application that tossed databases at SQL all day long. We wrote some neat code to compare the SQL tree with the AG tree and add the missing databases to the AG group 1 at a time. SO this worker thread alert came into play when we starved SQL of threads on a smaller VM testing machine. Once we got to a large VM to test on we were able to cross 1,200 seeded databases but ofcourse ran out of threads. The goal was to find the "breakling point" where this falls apart. Thge 545 I sould have explained further that is a very high number but to just test the alerts functionality at first. For alerting a DBA should use a much lower number they felt spells trouble on their system. I used POS to manage the seeding work and encryption working using invoke to call T-SQL. One intersting item I encountered was in automating this process you can not run right from encrypt to add to AG since a quick backup is needed to meet AG pre req. So I wrote a small encyption watcher so the flow will not move forward until the encryption is completed to peform the backup. I also wrote another alert that if the AG fails over and the "Secondary" has non seeded databases it sends an email with those listed.

    Thanks very much for reviewing my code and making more efficient. I always say in T-SQL land there are always more then 1 way to accomplish a goal.

    Ed Pochinski

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply