How to Track Performance of Queries That Use RECOMPILE Hints


Say we have a stored procedure that has two queries in it – the second query uses a recompile hint, and you might recognize it from my parameter sniffing session:

CREATE OR ALTER PROC dbo.usp_SearchUsers @Reputation INT AS BEGIN /* Query 1, always the same: */ SELECT COUNT(*) FROM dbo.Users; /* Query 2, recompiles and gets different plans: */ SELECT TOP 10000 * FROM dbo.Users WHERE Reputation = @Reputation ORDER BY DisplayName OPTION (RECOMPILE); END GO

The first query will always get the same plan, but the second query will get different plans and return different numbers of rows depending on