Crear índice para optimizar las consultas

Muchas veces cuando manejamos grandes cantidades de información y realizamos búsquedas que pueden tardar bastante en mostrar los resultados, posteriormente las siguientes búsquedas serán un poco más rápidas siempre y cuando la condición de la consulta sea idéntica a la primera que realizamos, esto sucede porque se ha creado un plan de ejecución, pero si se llegara a borrar el Cache la consultara tardaría de nuevo muchos tiempo.


Para borrar el cache puedes utilizar las siguientes instrucciones:
--BORRA TODOS LOS PLANES DEL CACHE
DBCC FREEPROCCACHE WITH NO_INFOMSGS
--VACIA LOS DATOS DEL CACHE
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS



Para solucionar el problema lo mejor es crear índices, dentro de SQL Server se manejan 2 tipo de índices Clustered y NON Clustered.

Clustered (agtupados)
Se ordena físicamente en el disco, reacomodando los datos de acuerdo a los campos seleccionados en el índice, solo se puede crea un índice Clustered por tabla.


Non Clustered (no agrupoados)
Se crea un estructura adicional, sobre está se ordenan los datos para mejorar el rendimiento de las consultas que no están adicionadas en un índice Clustered, se pueden crear "n" cantidad de índices Non Clustered.

Cuando creamos una nueva tabla e indicamos en la estructura un campos UNIQUE o PRIMARY KEY se creara automáticamente un índice Clustered tomando como referencia dicho campo.


Para la creación de un índice ingresamos a SQL Manager, seleccionamos la tabla donde crearemos el índice, verificaremos los campos y los índice que tenga creados, para el ejemplo tenemos un índice Clustered ya creado.

Realizamos una consulta sencilla y ejecutamos

Otra forma de revisar los índice es tecleando y ejecutando :
execute sp_helpindex 'nombre_tabla'

Para crear el nuevo índice seleccionamos Indexes, botón derecho New Index…

Tecleamos la siguiente instrucción para crear el índice:
 create nonclustered index NOMBRE_INDICE on NOMBRE_TABLA (NOMBRE_CAMPO)
Podemos presionar el icono para verificar que las instrucciones sean correctas.
En caso de necesitar un índice CLUSTERED utilizamos create clustered index...

Presionamos EXECUTE y refrescamos la tabla para verificar los cambios.

En el apartado de Indexes se visualizara el nuevo índice.

Por último haremos una consulta, seleccionamos la instrucción y presionamos en icono DISPLAY ESTIMATED EXECUTION PLAN, en la parte inferior se podrá visualizar el índice con el cual se realizar la consulta, en caso de no aparecer ningún índice se deberá crearlo para agilizar las consultas.

Si queremos utilizar los índice en una consulta haremos lo siguiente:


CONSULTA EFICIENTE
Select * form NOMBRE_TABLA with(index=NOMBRE_INDICE)


CONSULTA INEFICIENTE
Select * from NOMBRE_TABLA order by NOMBRE_CAMPOS


Para consultar todos los índices que comiencen con algunas letras, utiliza:
Select name from sysindexes where name like 'idx%'


Para reconstruir y reorganizar los índices (indexar):
Alter index all on NOMBRE_TABLA rebuild
Alter index all on NOMBRE_TABLA reorganize