msSQL – ¿Como selecciona el SQL Server los índices de sus tablas?
Fuente: http://www.sql-server-performance.com/mr_indexing.asp
Tipos de Índices preferidos por el SQL Server
Cuando se usa el analizador de consultas que produce el grafico del plan de ejecución, se aprecia los diferentes usos de índices del SQL Server.
1) Clustered Index Seek: Utiliza la habilidad de recuperar las tuplas directamente desde el índice agrupado. En la mayoría de los casos, proveen el mejor rendimiento para una cláusula SELECT.
En el analizador de consultas, utilizando la base de datos ejemplo pubs, se ejecuto la siguiente consulta para obtener su plan de ejecución:
SELECT *
FROM authors
WHERE au_id LIKE’2%’
2) Index Seek: Usa un índice no agrupado para recuperar la información, y de algún modo, actúa como un índice agrupado. Esto es porque los datos recuperados se obtienen de la hoja superior de un índice no agrupado y no de las hojas de datos. Se ve este comportamiento en los covering index.
En el analizador de consultas, utilizando la base de datos ejemplo pubs, se ejecuto la siguiente consulta para obtener su plan de ejecución:
Nótese que el índice no agrupado “titleind” es utilizado para la recuperación de los datos.SELECT title_id, title
FROM titles
WHERE title LIKE ‘t%’
3) Bookmark Lookup: Usa un índice no agrupado para la selección de información. Empieza con una búsqueda indexada en las hojas nodos del índice no agrupado para identificar la ubicación de la información en las hojas de datos, luego recupera la información directamente de las mencionadas hojas de datos. Las hojas nodo de un índice no agrupado contienen un puntero a la ubicación de la tupla en las hojas de datos.
En el analizador de consultas, utilizando la base de datos ejemplo pubs, se ejecuto la siguiente consulta para obtener su plan de ejecución:
SELECT *
FROM titles
WHERE title LIKE ‘t%’
4) Scans: (Table scans, Index scan, y Clustered Index scans) son usualmente malas a menos que las tablas tenga pocas filas y el optimizador determina que escanear toda la tabla será mejor que usar cualquiera de los índices disponibles.
En el analizador de consultas, utilizando la base de datos ejemplo pubs, se ejecuto la siguiente consulta para obtener su plan de ejecución:
SELECT *
FROM employee
WHERE hire_date > ’1992-08-01′
Se observa en este caso, una Clustered Index Scan lo que significa que todas las tuplas del índice agrupado han sido analizadas para cumplir con los requerimientos de la consulta.
Una vez analizados los principios básicos de como leer un plan de ejecución, tengamos en cuenta la siguiente información adicional para analizar el uso adecuado de índices en las diferentes consultas:
- Si se crea un plan de ejecución para una consulta múltiple o un procedimiento almacenado al mismo tiempo con el analizador de consultas, se pueden comparar los costos de cada consulta o procedimiento para saber cual es el mas eficiente. Esto es muy útil para analizar las diferentes versiones de una misma consulta o procedimiento almacenado.
- Un índice agrupado se crea automáticamente con la aparición de una clave primaria si este aun no existe pero nada se dice de un índice no agrupado al realizar esta acción.
- Los índices no agrupados guardan la información de la clave del agrupado para localizar a la fila correspondiente. Esto es tomado como un beneficio a la hora de crear covering indexes.
- El tamaño de una tabla comprende el tamaño de la información de la misma y el tamaño de cualquier índice que se deba mantener sobre la misma.
- Agregarle muchos índices a una tabla hace que aumente el tamaño de los índices que administra esa tabla pudiendo esto afectar el rendimiento de la misma
- Siempre se recomienda que una tabla tenga un índice agrupado salvo que se sepa que la misma tendrá pocas filas.
- Aquellas búsquedas denominadas seeks por el plan de ejecución de una consulta tienen un mejor rendimiento, mientras que las llamadas scans deben ser evitadas.
- El tamaño de una tabla de acuerdo al número de filas es el factor principal que ayuda al optimizador de consultas a determinar el mejor plan para su ejecución.
Buena Consulta A
SELECT *
FROM authors
WHERE au_lname like ‘r%’
La misma utiliza un Bookmark Lookup y una Index seek.
Buena Consulta B
SELECT *
FROM authors
WHERE au_lname LIKE ‘r%’ AND au_fname like ‘a’
Como la consulta anterior esta utiliza un Bookmark Lookup y una Index seek.
No tan Buena Consulta C
SELECT *
FROM authors
WHERE au_fname LIKE ‘a’
Esta ultima utiliza en su plan una Clustered Index Scan.
Acciones para las tablas claves
Para las tablas estadisticas (aquellas que raramente o nunca cambian), se le pueden asignar cualquier cantidad de índices. Ya que como se menciono antes, muchos índices pueden afectar el rendimiento de una tabla que se consulta mucho, pero en este caso no ocurre ya que los datos de las mismas no cambiaran.
Para tablas que se consultan con mucha frecuencia, se debe tratar de limitar el numero de índices. Siempre teniendo en cuenta que los índices no agrupados contienen la clave del agrupado. Por esta razón se debe limitar el numero de columnas de los índices agrupados para que se mantengan con un tamaño pequeño. Todo índice en una tabla de estas características debe estar altamente justificado, con un factor de consultas por ese campo mayor al 80%.
Para tablas muy utilizadas por los procedimientos almacenados se debe analizar muy cuidadosamente como se acceden a esas tablas tratando de eliminar los scans que puedan producir esas consultas transformándolos en seeks. Para determinar esto es bueno utilizar las estadísticas de entrada salida y decidir que tan importante es la tabla a analizar.
En caso de que haya tablas que poseen un tamaño de índices mayor que su tamaño de datos, la misma se debe revisar para justificar el uso de cada uno de ellos.
Abril 2007
Ibarra, Ariel 3401-1279
Kremky, Héctor 3401-0146
Mazzi, María Flavia 3601-0653
Pacciocetti, Claudio 3301-1608
Rizzo, Gastón 3501-0166
Sayus, Eduardo 3501-1623
