Why SQL Server occasionally decides not to use this index?

Question

I have a complex problem with SQL Server.

I administer 40 databases with identical structure but different data. Those database sizes vary from 2 MB to 10 GB of data. The main table for these databases is:

CREATE TABLE [dbo].[Eventos](
    [ID_Evento] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [FechaGPS] [datetime] NOT NULL,
    [FechaRecepcion] [datetime] NOT NULL,
    [CodigoUnico] [varchar](30) COLLATE Modern_Spanish_CI_AS NULL,
    [ID_Movil] [int] NULL,
    [CodigoEvento] [char](5) COLLATE Modern_Spanish_CI_AS NULL,
    [EventoData] [varchar](150) COLLATE Modern_Spanish_CI_AS NULL,
    [EventoAlarma] [bit] NOT NULL CONSTRAINT [DF_Table_1_Alarma]  DEFAULT ((0)),
    [Ack] [bit] NOT NULL CONSTRAINT [DF_Eventos_Ack]  DEFAULT ((0)),
    [Procesado] [bit] NOT NULL CONSTRAINT [DF_Eventos_Procesado]  DEFAULT ((0)),
    [Latitud] [float] NULL,
    [Longitud] [float] NULL,
    [Velocidad] [float] NULL,
    [Rumbo] [smallint] NULL,
    [Satelites] [tinyint] NULL,
    [EventoCerca] [bit] NOT NULL CONSTRAINT [DF_Eventos_FueraCerca]  DEFAULT ((0)),
    [ID_CercaElectronica] [int] NULL,
    [Direccion] [varchar](250) COLLATE Modern_Spanish_CI_AS NULL,
    [Localidad] [varchar](150) COLLATE Modern_Spanish_CI_AS NULL,
    [Provincia] [varchar](100) COLLATE Modern_Spanish_CI_AS NULL,
    [Pais] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
    [EstadoEntradas] [char](16) COLLATE Modern_Spanish_CI_AS NULL,
    [DentroFuera] [char](1) COLLATE Modern_Spanish_CI_AS NULL,
    [Enviado] [bit] NOT NULL CONSTRAINT [DF_Eventos_Enviado]  DEFAULT ((0)),
    [SeñalGSM] [int] NOT NULL DEFAULT ((0)),
    [GeoCode] [bit] NOT NULL CONSTRAINT [DF_Eventos_GeoCode]  DEFAULT ((0)),
    [Contacto] [bit] NOT NULL CONSTRAINT [DF_Eventos_Contacto]  DEFAULT ((0)),
 CONSTRAINT [PK_Eventos] PRIMARY KEY CLUSTERED 
(
    [ID_Evento] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [ABS]
GO
ALTER TABLE [dbo].[Eventos]  WITH CHECK ADD  CONSTRAINT [FK_Eventos_Eventos] FOREIGN KEY([ID_Evento])
REFERENCES [dbo].[Eventos] ([ID_Evento])

I also have a cycle that runs every n seconds to process these records (only new ones and mark them as processed). This process uses this query:

SELECT     
   Tbl.ID_Cliente, Ev.ID_Evento, Tbl.ID_Movil, Ev.EventoData, Tbl.Evento, 
   Tbl.ID_CercaElectronica, Ev.Latitud, Ev.Longitud, Tbl.EsAlarma, Ev.FechaGPS, 
   Tbl.AlarmaVelocidad, Ev.Velocidad, Ev.CodigoEvento
FROM         
   dbo.Eventos AS Ev 
INNER JOIN
   (SELECT 
        Det.CodigoEvento, Mov.CodigoUnico, Mov.ID_Cliente, Mov.ID_Movil, Det.Evento, 
        Mov.ID_CercaElectronica, Det.EsAlarma, Mov.AlarmaVelocidad 
    FROM 
        dbo.Moviles Mov 
    INNER JOIN 
        dbo.GruposEventos AS GE 
    INNER JOIN  
        dbo.GruposEventosDet AS Det ON Det.ID_GrupoEventos = GE.ID_GrupoEventos
     ON GE.ID_GrupoEventos = Mov.ID_GrupoEventos) as Tbl ON EV.CodigoUnico = Tbl.CodigoUnico AND Ev.CodigoEvento = Tbl.CodigoEvento
WHERE     
    (Ev.Procesado = 0)

The table can have on some databases more than 1.000.000 records. So to optimize the process I created this index specific for this query using SQL assistant for optimization:

CREATE NONCLUSTERED INDEX [OptimizadorProcesarEventos] ON [dbo].[Eventos] 
(
    [Procesado] ASC,
    [CodigoEvento] ASC,
    [CodigoUnico] ASC,
    [FechaGPS] ASC
)
INCLUDE ( [ID_Evento],
[EventoData],
[Latitud],
[Longitud],
[Velocidad]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

This used to work perfect. But now occasionally and only in some databases, the query takes forever and gives me timeout. So I run a "show execution plan" and realize that in some scenarios depending on the data from the table, SQL Server decides not to use my index and use a PK Index instead. I verify this running the same execution plan on other db that works fine and the index is being use.

So my question: why does SQL Server on some occasions decide not to use my index?

Thank you for your interest!

UPDATE I already try to UPDATE STATICS and didn´t help. I preffer to avoid the use of HINT for now, so the question remains: Why SQL Server choose a more inefficient way to execute my query if has an index for it?

UPDATE II After many test, I could finaly resolve the problem, even though i don't quite undestand why this worked. I change the index to this:

CREATE NONCLUSTERED INDEX [OptimizadorProcesarEventos] ON [dbo].[Eventos] 
    (
        [CodigoUnico] ASC,                          
        [CodigoEvento] ASC,
        [Procesado] ASC,
        [FechaGPS] ASC
    )
    INCLUDE ( [ID_Evento],
    [EventoData],
    [Latitud],
    [Longitud],
    [Velocidad]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Basicaly i change the order of the fields in the index and the query inmediatly start to use the index as espected. I'ts still a mistery for me how SQL Server choose to use or not to use indexes on specific query. Thanks to everyone.

Answer 1

Determining why the optimizer does or doesn't choose a given index can be somewhat of a dark art. I do notice, however, that there's likely a better index that you could be using. Specifically:

CREATE NONCLUSTERED INDEX [OptimizadorProcesarEventos] ON [dbo].[Eventos] 
(
    [Procesado] ASC,
    [CodigoEvento] ASC,
    [CodigoUnico] ASC,
    [FechaGPS] ASC
)
INCLUDE ( [ID_Evento],
  [EventoData],
  [Latitud],
  [Longitud],
  [Velocidad]) 
WHERE Procesado = 0 -- this makes it a filtered index
WITH (SORT_IN_TEMPDB = OFF, 
  DROP_EXISTING = OFF, 
  IGNORE_DUP_KEY = OFF, 
  ONLINE = OFF)
ON [PRIMARY]

This goes on my assumption that at any given time, most of the rows in your table are processed (i.e. Procesado = 1) so the above index would be much smaller than the non-filtered version.

Answer 2

you must have find lot of articles on how Query optimizer chooses the Right Index. if not search something on google. I can point out one to start with.

Index Selection and the Query Optimizer

The simple answer is as follows:

"Based on the index usage history, statistics, number of rows inserted/updated/deleted etc.... Query optimizer has find out that using the PK index is less costly than using the other Non Clustered index."

now you will have lot of questions around how did Query Optimizer finds that out? and that will require some home work.

though in your specific situation, I am not agree with "Femi" as mentioned to try and running "Update Statistics" because there are some other situations as well where Update Statistics will also not help. It sound like you have tested this Index on this query and if you are sure that you want only this index to be used 100% of time by that query, use the query hint and specify this index needs to be used. by that way you can always sure that this index will be used.

CAUTION: you must have done more than enough testing on various data loads to make sure in no case using this index is not expected or not acceptable. Once you use the Query hints every execution will use that only and Optimizer will always come up with execution plan using that Index.

Answer 3

Its difficult to tell in this specific case, but very often the query planner will look at the statistics it has for the specific table and decide to use the wrong index (for some definition of wrong; probably just not the index you think it should use). Try running UPDATE STATISTICS on the table and see if the query planner arrives at a different set of decisions.