A while back I wrote a blog post on how parameterized queries with a LIKE in the predicate (WHERE) performed slower than the non-parameterized version.

In a nutshell, the parameterized version of the query can’t tell for certain what parameters will be passed and does not generate an optimal plan.

While attempting to set up a parameter sniffing scenario for a demo recently, I ran into an issue when trying to get parameter sniffing to occur.  It turns out my Azure SQL Database had the new parameter sniffing option enabled.

This got me to thinking, would this help solve the performance issue related with parameterized wildcard queries?  Let’s find out!

The demo query

I’ll use the query below in order to test this.  Note @AccountNbr is parameterized and we are doing a LIKE wildcard query.  The query has a trailing wildcard.  This should make the query sargable.

.exec sp_executesql @stmt=N'select * from CustomerAccount WHERE AccountNbr LIKE @AccountNbr',
@params=N'@AccountNbr VARCHAR(40)', @AccountNbr='ACT00001%'

I’m going to start my test by disabling the parameter sniffing option.  With the parameter sniffing option disabled, we are getting a scan:

This execution took 74 seconds.

Just to be sure this wasn’t the result of a cached plan I made sure to do a DBCC FREEPROCCACHE prior to this test.

After enabling the new parameter sniffing option and running the same query our plan has a seek:

This execution took 74 milliseconds.

Final thoughts

While this seems to improve the issue associated with parameterized wildcard queries, there is probably an impact on the plan cache (I’m just guessing).  I’ll devote  a separate post to dig into the impacts of this on the plan cache, but also the broader topic of how the parameter sniffing features impact the plan cache for other types of queries.