El proceso de tunning continuado de BBDD- Clustered Index Scan
En una base de datos en continuo cambio y con gran número de usuarios conectados, es básico optimizar el rendimiento para conseguir una mayor velocidad de respuesta y un mayor número de conexiones concurrentes, consumiendo un mínimo de CPU.
Esto implica también un buen uso de la memoria caché y evitar los tiempos de espera a disco.
La solución consiste en almacenar en storeds procedures las consultas, optimizando los planes de ejecución.
Veamos un ejemplo de como funciona un plan de ejecución y que sucede si variamos los campos de búsqueda.
tenemos la siguiente consulta:
select GeographyKey , firstname from [dbo].[DimCustomer] where GeographyKey =10
su plan de ejecución es un index seek , eso significa que va a leer el indice clúster, ya que no tiene ningún índice mas, pero como la consulta es sobre campos no clúster el plan de ejecución realiza una búsqueda en claves clúster que le devuelva el resto de campos no incluidos en el clúster.
Si cambiamos el signo = por un > en la consulta, el plan cambia asi:
Donde vemos que el index seek se convierte en un index scan, ya que nos devuelve mas resultados que la consulta anterior.
Es aqui donde el propio plan de ejecución nos informa de una importante mejora del rendimiento del 96% si creamos el siguiente indice:
CREATE NONCLUSTERED INDEX [index_DimCustomer_1] ON [dbo].[DimCustomer]
([GeographyKey] ASC,
[CustomerKey] ASC
)
INCLUDE ( [FirstName]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
ejecutando el indice nos cambia el plan de ejcución a:
donde tenemos un óptimo index seek 100% pero esta vez sobre el nuevo indice creado.
Pero que ocurre si añadimos un nuevo campo a la consulta?
select GeographyKey , firstname, LastName from [dbo].[DimCustomer] where GeographyKey >10
como vemos todo el trabajo anterior se ha perdido. La búsqueda vuelve a hacerse sobre el indice clúster y nos sugiere una nueva creación de indice.
Es esto óptimo? La respuesta es no exactamente.
Lo que debemos hacer es recrear el indice añadiendo en el campo INCLUDE el nuevo campo LastName. Asi nos evitamos que queden indices obsoletos, con el correspondiente incremento de espacio en disco ocupado y con el aumento de tiempo de mantenimiento y reconstrucción periodica de indices.
Es pues necesario un constante trabajo de tunnig si queremos una base de datos en las mejores condiciones de funcionamiento.