sql-server-2008 - unico - unique index sql server

El optimizador ignora la condición del índice filtrado.

Question

Supongamos que estoy ejecutando un sitio web que muestra imágenes divertidas de gatos. Tengo una tabla llamada CatPictures con las columnas Filename , Awesomeness , y DeletionDate , y el siguiente índice:

create nonclustered index CatsByAwesomeness
on CatPictures (Awesomeness) 
include (Filename)
where DeletionDate is null

Mi consulta principal es la siguiente:

select Filename from CatPictures where DeletionDate is null and Awesomeness > 10

Yo, como ser humano, sé que el índice anterior es todo lo que necesita SQL Server, porque la condición del filtro de índice ya garantiza que la fecha de DeletionDate is null .

Sin embargo, SQL Server no lo sabe; el plan de ejecución para mi consulta no utilizará mi índice:

Incluso si se agrega una sugerencia de índice, aún se verificará explícitamente DeletionDate mirando los datos de la tabla real:

(y además se quejan de un índice faltante que incluiría DeletionDate ).

Por supuesto que pude

include (Filename, DeletionDate)

en cambio, y funcionará:

Pero parece un desperdicio incluir esa columna, ya que solo consume espacio sin agregar información nueva.

¿Hay alguna manera de que SQL Server sea consciente de que la condición del filtro ya está haciendo el trabajo de verificar DeletionDate ?

Answer 1

¿Hay alguna manera de que SQL Server sea consciente de que la condición del filtro ya está haciendo el trabajo de verificar DeletionDate?

No.

Los índices filtrados fueron diseñados para resolver ciertos problemas, no TODOS. Las cosas evolucionan y, algún día, es posible que vea que SQL Server es compatible con la función que espera de los índices filtrados, pero también es posible que nunca lo vea.

Hay varias buenas razones por las que puedo ver cómo funciona.

Lo que mejora en:

1. Almacenamiento. El índice solo contiene claves que coinciden con la condición de filtrado
2. Actuación. Un shoo-in de lo anterior. Menos para escribir y menos páginas = recuperación más rápida

Lo que no hace:

1. Cambia radicalmente el motor de consulta.

Juntándolos, teniendo en cuenta que SQL Server es una bestia capaz de paralelismo de multiprocesadores y altamente segmentado, obtenemos el siguiente comportamiento cuando se trata de atender una consulta:

1. Pre-condición para que el optimizador de consultas seleccione índices: verifique si un Índice filtrado es aplicable contra la cláusula WHERE.
2. El optimizador de consultas continúa su trabajo normal: determinar la selectividad a partir de estadísticas, sopesar el índice-> búsqueda de marcadores frente a la agrupación / análisis de almacenamiento dinámico en función de si el índice está cubriendo, etc.

Enlazando la condición contra el índice filtrado en el "núcleo" del optimizador de consultas, sospecho que va a ser un trabajo mucho más grande que dejarlo en el paso 1.

Personalmente, respeto al equipo de desarrollo de SQL Server y, si fuera lo suficientemente fácil, podrían lograrlo en un sprint no muy lejano y hacerlo. Sin embargo, lo que hay actualmente ha logrado lo que estaba destinado y me hace muy feliz.

Answer 2

Acabo de encontrar esa "brecha en la funcionalidad", es realmente triste que los índices filtrados sean ignorados por el optimizador. Creo que intentaré usar vistas indexadas para eso, eche un vistazo a este artículo

http://www.sqlperformance.com/2013/04/t-sql-queries/optimizer-limitations-with-filtered-indexes

Answer 3

No, no actualmente.

Ver este elemento de conexión . Está cerrado porque no se arregla. (O este específicamente para el caso IS NULL )

El elemento de conexión proporciona una solución que se muestra a continuación.

Publicado por RichardB CFCU el 29/09/2011 a las 9:15 AM

Una solución es INCLUDE la columna que se está filtrando.

Ejemplo:

CREATE NONCLUSTERED INDEX [idx_FilteredKey1] ON [dbo].[TABLE] 
(
    [TABLE_ID] ASC,
    [TABLE_ID2] ASC
)
INCLUDE ( [REMOVAL_TIMESTAMP]) --explicitly include the column here
WHERE ([REMOVAL_TIMESTAMP] IS NULL)