Use SQL 2016 Query Store to Remove Ad-hoc Plans

Ad-hoc query plans consume memory and can be of limited use. It is a good idea to remove ad-doc query plans if they are not being recused. The query below demonstrates how to use Query Store metadata to identify and remove unwanted ad-hoc query plans from the cache. 

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL =

(

        SELECT 'EXEC sp_query_store_remove_query ' 

                + CAST(qsq.query_id AS NVARCHAR(6)) + ';' AS [data()]

        FROM sys.query_store_query_text AS qsqt

        JOIN sys.query_store_query AS qsq

               ON qsq.query_text_id = qsqt.query_text_id

        JOIN sys.query_store_plan AS qsp

               ON qsp.query_id = qsq.query_id

        JOIN sys.query_store_runtime_stats AS qsrs

               ON qsrs.plan_id = qsp.plan_id

        GROUP BY qsq.query_id

        HAVING SUM(qsrs.count_executions) = 1

               AND MAX(qsrs.last_execution_time) < DATEADD (HH, -24, GETUTCDATE())

        ORDER BY qsq.query_id

        FOR XML PATH('')

) ;

EXEC(@SQL) ;

You can find out more about the Query Store in SQL Server 2016, in my new book, Expert Scripting and Automation for SQL Server DBAs, available from Apress at www.apress.com/9781484219423?gtmf=s or at Amazon at www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465300485&ref_=sr_1_1&sr=8-1