Partager via

What is QDS_ASYNC_QUEUE wait type ???

  • Article

A week ago, one of my clients' faced an issue with the SQL server performance going poor post migration from SQL 2012 to SQL 2016.

On delving deeper found that QDS_ASYNC_QUEUE was the major wait type. The client did have the Query Store enabled on the subject databases and found the below information on research.

As we know, Query Store feature does impact the performance of SQL server but ideally the impact is expected to be in the range of 3-5% which accounts for the fact of SQL undergoing expensive IOs while persisting the query store information from the memory to the physical storage.

Internally, the Query Store itself doesn’t persist the data it holds in memory to the storage subsystem. Instead it places the data inside an asynchronous queue. From there, an internal process will write the data inside the queue to the storage subsystem. By using this asynchronous queue, the writes are not directly processed but are, instead, scheduled and the Query Store process can continue without waiting for the data write to complete.

Also, it is considered that the Data Flush Interval setting controls the interval at which the Query Store persists its data to the storage subsystem. This is not completely true. The Data Flush Interval only represents the interval that Query Store data is added to the asynchronous queue. After it is placed in the queue, the speed at which the data is persisted to the storage subsystem depends on the internal asynchronous writer process. Also, it effects only the query runtime information where as Query texts and Execution Plans are added to the asynchronous queue immediately to avoid data loss of those objects.

The textual explanation is clearly depicted in the flowchart below :

Thus we can find that QDS_ASYNC_QUEUE wait time accounts for the sleep time of the QDS Async Persist Queue task while it is waiting for an item to be scheduled for persistence. And we can delve deeper to find the resource under concern using the perform capture of the below performance counters under SQL Server : Query Store

  • Query Store CPU usage
  • Query Store logical reads
  • Query Store logical writes
  • Query Store physical reads

In our client scenario, as they did not have an intended usage of the Query Data Store on the databases of SQL 2016 server, we were allowed to disable it and that drastically improved the performance and the jobs completed in good run time. This helped us resolve the issue.

 

Hope this helps !! Happy better performing !!

Comments

  • Anonymous
    September 09, 2018
    Informative article.. Shreyas R Gowda the blog series has 24 posts now and counting.. Great Job!!!!!
    • Anonymous
      November 06, 2018
      Thanks for your support!!