Práctica No. 8. Indices

Objetivo: Gestionar índices en SQL Server

Un índice es una estructura de disco asociada con una tabla o una vista que acelera la recuperación de filas de la tabla o de la vista. Un índice contiene claves generadas a partir de una o varias columnas de la tabla o la vista. Dichas claves están almacenadas en una estructura (árbol b) que permite que SQL Server busque de forma rápida y eficiente la fila o filas asociadas a los valores de cada clave.

Una tabla o una vista puede contener los siguientes tipos de índices:

Para obtener más información acerca de la arquitectura de índices, vea Arquitectura de estructuras de tablas y datos de índices.

Tanto los índices agrupados como los no agrupados pueden ser únicos. Esto significa que dos filas no pueden tener el mismo valor para la clave de índice. De lo contrario, el índice no es único y varias filas pueden compartir el mismo valor de clave. Para obtener más información, vea Directrices para diseñar índices únicos.

Los índices se mantienen automáticamente para una tabla o vista cuando se modifican los datos de la tabla.

Índices y restricciones

Los índices se crean automáticamente cuando las restricciones PRIMARY KEY y UNIQUE se definen en las columnas de tabla. Por ejemplo, cuando cree una tabla e identifique una determinada columna como la clave primaria, Motor de base de datos crea automáticamente una restricción PRIMARY KEY y un índice en esa columna. Para obtener más información, vea Crear índices (motor de base de datos).

Cómo utiliza los índices el optimizador de consultas

Los índices bien diseñados pueden reducir las operaciones de E/S de disco y consumen menos recursos del sistema, con lo que mejoran el rendimiento de la consulta. Los índices pueden ser útiles para diversas consultas que contienen instrucciones SELECT, UPDATE, DELETE o MERGE. Fíjese en la consulta SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250 en la base de datos AdventureWorks2008R2. Cuando se ejecuta la consulta, el optimizador de consultas evalúa cada método disponible para recuperar datos y selecciona el método más eficiente. El método puede ser un recorrido de la tabla o puede ser recorrer uno o más índices si existen.

Al realizar un recorrido de la tabla, el optimizador de consultas leerá todas las filas de la tabla y extraerá las filas que cumplen con los criterios de la consulta. Un recorrido de la tabla genera muchas operaciones de E/S de disco y puede consumir recursos. No obstante, puede ser el método más eficaz si, por ejemplo, el conjunto de resultados de la consulta es un porcentaje elevado de filas de la tabla.

Cuando el optimizador de consultas utiliza un índice, busca en las columnas de clave de índice, busca la ubicación de almacenamiento de las filas que necesita la consulta y extrae las filas coincidentes de esa ubicación. Generalmente, la búsqueda del índice es mucho más rápida que la búsqueda de la tabla porque, a diferencia de la tabla, un índice frecuentemente contiene muy pocas columnas por fila y las filas están ordenadas.

El optimizador de consultas normalmente selecciona el método más eficaz cuando ejecuta consultas. No obstante, si no hay índices disponibles, el optimizador de consultas debe utilizar un recorrido de la tabla. Su tarea consiste en diseñar y crear los índices más apropiados para su entorno de forma que el optimizador de consultas disponga de una selección de índices eficaces entre los que elegir. SQL Server proporciona el Asistente para la optimización de motor de base de datos como ayuda en el análisis del entorno de la base de datos y en la selección de los índices adecuados.

Material y equipo necesario

Cuenta de administrador

Bases de datos ejemplo

Metodología

Un índice es una estructura de disco asociada con una tabla o una vista que acelera la recuperación de filas de la tabla o de la vista. Un índice contiene claves generadas a partir de una o varias columnas de la tabla o la vista. Dichas claves están almacenadas en una estructura (árbol b) que permite que SQL Server busque de forma rápida y eficiente la fila o filas asociadas a los valores de cada clave.

La selección de los índices apropiados para una base de datos y su carga de trabajo es una compleja operación que busca el equilibrio entre la velocidad de la consulta y el costo de actualización. Los índices estrechos, o con pocas columnas en la clave de índice, necesitan menos espacio en el disco y son menos susceptibles de provocar sobrecargas debido a su mantenimiento. Por otra parte, la ventaja de los índices anchos es que cubren más consultas. Puede que tenga que experimentar con distintos diseños antes de encontrar el índice más eficaz. Es posible agregar, modificar y quitar índices sin que esto afecte al esquema de la base de datos o al diseño de la aplicación. Por lo tanto, no debe dudar en experimentar con índices diferentes.

El optimizador de consultas de SQL Server elige de forma confiable el índice más eficaz en la mayoría de los casos. La estrategia general de diseño de índices debe proporcionar una buena selección de índices al optimizador de consultas y confiar en que tomará la decisión correcta. Así se reduce el tiempo de análisis y se obtiene un buen rendimiento en diversas situaciones. Para saber qué índices utiliza el optimizador de consultas para determinada consulta, en SQL Server Management Studio, en el menú Consulta, seleccione Incluir plan de ejecución real.

No equipare siempre la utilización de índices con un buen rendimiento ni el buen rendimiento al uso eficaz del índice. Si la utilización de un índice contribuyera siempre a producir el mejor rendimiento, el trabajo del optimizador de consultas sería muy sencillo. En realidad, una elección incorrecta de índice puede provocar un rendimiento bajo. Por tanto, la tarea del optimizador de consultas consiste en seleccionar un índice o una combinación de índices solo si mejora el rendimiento, y evitar la recuperación indizada cuando afecte al mismo.

Tareas del diseño de índices

Las siguientes tareas componen la estrategia recomendada para el diseño de índices:

  1. Comprender las características de la propia base de datos. Por ejemplo, se trata de una base de datos de procesamiento de transacciones en línea (OLTP) con modificaciones frecuentes de datos, o de una base de datos de sistema de ayuda a la toma de decisiones (DSS) o de almacenamiento de datos (OLAP) que contiene principalmente datos de solo lectura y debe procesar rápidamente conjuntos de datos muy grandes. En SQL Server 2012, el índice de almacén de columnas optimizado para memoria xVelocity resulta especialmente indicado para los conjuntos de datos típicos de almacenamiento de datos. Los índices de almacén de columnas pueden transformar la experiencia de almacenamiento de datos de los usuarios, ya que permite un rendimiento más rápido en las consultas habituales de almacenamiento de datos, como el filtrado, la agregación, la agrupación y la combinación en estrella de consultas. Para obtener más información, vea Índices de almacén de columnas.
  2. Comprender las características de las consultas utilizadas con frecuencia. Por ejemplo, saber que una consulta utilizada con frecuencia une dos o más tablas facilitará la determinación del mejor tipo de índices que se puede utilizar.
  3. Comprender las características de las columnas utilizadas en las consultas. Por ejemplo, un índice es idóneo para columnas que tienen un tipo de datos entero y además son columnas con valores únicos o no nulos. En el caso de columnas que tengan subconjuntos de datos bien definidos, puede usar un índice filtrado en SQL Server 2008 y en versiones posteriores. Para obtener más información, vea Directrices generales para diseñar índices filtrados en esta guía.
  4. Determinar qué opciones de índice podrían mejorar el rendimiento al crear o mantener el índice. Por ejemplo, la creación de un índice clúster en una tabla grande existente se beneficiaría de la opción de índice ONLINE. La opción ONLINE permite que la actividad simultánea en los datos subyacentes continúe mientras el índice se crea o regenera. Para obtener más información, vea Establecer opciones de índice.
  5. Determinar la ubicación de almacenamiento óptima para el índice. Un índice no clúster se puede almacenar en el mismo grupo de archivos que la tabla subyacente o en un grupo distinto. La ubicación de almacenamiento de índices puede mejorar el rendimiento de las consultas aumentando el rendimiento de las operaciones de E/S en disco. Por ejemplo, el almacenamiento de un índice no clúster en un grupo de archivos que se encuentra en un disco distinto que el del grupo de archivos de la tabla puede mejorar el rendimiento, ya que se pueden leer varios discos al mismo tiempo.

O bien, los índices clúster y no clúster pueden utilizar un esquema de particiones en varios grupos de archivos. Las particiones facilitan la administración de índices y tablas grandes al permitir el acceso y la administración de subconjuntos de datos rápidamente y con eficacia, mientras se mantiene la integridad de la colección global. Para obtener más información, vea Tablas e índices con particiones. Al considerar la posibilidad de utilizar particiones, determine si el índice debe alinearse; es decir, si las particiones se crean esencialmente del mismo modo que la tabla o de forma independiente.

Directrices generales para diseñar índices

Los administradores de bases de datos más experimentados pueden diseñar un buen conjunto de índices, pero esta tarea es muy compleja, consume mucho tiempo y está sujeta a errores, incluso con cargas de trabajo y bases de datos con un grado de complejidad no excesivo. La comprensión de las características de la base de datos, las consultas y las columnas de datos facilita el diseño de los índices.

Consideraciones acerca de las bases de datos

Cuando diseñe un índice, tenga en cuenta las siguientes directrices acerca de la base de datos:

Consideraciones sobre las consultas

Cuando diseñe un índice, tenga en cuenta las siguientes directrices acerca de las consultas:

Sugerencias didácticas

Directrices para diseñar el criterio de ordenación de los índices

Al definir índices, debe tenerse en cuenta si los datos de la columna de clave de índice se almacenan en orden ascendente o descendente. El orden ascendente es el predeterminado y mantiene la compatibilidad con las versiones anteriores de SQL Server. La sintaxis de las instrucciones CREATE INDEX, CREATE TABLE y ALTER TABLE admite las palabras clave ASC (ascendente) y DESC (descendente) en columnas individuales de índices y restricciones.

La especificación del orden en que se almacenan los valores de clave en un índice es de utilidad cuando las consultas que hacen referencia a la tabla tienen cláusulas ORDER BY que especifican distintas direcciones para las columnas de clave del índice. En estos casos, el índice puede eliminar la necesidad de un operador SORT en el plan de consultas, lo que aumenta la eficacia de la consulta. Por ejemplo, los compradores del departamento de compras de Adventure Works Cycles tienen que evaluar la calidad de los productos que compran a los proveedores. Los compradores están especialmente interesados en buscar productos enviados por estos proveedores con una tasa alta de rechazos. Como se muestra en la siguiente consulta, para recuperar los datos que cumplen estos criterios es necesario organizar la columna RejectedQty de la tabla Purchasing.PurchaseOrderDetail en orden descendente (de mayor a menor) y la columna ProductID en orden ascendente (de menor a mayor).

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,
    ProductID, DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;

El siguiente plan de ejecución para esta consulta muestra que el optimizador de consultas utilizó un operador SORT para devolver el conjunto de resultados en el orden especificado mediante la cláusula ORDER BY.

El plan de ejecución muestra el uso de un operador SORT Si se crea un índice con columnas de clave que coincidan con las de la cláusula ORDER BY de la consulta, se puede eliminar el operador SORT del plan de consultas y éste resulta más eficaz.

script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/js/bootstrap.min.js">

Reporte del alumno

Ejercicios de Oracle (Página), SQL Server y MySqL

Implementar una bitacora

Preentarlos en el formato acostumbrado

Video tutorial con resultados

Conckusiones<

Bibliografía preliminar

https://dbasqlserver.wordpress.com/2012/03/09/optimizacion-de-indices-sql-server/

https://dbasqlserver.wordpress.com/2012/03/09/optimizacion-de-indices-sql-server/



Anterior
Valid XHTML
home

Siguiente