I had an issue with a Clustered Index Scan. So I got all columns in the Output list of the Index Scan in SSMS and I created a NonClustered Index for these columns.

Now however, I do not see any performance difference even though it is using the new NonClustered Index. Instead it is now doing a NonClustered Index Scan.

How do I get it to do a seek? enter image description here

This is the Predicate shown in the plan

CASE WHEN (1) THEN 'SGD' ELSE [UserTbl].[Name] END=[UserTbl].[Name]
  • 1
    Can you post your query and the execution plan please? Difficult to help without these – James Anderson Dec 24 '15 at 11:28
  • 2
    It could be that the relevant filtering or joining clause(s) are simply not sargable (en.wikipedia.org/wiki/Sargable) in which case the best the query planner can do it order an index scan. It is impossible to say without seeing the query and table structure, so you'll need to edit that information into the question. – David Spillett Dec 24 '15 at 11:34
  • 1
    CASE WHEN (1) is not valid TSQL. What is the actual predicate in the Query? – Martin Smith Dec 24 '15 at 15:27

How do I get it to do a seek?

Given the predicate on the table you have shared with us:

CASE WHEN (1) THEN 'SGD' ELSE [UserTbl].[Name] END=[UserTbl].[Name]

It seems likely you are using an OPTION (RECOMPILE) hint on the query in a version of SQL Server that does not support the Parameter Embedding Optimization (PEO) fully. This requires at least SQL Server 2008 SP1 CU5.

Without that, the recompile hint is not an effective way for dynamic search queries to seek on an index. This type of query is characterized by expressions like:

column = @variable OR @variable IS NULL
column = COALESCE(@variable, column)

If you are not using one of those constructions, it would be something broadly similar. If full parameter embedding were available, the predicate you have would be simplified to:

'SGD' = [UserTbl].[Name]

...which could use a nonclustered index with [Name] as the leading key to perform a seek.

You need to do one or more of the following:

  1. Upgrade to a more recent version of SQL Server (adding an OPTION (RECOMPILE) hint if not already present)
  2. Rewrite the query, perhaps using dynamic SQL, so that the expression is seekable using an index with Name as the leading key
  3. Ensure Name is the leading column in the index

Further reading:

Parameter Sniffing, Embedding, and the Recompile Options
Dynamic Search Conditions in T-SQL

| improve this answer | |

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.