2

I have SQL code that is executing slow

enter image description here

DECLARE @ccode varchar(500)
SET @ccode = '%NIP%'

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE @ccode

Why is SQL Server using nested loops?

This code is working fine, it uses only index scan:

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

The slow query is executing 5.5 seconds, without the parameter it's 1.8 seconds.

This query is also working fine:

SELECT [CVN_UID]
FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS]
WHERE DESCRIPTOR_TEXT LIKE @ccode
OPTION (recompile)
  • How much faster is the fast version? – Mikael Eriksson Mar 11 '16 at 12:12
4

The Seek

When the LIKE specification is in a variable:

WHERE DESCRIPTOR_TEXT LIKE @ccode

...SQL Server needs to build a plan that will work whatever the variable contains each time the cached execution plan is executed. It would also like to be able to seek if the variable contents are compatible. The solution is to use a dynamic seek, as I explain in the following article:

This can result in a much better plan if the variable contains something that can be transformed to an efficient seek, which usually means no leading wildcards. If there is a leading wildcard, the dynamic seek may turn out to be less efficient than a simple scan with a residual LIKE predicate. For example, a full scan may issue more, and larger, read-ahead reads.

To be clear, a dynamic seek can still be used if the variable contains a leading wildcard, but the start and end points of the seek will cover the whole structure.

The Scan

Making the leading-wildcard explicit in a string literal makes it clear that a seek cannot be used, so you get a scan:

WHERE DESCRIPTOR_TEXT LIKE '%NIP%'

The downside is that this plan cannot be reused for different string literals, which may cause excessive plan cache usage.

The combination

Using a variable with the recompile option:

WHERE DESCRIPTOR_TEXT LIKE @ccode
OPTION (RECOMPILE)

...means SQL Server can 'sniff' the value of the variable at runtime, embed the value it finds into the query, and produce a plan optimized for that specific value.

The resulting execution plan will not be cached, so there is no possibility of plan reuse, but this also means you will not suffer from plan cache bloat either. There will be a small overhead on each call for the fresh compilation, but this is often a trade-off that makes sense: If the variable contains some seekable, you'll get a seek plan; otherwise, a scan. More details in my article:

1

My kneejerk suggestion would be that you're having problems with parameter sniffing. Jes Schultz Borland has a great article about it here. Based on the fact that you get a different plan when the query uses a parameter I think this is a great place to start :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.