After blogging recently (maybe?) about filters, there was a Stack Exchange question about a performance issue when a variable was declared with a max type.
After looking at it for a minute, I realized that I had never actually checked to see if a recompile hint would allow the optimizer more freedom when dealing with them.
CREATE INDEX u ON dbo.Users(DisplayName); DECLARE @d nvarchar(MAX) = N'Jon Skeet'; SELECT COUNT_BIG(*) AS records FROM dbo.Users AS u WHERE u.DisplayName = @d; SELECT COUNT_BIG(*) AS records FROM dbo.Users AS u WHERE u.DisplayName = @d OPTION(RECOMPILE);
Turns out that it won’t, which is surprising.
Even though both plans have sort of a weird seek, the filter operator remains as a weird sort of residual predicate.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
2 thoughts on “Recompile Hints Can’t Fix Every SQL Server Query Performance Problem”
…And … what is Expr10004?
Is there any way the XML plan could help?
Comments are closed.