nonclustered - full table scan sql server
Planes de SQL Server: diferencia entre Index Scan/Index Seek (7)
En un plan de ejecución de SQL Server, ¿cuál es la diferencia entre un análisis de índice y una búsqueda de índice?
Estoy en SQL Server 2005.
Escanea vs. Busca
Escaneo de índice:
Como un escaneo toca cada fila de la tabla, califique o no, el costo es proporcional al número total de filas en la tabla. Por lo tanto, una exploración es una estrategia eficiente si la tabla es pequeña o si la mayoría de las filas califican para el predicado.
Búsqueda de índice:
Como una búsqueda solo toca las filas que califican y las páginas que contienen estas filas calificativas, el costo es proporcional al número de filas y páginas calificadas en lugar de a la cantidad total de filas en la tabla.
Index Scan no es más que escanear en las páginas de datos desde la primera página hasta la última página. Si hay un índice en una tabla, y si la consulta toca una cantidad mayor de datos, lo que significa que la consulta está recuperando más del 50 por ciento o el 90 por ciento de los datos, y luego el optimizador simplemente escaneará todas las páginas de datos para recuperar las filas de datos. Si no hay un índice, entonces es posible que vea un Escaneo de tabla (Escaneo de índice) en el plan de ejecución.
Las búsquedas de índice son generalmente preferidas para las consultas altamente selectivas. Lo que eso significa es que la consulta solo solicita un número menor de filas o simplemente recupera las otras 10 (algunos documentos dicen 15 por ciento) de las filas de la tabla.
En general, el optimizador de consultas intenta utilizar una búsqueda de índices, lo que significa que el optimizador ha encontrado un índice útil para recuperar el conjunto de registros. Pero si no puede hacerlo porque no hay índice o no hay índices útiles en la tabla, entonces SQL Server tiene que escanear todos los registros que satisfacen la condición de consulta.
¿Diferencia entre un escaneo y una búsqueda?
Un escaneo devuelve toda la tabla o índice. Una búsqueda devuelve eficientemente filas de uno o más rangos de un índice basado en un predicado. Por ejemplo, considere la siguiente consulta:
select OrderDate from Orders where OrderKey = 2
Escanear
Con un escaneo, leemos cada fila en la tabla de órdenes, evaluamos el predicado "donde OrderKey = 2" y, si el predicado es verdadero (es decir, si la fila califica), devuelve la fila. En este caso, nos referimos al predicado como un predicado "residual". Para maximizar el rendimiento, siempre que sea posible, evaluaremos el predicado residual en el escaneo. Sin embargo, si el predicado es demasiado caro, podemos evaluarlo en un iterador de filtro separado. El predicado residual aparece en el plan de presentación de texto con la palabra clave WHERE o en el plan de presentación XML con la etiqueta.
Aquí está el plan de presentación de texto (ligeramente editado para abreviar) para esta consulta usando un escaneo:
| -Table Scan (OBJECT: ([ORDERS]), WHERE: ([ORDERKEY] = (2)))
La siguiente figura ilustra el escaneo:
Como un escaneo toca cada fila de la tabla, califique o no, el costo es proporcional al número total de filas en la tabla. Por lo tanto, una exploración es una estrategia eficiente si la tabla es pequeña o si la mayoría de las filas califican para el predicado. Sin embargo, si la tabla es grande y si la mayoría de las filas no califican, tocamos muchas más páginas y filas y realizamos muchas más E / S de las necesarias.
Buscar
Volviendo al ejemplo, si tenemos un índice en OrderKey, una búsqueda puede ser un mejor plan. Con una búsqueda, usamos el índice para navegar directamente a aquellas filas que satisfacen el predicado. En este caso, nos referimos al predicado como un predicado "buscar". En la mayoría de los casos, no es necesario volver a evaluar el predicado de búsqueda como un predicado residual; el índice asegura que la búsqueda solo devuelve filas que califiquen. El predicado de búsqueda aparece en el plan de presentación de texto con la palabra clave SEEK o en el plan de presentación XML con la etiqueta.
Aquí está el plan de presentación de texto para la misma consulta usando una búsqueda:
| -Index Seek (OBJECT: ([ORDERS]. [OKEY_IDX]), SEEK: ([ORDERKEY] = (2)) ORDENADO HACIA ADELANTE
La siguiente figura ilustra la búsqueda:
Como una búsqueda solo toca las filas que califican y las páginas que contienen estas filas calificativas, el costo es proporcional al número de filas y páginas calificadas en lugar de a la cantidad total de filas en la tabla. Por lo tanto, una búsqueda es generalmente una estrategia más eficiente si tenemos un predicado de búsqueda altamente selectivo; es decir, si tenemos un predicado de búsqueda que elimine una gran fracción de la tabla.
Una nota sobre showplan
En showplan, distinguimos entre escaneos y búsquedas, así como entre escaneos en montones (un objeto sin índice), índices agrupados e índices no agrupados. La siguiente tabla muestra todas las combinaciones válidas:
https://blogs.msdn.microsoft.com/craigfr/tag/scans-and-seeks/
Con un Escaneo de índice, todas las filas en el índice se escanean para encontrar una fila coincidente. Esto puede ser eficiente para tablas pequeñas. Con Index Seek, solo necesita tocar las filas que realmente cumplen con los criterios y, por lo tanto, generalmente es más eficiente.
La regla básica a seguir es que los escaneos son malos, las búsquedas son buenas.
Escaneo de índice
Cuando SQL Server realiza un escaneo, carga el objeto que desea leer desde el disco en la memoria y luego lo lee de arriba a abajo buscando los registros que necesita.
Búsqueda de índice
Cuando SQL Server realiza una búsqueda, sabe en qué parte del índice se ubicarán los datos, de modo que carga el índice desde el disco, va directamente a la parte del índice que necesita y lee dónde terminan los datos que necesita. . Obviamente, esta es una operación mucho más eficiente que una exploración, ya que SQL ya sabe dónde se encuentra la información que está buscando.
¿Cómo puedo modificar un plan de ejecución para usar una búsqueda en lugar de un escaneo?
Cuando SQL Server está buscando sus datos, probablemente una de las cosas más importantes que harán que SQL Server cambie de una búsqueda a una exploración es cuando algunas de las columnas que busca no están incluidas en el índice que desea que use. En la mayoría de los casos, esto hará que SQL Server vuelva a realizar un análisis de índice agrupado, ya que el índice agrupado contiene todas las columnas de la tabla. Esta es una de las principales razones (al menos en mi opinión) de que ahora tenemos la capacidad de INCLUIR columnas en un índice, sin agregar esas columnas a las columnas indexadas del índice. Al incluir las columnas adicionales en el índice aumentamos el tamaño del índice, pero permitimos que SQL Server lea el índice, sin tener que volver al índice agrupado, ni a la tabla para obtener estos valores.
Referencias
Para obtener información sobre los detalles de cada uno de estos operadores dentro de un plan de ejecución de SQL Server, vea ....
Un análisis de índice es donde SQL Server lee todo el índice buscando coincidencias; el tiempo que esto toma es proporcional al tamaño del índice.
Una búsqueda de índice es cuando el servidor SQL usa la estructura del árbol b del índice para buscar directamente registros coincidentes (ver http://mattfleming.com/node/192 para obtener una idea de cómo funciona esto); el tiempo empleado es solo proporcional a el número de registros coincidentes.
- En general, una búsqueda de índice es preferible a una exploración de índice (cuando el número de registros coincidentes es mucho más bajo que el número total de registros), ya que el tiempo necesario para realizar una búsqueda de índice es constante independientemente del número total de registros en su mesa.
- Sin embargo, tenga en cuenta que, en determinadas situaciones, un análisis de índice puede ser más rápido que una búsqueda de índice (a veces significativamente más rápido), generalmente cuando la tabla es muy pequeña o cuando un gran porcentaje de los registros coincide con el predicado.
Un escaneo toca cada fila en la tabla, incluso si es lo que buscas o no
Un escaneo solo mira las filas que son lo que está buscando.
Siempre es mejor tener búsquedas que escaneos, ya que son más eficientes en la forma en que se ven los datos.
Una buena explicación se puede encontrar here
Una exploración de índice ocurre cuando la definición del índice no puede encontrar en una sola fila para satisfacer los predicados de búsqueda. En este caso, SQL Server debe analizar varias páginas para encontrar un rango de filas que satisfagan los predicados de búsqueda.
En el caso de una búsqueda de índice, SQL Server encuentra una sola fila que coincide con los predicados de búsqueda que usan definición de índice .
Las búsquedas de índices son mejores y más efectivas.
Respuesta corta:
Escaneo del índice: toque todas las filas pero ciertas columnas.
Búsqueda de índice: toque ciertas filas y ciertas columnas.