Recientemente tuvimos que dar solución a un escenario “no tan peculiar” de uso de índices filtrados.

Introducción a índices filtrados y sus casos de uso (http://technet.microsoft.com/en-us/library/cc280372.aspx)

Si no tienes claro el concepto, por favor lee el artículo de Technet antes de seguir adelante.

El escenario a resolver era el siguiente:

  • Dada una aplicación que por cada visita web, añade una fila a una tabla de auditoría.
  • Dicha tabla tiene una columna fecha que indica cuando sucedió la visita. Descartamos información adicional como usuario, url de procedencia, navegador, etc. para simplificar el escenario.
  • Cada noche se borraba y creaba un índice filtrado para acotar el conjunto de filas a los últimos 7 días de visitas: el cliente tiene un proceso más o menos importante que requiere manejar la información de los últimos 7 días para alimentar ciertos procesos analíticos.
  • En principio el índice parecía bien diseñado, pero las DMVs de actividad de índices decían que no se usaba y las cargas de los procesos analíticos tenían más o menos la misma velocidad que antes de la creación del índice filtrado.

En este post vamos a ver qué sucedió y cómo llegamos a ver qué sucedía.

En primer lugar, creamos un conjunto de datos para la simulación; para ello puedes utilizar el siguiente código:

 

--
-- filtered indexes
--
-- SolidQ 
-- http://creativecommons.org/licenses/by-sa/3.0/
-- Attribution-NonCommercial-ShareAlike 3.0
--
-- https://blogs.solidq.com/elrinconDelDBA
-- http://siquelnet.com

use Adventureworks
go

create table top_
(id int identity, d date, s char(10)
constraint top__pk primary key (id))
go

insert top_ values
  ('20081010', 'aaa'), ('20081011', 'abb')
, ('20081012', 'acc') ,('20081013', 'add')
, ('20081016', 'baa') ,('20081013', 'bdd')
, ('20081017', 'bcc') ,('20081013', 'bdd')
go

insert top_
select top 1000 p.d, p.s
from top_ p
cross join master.dbo.spt_values c
go

insert top_ values (null, null)
go

-- mas filas
insert top_
select top 100000 p.d, p.s
from top_ p
cross join master.dbo.spt_values c
go
-- mas filas
insert top_
select top 100000 p.d, p.s
from top_ p
cross join master.dbo.spt_values c
go
-- mas filas
insert top_
select top 100000 p.d, p.s
from top_ p
cross join master.dbo.spt_values c

asdf

Comprobamos cual es la distribución de la columna d (almacena fechas):

select d, count(*) c
from top_
group by d
order by c desc

Quedando de la siguiente forma:

Untitled_10_054347F3

Para nuestro ejemplo, asumamos que buscamos filas >= que el 16 de octubre de 2008.

Primero habilitamos STATISTICS IO

set statistics io on

y comprobamos cuantas filas tenemos posteriores al día 16 comentado anteriormente:

select COUNT(*) c
from top_
where d >= '20081016'

validamos el plan de ejecución y STATISTICS IO y vemos lo siguiente:

Untitled2_9_054347F3

 

Obviamente la tabla sólo tiene índice clustered por la PK por lo que SQL Server tendrá que recorrerse toda la tabla para validar qué filas cumplen el predicado.

Si creamos un índice nonclustered por la columna fecha, tendríamos los siguientes resultados:

create nonclustered index nci_top_d
on top_ (d);

y volviendo a ejecutar la consulta tendríamos:

Untitled3_1_054347F3

 

Donde hemos pasado de las más de 1400 lecturas de páginas iniciales a apenas 28.

hasta aquí todo en orden, veamos ahora qué sucede creando un índice filtrado por la fecha del filtro (16 de octubre de 2008).

La sintaxis para crear el índice es la siguiente:

create nonclustered index nci_top_d_filter
on top_ (d)
where d >= '20081016';

y volviendo a ejecutar la misma consulta, vemos que SQL Server no utiliza el índice recientemente creado 🙁

Untitled4_1_054347F3

 

Si intentamos forzar el uso del índice tendremos el siguiente resultado:

select COUNT(*)
from top_ with (index=nci_top_d_filter)
where d >= '20081016'

la consulta finalizará correctamente y en el plan de ejecución podrás comprobar que el índice filtrado se ha usado; sin embargo, si cambias la base de datos a parametrización FORZADA, SQL Server te mostrará el siguiente error:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Es como si SQL Server determinara que el índice no puede usarse para resolver la consulta; desde el pto. de vista de lógica relacional, el índice filtrado si podría cubrir la consulta; tras buscar en varios sitios, he llegado a leer argumentos que indican que debe forzarse a generar un plan de ejecución nuevo para cada ejecución, pero eso funcionará unas veces si, y otras no 🙂 me explico: en modo parametrización FORZADA, SQL Server parametriza casi todas las consultas (mira en BOL los casos que no), y el plan de ejecución que deje en caché deberá ser estable para siguientes usos que se haga del el; si dejara en caché un plan de ejecución que use el índice filtrado:

  • Unas veces funcionará (cuando el argumento se cumpla para el rango del índice filtrado); por ejemplo: filtras para día posterior al día 16: el índice filtrado si cumple el predicado.
  • y para otras veces no: filtras por fecha anterior al día 16.

Antes de seguir adelante, borra el índice por la columna fecha sin filtro:

drop index nci_top_d on top_

Configura la base de datos en parametrización SIMPLE, y ejecuta la consulta siguiente:

select COUNT(*)
from top_ 
where d >= '20081016'
Untitled10_1_054347F3

Comprueba cómo se usa el índice filtrado, ahora ejecuta la siguiente consulta y comprueba que ha decidido hacer un clustered index scan porque el índice filtrado no es suficiente:

select COUNT(*)
from top_
where d >= '20011016'
Untitled9_1_054347F3

Es decir, SQL Server dinámicamente valida que los parámetros son suficientes para resolver con el índice filtrado o no.

Sin embargo, si cambias la BD a parametrización FORZADA y ejecutas la consulta siguiente:

select COUNT(*)
from top_
where d >= '20011016'
Untitled9_1_054347F3

notarás cómo a pesar de haber un índice que satisface el predicado, directamente lo descarta; sucede exactamente lo mismo que hemos comentado anteriormente: como el plan de ejecución no es “estable” SQL Server directamente desecha el índice filtrado.

¿Qué puedes hacer?

select COUNT(*)
from top_ 
where d >= '20081016'
option (recompile)
Untitled10_1_054347F3

Efectivamente, forzar a que SQL Server cree un plan de ejecuión ad-hoc para la consulta que estás ejecutando; de esta forma, SQL Server si usará el índice filtrado cuando los argumentos sean adecuados para el índice.

Conclusiones:

  • Base de datos configurada en Parametrización Forzada
    • No se puede usar un hint para usar índice filtrado: plan inestable
    • SQL Server no usa el índice filtrado en consultas normales porque parametriza casi todo y tendría que reutilizar planes potencialmente inestables
    • SQL Server considera usar el índice filtrado si especificas OPTION RECOMPILE
    • SQL Server considera usar el índice filtrado si utilizas código dinámico sp_executesql…
  • Base de datos configurada en Parametrización Simple
    • SQL Server considera usar usar índice filtrado si los argumentos son adecuados para el índice filtrado: es decir, dinámicamente se adapta a la cobertura del índice filtrado.
    • sigue probándolo porque en escenarios en los que el índice filtrado es un subconjunto del índice completo, suele resultar difícil que SQL Server utilice el índice filtrado (a menos que lo fuerces)… quizás ahondo en el suguiente post en esto 🙂

 

 

Eladio Rincón

Eladio Rincón is professionally focused on SQL Server databases. He is an MVP on SQL Server since 2003, and his area of expertise is resolution of performance and scalability issues for OLTP based systems. His professional career revolves on SQL Server mentoring, consulting, and training projects. He believes that mixing training and mentoring projects is the best approach to help the clients to get the most from SQL Server. With other mentors of SolidQ he has developed a tuning methodology applied in most of the SQL Server consulting projects.