SQL Server - No determina el índice correcto

Buenas, estoy haciendo un query a una tabla de 40.000 filas. Tengo un índice en esa tabla que tiene dos columnas (digamos COL1, COL2)

La cláusula WHERE del query sería así:

WHERE COL1 = X1 AND COL2 = X2

La cantidad de filas que devuelve el query es 300 (es decir el 0,75% del total de las filas)

El problema que tengo es que al ver el plan de ejecución el SQL se observa que no toma el índice que existe por esas columnas, sino que toma el índice de la PK, lo cual tiene un costo mayor.

Yo uso el hint INDEX para forzar que tome ese índice si lo toma y el costo obviamente es muchísimo menor.

Mi pregunta es: ¿por qué si el costo de usar el índice es menor que no hacerlo entonces el optimizador de consultas no lo utiliza?

Tengo las estadísticas actualizadas.

Gracias.

¿Que tan DEFRAGMENTADOS tienes el indice?

No comentas que VERSION manejas, pero si fuera 2005 o superior, checalo con:

SELECT OBJECT_NAME(dt.object_id),si.name, dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('<tubase>'), NULL, NULL, NULL, 'DETAILED')
WHERE index_id = 0) as dt --Con index_id 0 evitamos traer información de tablas que no tienen índices (Heaps)
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id

Debemos de observar el valor avg_fragmentation_in_percent para determinar si el índice contiene fragmentación externa, cuando este es mayor a 10 debemos considerar desfragmentar el índice. En la columna avg_page_space_used_in_percent podemos ver la fragmentación interna y esta se presenta cuando el valor es menor a 75.

Fragmentación Valor
Interna < 75
Externa > 10

Si determinamos que nuestra base de datos esta presentando fragmentación, ya sea interna o externa, debemos de ejecutar ALTER INDEX...REORGANIZE o ALTER INDEX...REBUILD para eliminarla. Debemos recordar que cada sistema es diferente y este valor es un aproximado, pero lo podemos tomar como base y realizar pruebas para tomar un valor más adecuado.

Isaias, gracias por tu respuesta.

La versión es SQL Server 2008 R2.

Corrí el query que me pasaste reemplazando <tubase> por el nombre de la base de datos y no devolvió ninguna fila.

De todos modos probé a reorganizar y luego reconstruir el índice, corrí nuevamente el query y sigue sin tomar el índice.

Hagamos esto, ejecuta:

SELECT DB_ID('tubase')

El numero que te de, colocalo en XX

SELECT OBJECT_NAME(dt.object_id),si.name, dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (XX, NULL, NULL, NULL, 'DETAILED')
WHERE index_id = 0) as dt --Con index_id 0 evitamos traer información de tablas que no tienen índices (Heaps)
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id

Isaias, sigue sin devolver filas.

Saludos.

Entonces no hay indice:

ejecuta SP_HELP <tutabla>