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?

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)

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;