Query Store Cleaning And Mysterious Broker Tasks

Posted on by Erik Darling

Query Store Cleaning And Mysterious Broker Tasks

While looking at a client’s server, I noticed there was, at any given time, 2-3 background sessions with the command column saying BRKR TASK, which I found quite odd.

Only the Microsoft-shipped broker queues were present, and none were activated. Nothing uses Service Broker, Mirroring, or Log Shipping.

Running this query:

DROP TABLE IF EXISTS 
    #s1;

SELECT 
    r.session_id, r.start_time, r.status, r.command, 
    r.database_id, r.wait_type, r.wait_time, r.last_wait_type, 
    r.open_transaction_count, r.open_resultset_count, r.transaction_id, 
    r.cpu_time, r.total_elapsed_time, r.scheduler_id, r.task_address
INTO #s1 
FROM sys.dm_exec_requests AS r 
WHERE r.command = N'BRKR TASK';
WAITFOR DELAY '00:00:05';
SELECT 
    r.session_id, r.start_time, r.status, r.command, 
    r.database_id, r.wait_type, r.wait_time, r.last_wait_type, 
    r.open_transaction_count, r.open_resultset_count, r.transaction_id, 
    r.cpu_time, r.total_elapsed_time, r.scheduler_id, r.task_address,
    cpu_delta_ms = r.cpu_time - s1.cpu_time
FROM sys.dm_exec_requests AS r 
JOIN #s1 AS s1 
  ON s1.session_id = r.session_id
WHERE r.cpu_time - s1.cpu_time > 1000 
AND   r.command = N'BRKR TASK'
ORDER BY 
    cpu_delta_ms DESC;

Gave me results that looked like this:

SQL Server Query Results
You’re a BRKR TASK

Bell Ringing

This reminded me of things I saw at other client sites previously, where Query Store wait stats cleanup and Query Store internal index reorgs were causing some weird jiggery-pokery and burning serious CPU time. And another time where CE_FEEDBACK caused high PREEMPTIVE_OS_QUERYREGISTRY waits.

It would be nice if Query Store got some proper attention paid to its internals one of these days. I do trust it mostly, and many improvements have been made to it over the years. It is not 2016 anymore, after all, and Query Store is far from a forgotten feature. But when you pair this with the pains that accompany querying it (hello you weird union all views that hit slow-ass in-mem functions that can’t push filters down), it does feel a bit left-behind.

On the advice of my legal counsel, I took an ETW trace for a couple minutes. I do not recommend living as dangerously as I do.

wpr -start cpu
/*Listen to one Misfits song*/
wpr -stop c:\last_caress.etl

I won’t bore you with all the gruesome details, but after digging through the numbers, I found some Dear Old Friends:

QDS.DLL!CDBQDS::DoPersistData                      75,009  89.95%
  → QDS.DLL!CDBQDS::CleanupCapturePolicyStats      74,994  89.93%
    → QDS.DLL!::operator()
      → QDS.DLL!CDBQDS::GetStmt                    74,249  89.04% exclusive

Which, in this case, was conclusive.

You may feel tempted to argue with me, and if you have a Being Wrong™️ fetish, that’s totally cool. This is a great opportunity to max it out.

I only kink shame furries.

There’s Another Diamond Ring

Unfortunately there’s not much you can do when this crops up. Various trace flags recommended for Query Store don’t help, nor does recycling or rebooting the server. The task will just start back up.

All you can do is wait for it to be over, like when your wife puts on Love is Blind and opens a fresh bottle of wine.

Anyway, good luck out there.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Related Posts