Can't eliminate index scan

Question

I have a query, and even using SQL Sentry, I can't eliminate the index scan.

This is the query:

SELECT TOP 30 codCliente FROM (
        SELECT t1.CodCliente, codcampo, valor, t1.chavealeat 
        FROM tblCliente AS t1 WITH(NOLOCK) 
            INNER JOIN tblClienteDetalhe AS t2 WITH(NOLOCK) 
            ON t1.codcliente = t2.codcliente 
            AND CodCampo IN(-1, 4)
        WHERE codStatus IN (0)  
            AND t1.ChavePeriodo < GETDATE()
            AND t1.CodStatusLigacao = 0
            AND EXISTS
            (
                SELECT codcliente FROM tblclientedetalhe WITH(NOLOCK) 
                WHERE codcampo = 3 AND valor = '2' 
                    AND codcliente = t1.codcliente
            )
            AND EXISTS
            (
                SELECT codcliente FROM tblclientedetalhe WITH(NOLOCK) 
                WHERE codcampo = 6
                    AND CONVERT(DATETIME, Valor) BETWEEN '2015-08-01' AND '2015-08-31'
                    AND DATEDIFF(DAY, Valor, GETDATE()) > 15
                    AND codcliente = t1.codcliente
            )
            AND NOT EXISTS
            (
                SELECT 0 FROM tblPesquisa WITH(NOLOCK)
                WHERE tblPesquisa.CodCliente = t1.CodCliente
            )
            AND EXISTS
            (
                SELECT codcliente FROM tblclientedetalhe WITH(NOLOCK) 
                WHERE codcampo = 4 and valor = '202' and codcliente = t1.codcliente
            )
    ) AS Cliente 
Pivot (MAX(Valor) FOR codCampo in ([4])) AS PivotTable
WHERE (((([4] = '202')))) 
ORDER BY chavealeat;

This is the index I made:

create index IX_CHAVEALEAT_CODCLIENTE
on tblcliente (chavealeat,codcliente)
include (chaveperiodo,codstatus,codstatusligacao)

And, this is the analysis:

Why is the plan showing index scan? I'm covering all columns. I used option(recompile) to update the plan.

Is this index wrong? The output is only codcliente.

I have 10-30 queries running at the time, and theres no wait_info problems.

score 5 · Accepted Answer · edited Apr 13 '17 at 12:42

Just because you have a covering index doesn't mean it won't scan, nor does it mean that a scan isn't still the best way to access the requested data.

You're returning, what, 3 million rows? Even if that's not most of the table, it still wouldn't make much sense to perform 3 million seek operations, nor is it likely there would be any benefit in seeking to the first row and performing a range scan for the rest. There is also the issue that multiple EXISTS clauses - multiple of which do not join to the index's leading key column - will not likely all be able to take advantage of the same output from the seek. And you have filters on all of the INCLUDE columns - they're not part of the key, so seeks on those columns are going to be difficult (in fact the only case where I've seen an index still be used to seek when there are WHERE clauses against non-key columns is when the column was BIT, and those scenarios did not introduce all the other variables you have above). You're also using PIVOT and aggregating, which I'm sure don't help.

Also, what table is CodCampo in (why isn't it properly prefixed with a table alias)? You may have one or both of the following two problems:

It is a disjunction (OR clause) which can discourage seeks (see Paul's answer here).
If it is in tblcliente, then your index is not covering anyway.

You could try running the query with the FORCESEEK hint (also mentioned in Paul's example) and it will either fail to produce a plan (unlikely) or you will get a plan that you can share with us and, if you also share the scan plan you get without the hint, we can give further insight into why SQL Server chose the scan instead.

As an aside (this certainly won't help you get a seek), since the estimates were far lower, I'd check that your statistics are current.

And finally, don't always assume that a scan is bad and something you need to eliminate! In a lot of cases, a scan really is the best way to retrieve the data.

Can't eliminate index scan

1 Answers1