Remove query from Query Store report

Question

I've just started using Query data store in SQL server 2016, and its very useful indeed. I have a problem in that on the server there are a number of services that are monitoring service broker queues, and as a result their WAITFOR statements always appear as the longest running queries in the reports. This in itself is not an issue, but they run for so long that they skew the duration axis on the report so that all the other queries are hardly visible.

Is there any way to get Query Store to ignore a query so it doesn't show up on the report?

Soapbark

Answer 1

Try using sp_query_store_remove_query. It removes the query, as well as all associated plans and runtime stats from the query store.

DECLARE @QueryStoreRemoveCommand VARCHAR(MAX) 
SELECT @QueryStoreRemoveCommand = COALESCE(@QueryStoreRemoveCommand + 
                                    '; EXEC sp_query_store_remove_query ', 
                                    'EXEC sp_query_store_remove_query ') 
                                  + CONVERT(NVARCHAR,QueryData.query_id)
FROM 
(SELECT Qry.query_id  
FROM sys.query_store_plan AS Pl  
JOIN sys.query_store_query AS Qry  
    ON Pl.query_id = Qry.query_id  
JOIN sys.query_store_query_text AS Txt  
    ON Qry.query_text_id = Txt.query_text_id
WHERE UPPER(Txt.query_sql_text) LIKE '%WAITFOR DELAY%') QueryData

PRINT @QueryStoreRemoveCommand
EXECUTE (@QueryStoreRemoveCommand)

Ornie

Answer 2

Good question!
I didn't find how to remove one query (which would be usefull), but I found how to clear the cache so that you can start over. That way if you have an old query that changed, you can reset the cache and get fresh data.

ALTER DATABASE WideWorldImporters SET QUERY_STORE CLEAR;

Gadoid

I'll file that one for future reference as its a useful little snippet. Unfortunately I'd still have the same problem after doing so in this case as the services are always running and will immediately start mucking up the axis again... – Soapbark

Answer 3

From Delete ad hoc queries:

This purges adhoc and internal queries from the Query Store so that the Query Store does not run out of space and remove queries we really need to track.

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
SELECT q.query_id
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
   OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
GROUP BY q.query_id
HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

Superhuman