Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*
* This function will return all plan_handles that include a reference to
* a given object. This can help with targeted purges of execution plans.
*/
CREATE OR ALTER FUNCTION find_query_plans_by_object (
@database SYSNAME,
@schema SYSNAME,
@table SYSNAME
) RETURNS TABLE
AS
RETURN
WITH XMLNAMESPACES (
default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
query_stats.plan_handle
FROM (
SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats
) query_stats
CROSS APPLY sys.dm_exec_query_plan(query_stats.plan_handle) qp
WHERE
qp.query_plan.exist('//ColumnReference[
(empty(sql:variable("@database")) or @Database eq sql:variable("@database") or @Database eq fn:concat("[", sql:variable("@database"), "]"))
and (empty(sql:variable("@schema")) or @Schema eq sql:variable("@schema") or @Schema eq fn:concat("[", sql:variable("@schema"), "]"))
and (empty(sql:variable("@table")) or @Table eq sql:variable("@table") or @Table eq fn:concat("[", sql:variable("@table"), "]"))
]') = 1
@stummsft
Copy link
Author

stummsft commented Mar 26, 2019

Example usage:
SELECT *
FROM find_query_plans_by_object(NULL, 'dbo', 'tags')

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment