2

I know there's a lot of questions like this one, but I think this is different, and none of them could help me.

When I run a query with execution plan, I get this:

enter image description here

But, even with the index created, it's not using it.

The funny thing is, when I create it in my test environment (same database, same query, same index), it uses it.

Why is my production server not using the index I've created?

enter image description here

This is the same query in my test environment with the same index in the same database.

It's a simple query:

select * 
from Database 
where col1 = 'string'
  and col2 = 'string'

The index I created is:

create index (ix_col1_col2)
on Table (col1, col2)

Edit 1:

With Aaron's answers, I now have this:

enter image description here

I have the same result with * and using all fields.

The server still want me to create the same index it's already using. it's because the 0%? it thinks the query it's not using it?

EDIT 3:

What a strange thing. I used this:

alter index all on table rebuild

and update stats, and now, the query is using the index with 50% CPU.

Is this a good question to help others? or we should close it? since it was a simple problem.

Again thanks Aaron for your help.

  • Try adding OPTION (RECOMPILE) to the query. It is possible that the plan you are getting is stuck in a version prior to the index existing. It is also possible that because of the number of rows that match, and the fact that you're using SELECT * and so the index doesn't cover your query, that in one environment the other index is still the better choice. – Aaron Bertrand Sep 18 '15 at 11:41
  • It can be Aaron. I will update my question with I got. The query now is using the index, but with 99% CPU in the PK, and still tells me to add the missing index ( the same one ). – Racer SQL Sep 18 '15 at 11:46
  • It is doing a key lookup - meaning your index is not covered. Can you post the relevant colums. – Kin Shah Sep 18 '15 at 12:17
  • @Kin, This is the query: select * from Table where idChamada = '18/09/15 09:08:19 010A 7E09/00' and evento = 'EVENTO_DE_COMPLETAMENTO' – Racer SQL Sep 18 '15 at 12:23
  • Table columns: ID| Data| IDChamada| Evento| Resposta| IDTarifador| ProximoEvento – Racer SQL Sep 18 '15 at 12:24
2

Table creation :

CREATE TABLE dbo.table1
    (         [ID]              int primary key
            , [Data]            varchar(20)
            , [IDChamada]       varchar(50)
            , [Evento]          varchar(50)
            , [Resposta]        varchar(10)
            , [IDTarifador]     int
            , [ProximoEvento]   varchar(10))
;

You can bloat the table

UPDATE STATISTICS dbo.Table1 WITH ROWCOUNT = 10000000, pagecount = 10000000

With your given index

create nonclustered index nc_idChamada_evento_Rafael on dbo.table1 ( idchamada,evento) 

SQL Server is going to do a KEY LOOKUP to find other columns from your SELECT list

select [ID]
    ,[Data]
    ,[IDChamada]
    ,[Evento]
    ,[Resposta]
    ,[IDTarifador]
    ,[ProximoEvento]
from dbo.Table1
where idChamada = '18/09/15 09:08:19 010A 7E09/00'
    and evento = 'EVENTO_DE_COMPLETAMENTO'

enter image description here

Key Lookup is done on below columns since they are part of select query :

enter image description here

The index you need is

create nonclustered index nc_idChamada_evento_KIN on dbo.table1 (
    idchamada
    ,evento
    ) include (
    [Data]
    ,[Resposta]
    ,[IDTarifador]
    ,[ProximoEvento]
    )

Above index will give you a nice seek :

enter image description here

|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.