none
SSAS Tabular Periodically Freezes due to Query pool busy threads

    Question

  • Hi,

    Latest SQL 2017 SSAS tabular version.

    We have an SSAS tabular instance that periodically becomes unresponsive. There are no resource spikes from a CPU, memory, or disk IO perspective. And there is very little load on the server. We have noticed that the Query pool busy threads performance counter shows there are busy threads and the $System.discover_commands shows there are commands running. We've analyzed the commands and just replaying them does not reproduce the scenario. It just seems like those commands are stuck for some reason and subsequent commands begin to queue. We've tried to kill the running commands 

    Any thoughts on further steps to diagnose the root cause?


    Friday, July 13, 2018 3:52 PM

All replies

  • It just seems like those commands are stuck for some reason and subsequent commands begin to queue.

    This sort of behaviour is typically due to a processing commit lock. Do you know if you had processing operations running which were completing at about the same time?

    When processing a Tabular model the process operation takes place in a transaction and the new data is stored in a temporary copy of the model. Once the process operation is complete the SSAS server requests and exclusive database lock then it makes the data from the transaction the "current" version and drops the previous data and then releases the exclusive lock. Typically this is a relatively fast operation, however you can run into issues when you have long running queries in flight at the time the server requests the exclusive lock. When this happens the server typically waits up to 30 seconds for the inflight queries to complete and during this time any new queries get queued up.

    There are 2 timeouts in the server settings which can impact this behaviour ForceCommitTimeout controls how long the server waits for long running queries to complete (this defaults to 30 seconds) and after this timeout expires the server will attempt to cancel these queries in order to allow the commit operation to complete. The second one Commit timeout (which defaults to 0) will cause the commit operation to be cancelled if this timeout is exceeded. Typically you only have one of these time outs set.


    http://darren.gosbell.com - please mark correct answers

    Monday, July 16, 2018 1:54 AM
    Moderator
  • Hi mhaberman,

    Thanks for your question.

    >>>Any thoughts on further steps to diagnose the root cause?
    It is  pretty hard to determine the root cause without detailed error messages. If you can reproduce this issue, I would suggest you to use SQL Server Profiler to monitored Event "Command Begin" and "Command End" , these two Command Events will capture all processing commands, it will give you some hints about this issue.

    For more information, please refer to Use SQL Server Profiler to Monitor Analysis Services.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 16, 2018 4:58 AM
    Moderator
  • If you have a connection open at the time this issue occurs you can run:

    SELECT * FROM $System.DISCOVER_LOCKS

    and see if there is a lock_type of 16 with a status of 0 - this is a pending commit lock. The only issue here is that if this theory is correct, then if you do not have a connection open you may not be able to establish a connection until the lock is released (unless you have a different database on this server that you can connect to)


    http://darren.gosbell.com - please mark correct answers

    Monday, July 16, 2018 6:39 AM
    Moderator
  • There is no processing happening during the time where this happens
    Monday, July 16, 2018 5:53 PM
  • I captured all the commands and they are all standard DAX queries. Rerunning them again doesn't reproduce the issue.
    Monday, July 16, 2018 5:54 PM
  • There is no processing happening during the time where this happens
    Monday, July 16, 2018 5:55 PM
  • Hi mhaberman,

    Thanks for your response.

    >>>I captured all the commands and they are all standard DAX queries. Rerunning them again doesn't reproduce the issue.
    If you can not reproduce this issue, then you will not be able to capture the trace for your SSAS instance. Just like Darren said, this sort of behavior is typically due to a processing commit lock. If there is no processing happening during the time where this happens, I do not think a query session will block another query session, if you have enough memory for your SSAS instance.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, July 17, 2018 6:58 AM
    Moderator
  • There is no processing happening during the time where this happens

    In this case, try checking if any of the thread pools are experiencing queuing. There are performance counters called "Query pool job queue length", "Long Parsing job queue length", "Short Parsing job queue length" and "Processing pool job queue length". If you are seeing non-zero queue lengths for extended periods you could be experiencing thread pool  starvation. If this is your issue it would be best to raise a support case with Microsoft as this sort of issue is not common and you need to be very careful when making an alterations to any of the thread pool settings.

    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 17, 2018 8:36 AM
    Moderator