sp_BlitzCache: @SlowlySearchPlansFor isn't working

[aroques]

Version of the script
SELECT @Version = '8.06', @VersionDate = '20210914';

What is the current behavior?
Does not work to find parameterized queries that have been executed with sys.sp_executesql.

If the current behavior is a bug, please provide the steps to reproduce.

1. Execute any paramerterized query with sys.sp_executesql .
   In the StackOverflow database:

DECLARE @SearchDisplayName AS NVARCHAR(100) = 'Brent%';
DECLARE @StringToExecute AS NVARCHAR(MAX) ='SELECT * FROM dbo.Users u WHERE u.DisplayName LIKE @SearchDisplayName';
EXEC sys.sp_executesql @StringToExecute, 
    N'@SearchDisplayName NVARCHAR(100)', 
    @SearchDisplayName

2. Then, look for the query:
   EXEC dbo.sp_BlitzCache @SlowlySearchPlansFor='dbo.Users'

   In source code, it looks like the parameter is always wrapped in wildcards, but I have tried it with wildcards as well:
   EXEC dbo.sp_BlitzCache @SlowlySearchPlansFor='%dbo.Users%'

What is the expected behavior?
To be able to find parameterized queries that have been executed with sys.sp_executesql.
You can find them with the below query:

SELECT
	st.text,
	qs.sql_handle,
	qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE 1=1 
AND CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%dbo.Users%'
AND cp.objtype= 'Prepared'
--AND st.text LIKE '%dbo.Users%'
AND st.text NOT LIKE '%dm_exec_cached_plans%'
AND st.text NOT LIKE '%##BlitzCacheProcs%'

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL Server Version: Microsoft SQL Server Enterprise: Core-based Licensing (64-bit)
OS: Windows Server 2016 Standard (10.0)

I don't know if this worked in previous versions of this procedure.

[BrentOzar]

Yeah, we ran into issues we couldn't solve from the T-SQL side - when Microsoft converts the XML to NVARCHAR, it appears that they're taking out spaces and changing the text. More info: #2202

I'm going to close this because we couldn't figure out how to solve it, but if you want to experiment with it and come up with a fix, we'd totally welcome the pull request. Just drop a note here if you want to actively work on the issue.

[aroques]

I've looked into it more. The search is working. I think it's just that the results were getting filtered out (probably from the TOP parameter). When I added @DatabaseName, I started seeing results.

EXEC sp_BlitzCache 
	@SlowlySearchPlansFor='Users',
	@DatabaseName='StackOverflow'

Thanks for the reply.

Also, (in case it may be helpful to others), the main reason that I'm looking into this is to be able to reliably find prepared queries that are coming in through Entity Framework Core. I wrote this wrapper around sp_BlitzCache to do that for now.

ALTER PROC dbo.sp_FindEFCorePlans
	@QueryText AS VARCHAR(MAX),
	@MaxPlans INT = 10
AS
/*
	For more info about DMVs see: 
		https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-transact-sql?view=sql-server-ver15
	
	EXEC dbo.sp_FindEFCorePlans @QueryText='%dbo.Users%'
*/
BEGIN

DECLARE @DatabaseName AS NVARCHAR(128) = DB_NAME(),
	    @ResultCount AS INT = 0;

SELECT 
	SqlHandle = qs.[sql_handle], 
	[Text] = st.[text], 
	CreationDateTime = qs.creation_time, 
	ExecutionCnt = qs.execution_count,
	[Avg Duration (ms)]= CAST(ROUND((qs.total_elapsed_time / 1000.0) / qs.execution_count, 2) AS DECIMAL(18,2)),
	MoreInfo = 'EXEC dbo.sp_BlitzCache @DatabaseName=''' + @DatabaseName + ''', @OnlySqlHandles=''' + CONVERT(VARCHAR(MAX), qs.[sql_handle], 1) + ''''
INTO #sql_handles
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE 1=1 
	AND st.[text] LIKE @QueryText
	AND cp.objtype= 'Prepared'
	AND DB_NAME(st.[dbid]) = @DatabaseName
	AND st.[text] NOT LIKE '%dm_exec_cached_plans%'
	AND st.[text] NOT LIKE '%##BlitzCacheProcs%'

SELECT @ResultCount = COUNT(*) FROM #sql_handles

IF @ResultCount = 0
BEGIN 
	SELECT Result = 'No plans found.'
END
ELSE IF @ResultCount <= @MaxPlans
BEGIN
	DECLARE @SqlHandles AS VARCHAR(MAX)
	SELECT @SqlHandles = STRING_AGG(CONVERT(VARCHAR(MAX), sh.SqlHandle, 1), ',') FROM #sql_handles AS sh
	SELECT Results = CAST(@ResultCount AS VARCHAR(50)) + ' results found.',
		[Executing...] = 'EXEC dbo.sp_BlitzCache @DatabaseName=''' + @DatabaseName + ''', @OnlySqlHandles=''' + @SqlHandles + ''''
	EXEC dbo.sp_BlitzCache @DatabaseName=@DatabaseName, @OnlySqlHandles=@SqlHandles
END
ELSE 
BEGIN
	SELECT Result = 'More than '+ CAST(@MaxPlans AS VARCHAR(50)) +' plans found. Try adding more text to your search so that ' + CAST(@MaxPlans AS VARCHAR(50)) + ' or less plans are found.'
	SELECT sh.[Text],
           sh.CreationDateTime,
           sh.ExecutionCnt,
           sh.[Avg Duration (ms)],
           sh.MoreInfo 
	FROM #sql_handles AS sh 
	ORDER BY sh.ExecutionCnt DESC
END 

DROP TABLE #sql_handles

END

GO