Tuesday, 7 June 2016

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. 


        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('')
) ;


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

No comments:

Post a Comment