|
Ajuste y optimización del rendimiento de MS SQL Server para
programadores, primera parte: Introducción a los aspectos de rendimiento
DAT 410
Presentado en Tech-Ed 97
Adam Shapiro
Director de programas
Microsoft Corporation
Objetivos de rendimiento
El
objetivo del ajuste de rendimiento consiste en proporcionar un tiempo
de respuesta aceptable en las consultas al minimizar el tráfico
de la red, reducir la entrada y salida de disco y minimizar el tiempo
de CPU para permitir el máximo rendimiento de los procesos de todos
los usuarios. Este objetivo se logra mediante el análisis de los
requisitos de las aplicaciones, la comprensión de la estructura
física y lógica de los datos, y la capacidad de evaluar
y negociar las contrapartidas entre usos conflictivos de la base de datos,
como el procesamiento de transacciones en línea (OLTP), frente
a la ayuda a la toma de decisiones.
Tiempo de respuesta frente a rendimiento
El tiempo de respuesta mide la cantidad de tiempo necesario para que
se devuelva el primer conjunto de resultados. Normalmente se hace referencia
al tiempo de respuesta como el tiempo que tarda el usuario en recibir
una afirmación visual de que una consulta está en proceso.
El rendimiento mide el número total de consultas que el servidor
puede controlar durante un espacio de tiempo dado.
A medida que aumenta el número de usuarios, aumenta la contención
entre usuarios lo que, a su vez, puede hacer que aumente el tiempo de
respuesta y descienda el rendimiento general.
Cómo se mide el rendimiento
El rendimiento se puede medir mediante la cantidad de entrada y salida
necesaria para procesar una transacción, la cantidad de tiempo
de CPU y el tiempo de respuesta. El rendimiento varía según
cada entorno específico y depende de la aplicación, la arquitectura
y recursos, el servidor y las actividades simultáneas.
Factores que tienen impacto en el rendimiento
Recursos del sistema
Memoria
La existencia de una cantidad suficiente de RAM resulta vital para el
rendimiento de SQL Server.
Procesador
El número de procesadores, así como su velocidad, tiene
un impacto directo sobre el rendimiento general.
Disco
El número, velocidad y tipo de las unidades de disco, así
como los tipos de controladores utilizados, afectan al rendimiento.
Red
La actividad de red simultánea puede tener impacto en el rendimiento
de SQL Server. También tienen importancia el ancho de banda de
la red y la velocidad de transferencia de los datos.
Sistema operativo Windows NT
Subprocesos
Al ajustar las prioridades de los subprocesos se permite que SQL Server
equilibre sus necesidades con las de los demás servicios y con
las necesidades del propio sistema operativo Microsoft Windows NT®.
El número de subprocesos asignados por SQL Server puede tener impacto
en el rendimiento.
Archivo de paginación
El tamaño, número y ubicación de los archivos de
paginación puede tener un impacto importante en el rendimiento
del sistema.
Servicios
Los demás servicios que se ejecutan en Windows NT competirán
por los recursos que necesita SQL Server. Cerrar los servicios innecesarios
puede tener un impacto positivo.
Administración de discos
Windows NT dispone de determinadas características de administración
de discos como la creación de bandas y espejos que pueden tener
impacto sobre el rendimiento. Este impacto no siempre será positivo.
Actividades simultáneas
Otras actividades, como los programas de cliente y compiladores, pueden
competir con SQL Server por ciclos de CPU, acceso de disco y ancho de
banda de la red.
SQL Server
Configuración
Muchas de las variables de configuración de SQL Server pueden
tener un impacto directo sobre el rendimiento del servidor.
Bloqueo
La disputa por los recursos de base de datos (tablas y páginas
individuales) puede hacer que los procesos se bloqueen y tiene un impacto
importante sobre el rendimiento general en un entorno multiusuario.
Registro
A excepción de las operaciones específicas no registradas,
se debe registrar cada modificación realizada en una base de datos.
La propia grabación del registro puede tener impacto en el rendimiento
y el registro de transacciones (syslogs) puede convertirse en uno
de los orígenes de la contención.
Actividades simultáneas
Las actividades de mantenimiento, como las copias de seguridad y restauraciones,
DBCC y generación de índices, pueden interferir con las
actividades de producción.
Aplicaciones de bases de datos
Diseño físico y lógico
El nivel de normalización y desnormalización puede afectar
al rendimiento de las consultas. El diseño físico incluye
la elección de índices y se explicará detalladamente.
Evitar interbloqueos
Los interbloqueos repetidos pueden disminuir el rendimiento de una aplicación.
Existen técnicas de programación que pueden reducir la posibilidad
de que se produzcan interbloqueos.
Control de transacciones
Las transacciones, bloqueos e interbloqueos están muy relacionados
entre sí. El nivel de control de transacciones que utiliza una
aplicación puede tener un impacto importante sobre la duración
de los bloqueos y el rendimiento general de una aplicación.
Consultas
La forma en que se escriben las consultas individuales, incluido el hecho
de si están encapsuladas en procedimientos almacenados o no lo
están, puede determinar si se utiliza un plan óptimo para
obtener el máximo rendimiento.
Aplicación cliente
Requisitos de usuario
Los requisitos de usuario para realizar modificaciones y ejecutar consultas
pueden tener una influencia importante en el rendimiento de una aplicación.
Control de interbloqueos
Los programas cliente pueden reaccionar ante los interbloqueos de SQL
Server de diversas formas. Las respuestas más eficaces pueden aumentar
mucho el rendimiento del sistema cliente.
Control de transacciones
Las transacciones también se pueden controlar desde la aplicación
cliente. Además, algunas aplicaciones cliente pueden utilizar instrucciones
de control de transacciones sin que el programador o el cliente sean conscientes
de las mismas.
Cursores
Hay diversas formas de definir y manipular cursores y cada una tiene
un impacto diferente sobre el rendimiento.
¿Qué se puede hacer?
El ajuste del rendimiento es más un arte que una ciencia exacta.
El objetivo del ajuste es aumentar el rendimiento al eliminar los cuellos
de botella relacionados con la entrada y salida, la CPU o la red. Se puede
realizar al reducir la cantidad de tiempo de procesamiento del sistema
mediante la optimización del servidor, de los procesos y al minimizar
la contención de los datos.
Agregar más hardware
- A veces resulta útil, aunque a menudo se pueden encontrar alternativas
menos caras.
Optimizar SQL Server
- Ajustar los valores de las opciones de configuración.
Optimizar la base de datos
- Mejorar el diseño lógico y físico.
- Escribir mejores consultas.
- Crear índices útiles.
Resolver los problemas de contención y de simultaneidad
- Coordinar la actividad de lectura frente a la de escritura.
- Minimizar la contención de bloqueos.
- Evitar los interbloqueos.
Optimizar la aplicación cliente
- Utilizar procedimientos almacenados en lugar de consultas "ad hoc".
- Analizar y establecer prioridades en la combinación de transacciones.
- Descargar datos y procesos del servidor cuando sea apropiado.
Ventajas de Windows NT
SQL Server aprovecha las capacidades mejoradas del sistema operativo
Windows NT.
Arquitectura escalable
SQL Server puede aprovechar la escalabilidad de Windows NT desde equipos
portátiles a grandes servidores de múltiples procesadores
simétricos con compatibilidad con procesadores Intel® y RISC
(conjunto de instrucciones reducido).
Alta capacidad
SQL Server puede asignar hasta 2 gigabytes (GB) de memoria, que es lo
que Windows NT permite para procesos de usuario. El tamaño de las
particiones del disco duro puede ser de, aproximadamente, diecisiete mil
millones de GB (con NTFS).
Multiprocesamiento simétrico (SMP, Symmetric Multiprocessing)
Windows NT es un sistema operativo que puede utilizar SMP. Puede ejecutar
código de ambos sistemas operativos y código de usuario
en cualquier procesador disponible. Cuando hay más subprocesos
para ejecutar que procesadores para ejecutarlos, el sistema operativo
SMP también realiza multitarea y divide el tiempo de cada procesador
entre todos los subprocesos que hay en espera.
SQL Server aprovecha las capacidades de multiproceso de Windows NT. SQL
Server, en lugar de implementar su propio motor de procesamiento, utiliza
subprocesos independientes basados en Windows NT para dar servicio a cada
cliente. Windows NT equilibra la carga y programa los subprocesos entre
los procesadores automáticamente.
En equipos SMP, puede utilizar SQL Server para dedicar todos los recursos
de la CPU a SQL Server.
Procesos únicos en multiproceso
SQL Server admite SMP en el nivel de subproceso y aprovecha el control
de subprocesos de Windows NT de las siguientes formas:
- La arquitectura de un proceso dividido en múltiples procesos
reduce la sobrecarga del sistema y los requisitos de memoria.
- Las aplicaciones divididas en procesos múltiples utilizan un
único espacio de direcciones. Dado que todos los subprocesos
pertenecen al mismo proceso, se elimina la necesidad de coordinar los
procesos de memoria compartida.
E/S asincrónica
Windows NT utiliza E/S asincrónica en la que una aplicación
envía una petición de E/S y continúa su ejecución
mientras el dispositivo transfiere los datos. Esto difiere de un sistema
de E/S sincrónico, que no devuelve el control a la aplicación
hasta que no se completa la petición de E/S. SQL Server aprovecha
la E/S asincrónica de Windows NT y proporciona mayor rendimiento.
Utiliza servicios de Windows NT
SQL Server utiliza los servicios de Windows NT en subprocesos, programaciones,
notificación de sucesos, sincronización de procesos, E/S
asincrónica, control de excepciones y seguridad integrada.
SQL Server:
- Utiliza el registro de sucesos de Windows NT además del registro
de errores de SQL Server.
- Aprovecha el equilibrio automático de la carga de Windows NT.
- Está completamente integrado con el Monitor de rendimiento
de Windows NT.
- Puede aprovechar la seguridad de Windows NT para proporcionar inicios
de sesión y contraseñas integrados.
Metodología de optimización del rendimiento
Esta metodología de optimización del rendimiento proporciona
un punto de partida para ajustar correctamente una base de datos para
obtener un mejor rendimiento. Esta metodología también sirve
como marco de trabajo para los temas de este curso.
Metodología de optimización del rendimiento (continuación)
Los pasos incluidos en esta metodología se pueden realizar en
una secuencia diferente de la mostrada aquí, también se
pueden eliminar algunos pasos, según la fase de producción
del entorno de base de datos.
Enfoques de la optimización del rendimiento
La optimización se puede enfocar de dos formas diferentes.
En este curso, aprenderá cómo SQL Server tiene acceso a
los datos, cómo controla las actividades simultáneas de
múltiples usuarios y cómo interactúa con el sistema
operativo. Puede utilizar estos conocimientos para planear el diseño
lógico y físico, configurar SQL Server, planear las transacciones
y escribir las consultas para obtener un rendimiento óptimo.
Como alternativa, se puede enfocar la optimización para tratar
un problema específico. Una consulta se puede ejecutar lentamente
o el rendimiento puede ser menor que el necesario. Puede obtener información
acerca de la forma en que SQL Server se comporta y realizar los ajustes
necesarios en la consulta y en la configuración del sistema para
obtener el rendimiento óptimo.
Ambos enfoques son necesarios. Si tiene conocimientos profundos acerca
del servidor, los usuarios, los datos y los procesos, pero no dispone
de información del rendimiento, no será consciente de que
su teóricamente bien diseñada aplicación no funciona
tan bien como podría hacerlo. A la inversa, si dispone de todas
las mediciones de rendimiento posibles, pero no conoce su aplicación
o el servidor, será consciente de que tiene problemas de rendimiento,
pero no sabrá cómo resolverlos.
Introducción a las estrategias de indizado
Objetivos
- Seleccionar índices que sean útiles para diferentes
tipos de consultas.
- Comparar y contrastar las estrategias de indizado de los sistemas
de ayuda a la toma de decisiones (DSS) y de procesamiento de transacciones
en línea (OLTP).
- Crear índices efectivos.
- Distinguir entre selectividad y densidad de unión.
- Determinar cuándo no son útiles los índices.
- Probar la utilidad de los índices.
DSS y OLTP
La mayor parte de las consultas se dividen en estas dos categorías.
Puesto que sus aspectos de indicación pueden ser muy diferentes,
se explicarán ambas categorías por separado.
Sistemas de ayuda a la toma de decisiones
Normalmente, la ayuda a la toma de decisiones implica varios argumentos
de búsqueda y varias tablas. Las consultas pueden ser bastante
complejas, ya que pueden utilizar funciones de agregado, agrupamiento
y las operaciones CUBE y ROLLUP. Algunas veces, también se hace
referencia a las mismas como procesamiento analítico en línea
(OLAP).
Las consultas pueden ser arbitrarias e impredecibles y pueden utilizar
casi cualquier columna para especificar las filas que se desean.
La velocidad de recuperación y la devolución de los resultados
son los aspectos más críticos de estos tipos de consultas.
Procesamiento de transacciones en línea (OLTP)
OLTP implica frecuentemente a una sola tabla y, normalmente, sólo
afecta a un pequeño número de filas. En las operaciones
INSERT, las transacciones de una aplicación OLTP a veces sólo
insertan una sola fila.
A menudo, con OLTP, las consultas son más predecibles que con
la ayuda a la toma de decisiones.
La velocidad de modificación de los datos es el aspecto más
crítico de las consultas OLTP.
Indización para recuperaciones
Crear índices útiles
Probablemente, la creación de índices útiles es
la acción más importante que se puede realizar para mejorar
el rendimiento. El tipo y número de índices, así
como las columnas que se van a indizar se deben seleccionar cuidadosamente
gracias a una profunda comprensión de las necesidades del usuario
y de los propios datos. Los índices resultan útiles si sólo
va a consultar la tabla o realizar modificaciones en los datos. En ambos
casos, los índices pueden proporcionar un acceso más rápido
a los datos, ya sea con propósito de leer o de escribir.
Análisis del usuario
Comprenda las peticiones del usuario acerca de los datos, así
como de los tipos y frecuencia de las consultas que realiza usualmente.
Tener un profundo conocimiento de las necesidades del usuario ayudará
a determinar las contrapartidas que, probablemente, necesitará
realizar. Para equilibrar el rendimiento de las consultas más vitales,
tendrá que sacrificar la velocidad de algunas consultas para aumentar
el rendimiento de otras.
Análisis de los datos
Comprender los datos y cómo están organizados en el diseño
lógico y en el físico.
Comprender cómo funciona SQL Server
Cuanto más profundamente conozca cómo funciona SQL Server,
mejor podrá diseñar el sistema y tomar decisiones inteligentes.
Esto incluye comprender cómo SQL Server almacena y recupera los
datos, así cómo selecciona el optimizador de consultas el
plan de ejecución más eficaz.
Consideraciones generales
- Normalmente, el optimizador de consultas sólo utiliza un índice
por tabla de la consulta.
- Para que una consulta que se realiza en una tabla grande se ejecute
rápidamente, resulta útil tener un índice en las
columnas de la cláusula WHERE.
- Para determinar un número razonable de índices, deberá
tener en cuenta la frecuencia de las actualizaciones frente a las recuperaciones.
- Se deben elegir los índices según los tipos de cláusulas
WHERE o combinaciones que realizará.
- La clave para seleccionar índices consiste en ser cuidadoso
al elegir el tipo y número de índices. Determine el número
mínimo que debe crear para que el rendimiento no se vea afectado
debido al mantenimiento. Determine los índices más útiles.
- No cree un índice si no lo va a utilizar.
- El optimizador de consultas toma la decisión final acerca de
si el índice se utilizará.
Selectividad
Estimar los resultados resultará útil al seleccionar los
tipos de índices que se van a crear en una tabla para un conjunto
de transacciones determinado.
La selectividad de una consulta es el porcentaje de filas de una tabla
a la que se tiene acceso mediante una instrucción SELECT, UPDATE
o DELETE. Una selectividad alta puede devolver una fila que cumpla los
criterios de búsqueda. La selectividad baja no es lo mismo que
discriminar y puede devolver la mayor parte de las filas de una tabla.
Un concepto relacionado es la densidad, que es el porcentaje de
filas duplicadas en un índice. Un índice que tiene un gran
número filas duplicadas tiene una densidad alta. Un índice
único tiene densidad baja.
Exploraciones de tablas
Explorar la tabla resulta ventajoso para las consultas en que el conjunto
de resultados incluye un alto porcentaje de una tabla (selectividad baja).
Distribución de los datos
La distribución de los datos indica el intervalo de valores de
una tabla determinada y cuántas filas entran en ese intervalo.
En muchos casos, se puede predecir aproximadamente el porcentaje de datos
que se devolverán en un conjunto de resultados. Por ejemplo, si
el criterio es hombre/mujer, el conjunto de resultados para mujer se puede
estimar en el 50%.
Puede determinar la distribución de una columna con una consulta
como la siguiente:
SELECT columna, count(*)
FROM tabla
GROUP BY columna
Selectividad: ejemplo
En el ejemplo anterior, ambos conjuntos tienen el mismo número
de X, aunque el porcentaje de X (selectividad) es diferente.
Estime la selectividad de estas consultas (suponga que hay 10.000 filas
en la tabla member y que los números de miembro están
en el intervalo de 1 a 10.000; todos los valores son únicos):
Densidad de combinaciones
La densidad de combinaciones es el promedio de filas de la tabla interna
que coincidirán con una fila de tabla externa. También se
puede pensar en la densidad de combinaciones como en el número
promedio de duplicados.
Una columna con un índice único tendría una densidad
baja y una selectividad de combinación alta. Si la columna tiene
un gran número de duplicados, tiene una densidad alta y no es muy
selectiva para las combinaciones.
Los índices de la columna de combinación sólo resultarán
útiles en la tabla interna de una combinación. Si la densidad
de combinación fuera baja, ambos tipos de índice resultarían
útiles. Si la densidad de combinación fuera alta, sólo
resultaría útil un índice agrupado.
Crear el tipo de índice apropiado
Buenos candidatos para índices agrupados
Consideraciones
La columna utilizada para el índice agrupado determina el orden
físico de los datos. Coloque un índice agrupado en las columnas
de datos que necesite con más frecuencia en orden físico.
Se deben elegir los índices según el tipo de instrucciones
SELECT utilizadas.
Se recomienda utilizar índices agrupados para claves externas,
puesto que generalmente las claves externas no son únicas.
Sólo puede haber un índice agrupado por tabla.
Nota Colocar un índice agrupado en la clave principal (especialmente
si los datos son monótonos) no suele ser la mejor opción.
La clave principal debe tener un índice único. En muchos
casos, un índice único no agrupado puede ser casi tan eficaz
como un índice único agrupado.
Buenos candidatos para índices no agrupados
Agregue índices no agrupados solamente cuando vayan a resultar
realmente útiles, ya que se requiere una importante sobrecarga
para mantener estos índices al modificar datos.
No cree un índice si no lo va a utilizar.
Consideraciones
- Requisitos de espacio de almacenamiento.
- Impacto de la modificación de datos.
- Volatilidad de la columna candidata.
- Selectividad de las consultas. (La exploración de tablas es
más apropiada para consultas de selectividad baja).
- Unicidad de las columnas candidatas. (Un nivel alto de duplicados
disminuye la efectividad del índice).
- Los índices no agrupados no son útiles para consultas
de intervalos a menos que el intervalo suponga un pequeño porcentaje
de la tabla.
Buenos candidatos para índices compuestos
Un índice compuesto puede ser un índice con una clave de
ordenación de varias columnas. Los índices agrupados y los
no agrupados pueden tener una clave compuesta.
Consideraciones
- Los índices compuestos son útiles si la primera columna
de la clave se especifica en la cláusula WHERE.
- Son candidatos poco recomendables los índices demasiado amplios
en los que sólo se utiliza la segunda o la tercera columna en
la cláusula WHERE.
Por ejemplo, un índice sobre (lastname, firstname)
es bueno para seleccionar lastname y lastname, name,
pero no es bueno para seleccionar name.
Índices compuestos frente a varios índices de una columna
- Tener varios índices puede afectar al rendimiento de las instrucciones
de modificación de datos.
- Normalmente, sólo se utiliza un índice por tabla en
una consulta, por lo que usar varios índices no resulta tan útil
como los índices compuestos.
- Un índice compuesto puede ser una elección mejor para
las consultas que tengan acceso a varias columnas de una sola tabla.
Nota El orden en que se especifican las columnas en la cláusula
WHERE no afecta a la forma en que se utilizan los índices compuestos.
Sólo importa que la columna situada más a la izquierda del
índice compuesto esté contenida en la cláusula WHERE.
Se puede utilizar un índice compuesto aunque la columna
de orden más alto (la situada más a la izquierda) de la
clave de ordenación del índice no esté en la cláusula
WHERE. Esta situación requiere que todas las columnas a
las que se hace referencia en la lista SELECT y en la cláusula
WHERE estén en la clave de ordenación del índice.
Consulte índices de cobertura (a continuación) para obtener
detalles.
Indices de cobertura
Consideraciones
- Agregue columnas a algunos índices para dar cobertura a la
mayor parte de las consultas.
- No deje que la clave del índice sea demasiado amplia. Esto
aumenta el tamaño del índice y se anulan las ventajas
de rendimiento. Si las filas son demasiado amplias, aumenta el número
de niveles y , por lo tanto, el número de páginas. Si
aumenta el número de páginas, también aumenta la
cantidad de tiempo necesario para explorar un índice.
- Agregar más índices no agrupados afecta de forma adversa
al rendimiento de las actualizaciones.
- En algunos casos puede resultar efectivo para combinar dos índices
en un índice de cobertura compuesto.
- Los índices no agrupados que proporcionan cobertura a una consulta
de selectividad baja son muy rápidos, ya que nunca se tiene acceso
a las páginas de datos y, por lo tanto, se evita explorar la
tabla.
- Un índice de cobertura puede resultar útil aunque la
columna de mayor orden de la clave de ordenación del índice
no esté en la cláusula WHERE.
Índices agrupados frente a índices no agrupados
Hacer coincidir el índice con la consulta
Indizar un intervalo de datos: ejemplo
No hay un índice en la tabla
- Una exploración de tabla (53.000 operaciones de E/S) resulta
más eficaz que un índice no agrupado.
Índice agrupado sobre la columna price
- Índice agrupado sobre la columna price
- Lee filas a partir de 20,00 dólares y detiene la búsqueda
en 30,00 dólares.
- Puesto que la columna price está agrupada, el orden
físico de los datos se organiza según el precio. Todos
los datos que entran en el intervalo están en orden secuencial
en las páginas siguientes, con lo que se facilita la recuperación
de datos.
- Esta búsqueda necesita leer 10.000 páginas (190.000/19
filas por página).
Índice no agrupado sobre la columna price
- Busca en el índice no agrupado fila a fila para determinar
si cada fila cumple los criterios de la búsqueda.
- En todas las filas que cumplan los criterios, es necesario recuperar
los datos de cada página que almacene una fila.
- Esta búsqueda es el peor caso, ya que es necesario leer 190.000
páginas de datos (una por cada vez que aparezca), más
el nivel de hoja del índice. (Se lee varias veces cada página
de datos en la caché).
Índice de cobertura en las columnas price, title
- Puesto que las columnas price y title están en
el índice, no es necesario buscar en las páginas de datos
y se ahorran operaciones de entrada y salida.
- En el índice (price, title), hay un promedio
de 38 filas de índice por página de hoja. Esta búsqueda
necesita leer 5.000 páginas (190.000/38 filas por página).
Indices para AND: ejemplo
Observe el ejemplo de la diapositiva. Utilice las opciones enumeradas
en las notas del alumno para evaluar el mejor tipo de índice que
se puede crear para esta instrucción. El número 4 es la
mejor opción, puesto que todas las filas calificadas estarían
juntas. Con la opción número 6 se proporcionaría
cobertura a la consulta. Sin embargo, el índice sería mayor,
debido a la clave amplia. La opción número 7 es la misma
que utilizar un índice agrupado sobre dept, excepto que
el índice es mucho mayor.
Un error habitual consiste en pensar que el orden en que se enumeran
las columnas en la cláusula WHERE afecta a la forma en que se utilizan
los índices compuestos. Esto no es cierto. Sólo importa
que la columna situada más a la izquierda del índice compuesto
esté en la cláusula WHERE.
También puede observar que suponemos que hay una distribución
relativamente normal de los datos. Si todos o ninguno ganara > 50.000
o si se buscara en toda la compañía, la estrategia de indizado
sería diferente.
Si se cumplen ambas condiciones, la fila cumplirá el criterio
de búsqueda.
Opciones para evaluar en el ejemplo anterior
- Índice agrupado o no agrupado sobre dept.
- Índice agrupado o no agrupado sobre salary.
- Uno sobre dept y otro sobre salary.
- Índice compuesto sobre (dept, salary).
- Índice compuesto sobre (salary, dept).
- Índice no agrupado sobre (dept, salary, name,
address) (índice de cobertura).
- Índice agrupado sobre (dept, salary, name,
address).
Índices para OR
Las operaciones OR implican una situación muy diferente de las
operaciones AND.
Cuando hay varias condiciones en que se utiliza AND, se proporciona una
calificación progresivamente más estricta de los datos deseados.
Dado el conjunto de todas las filas que cumplen una de las condiciones
AND, las filas del resultado final estarán en dicho conjunto.
Con las condiciones OR, esto no se cumple. Dado el conjunto de todas
las filas que cumplen una de las condiciones OR, puede haber filas que
cumplan alguna de las demás condiciones OR que no estén
incluidas en este conjunto de resultados.
Las operaciones OR se explicarán con más detalle más
adelante en este curso.
Índices para SELECT *: ejemplo
SELECT * devuelve información de todas las columnas.
La selección del índice no tiene nada que ver con los datos
seleccionados. Sólo es significativo con lo que se califica. El
índice ayuda a encontrar filas. No afecta a la información
que se selecciona de una fila. No se puede proporcionar cobertura a una
instrucción SELECT * con un índice no agrupado a menos que
todas las columnas estén en la clave de ordenación.
La instrucción SELECT * no es el mejor tipo de consulta que se
puede utilizar para obtener un alto rendimiento si implica que se recuperarán
más datos de los que realmente se necesitan.
Opciones para evaluar en el ejemplo anterior
- Índice agrupado sobre la columna au_id.
- Al crear un índice agrupado sobre la columna au_id se
guardan todas las filas en ese orden.
- Índice no agrupado sobre la columna au_id.
- Así sería necesario tener acceso a la página
de datos. Potencialmente implicaría más operaciones de
E/S.
- Índice no agrupado sobre (au_id, au_lname).
Índices para consultas múltiples
En los ejemplos anteriores, se seleccionaba el mejor tipo de índice
basado en una consulta individual. La indización de consultas múltiples
es más compleja, ya que el índice óptimo para una
consulta puede no serlo para otra. El objetivo es obtener un rendimiento
aceptable para todas las consultas de prioridad más alta.
Opciones para evaluar en el ejemplo anterior
Supuestos: La consulta 1 es un 15% de la tabla. La consulta 2 es muy
selectiva; se tiene acceso a una fila.
Opción 1
- Índice agrupado sobre (price).
- Índice no agrupado sobre (title).
La consulta 1 es muy rápida. La consulta 2 es rápida, pero
requiere más operaciones de E/S que si se hubiera colocado un índice
agrupado sobre la columna title.
Opción 2
- Índice no agrupado sobre (price).
- Índice agrupado sobre (title).
La consulta 1 es más lenta que en la opción 1. La consulta
2 es muy rápida.
Opción 3
- Índice agrupado sobre (price).
- Índice no agrupado sobre (title, price).
La consulta 1 se ejecuta rápidamente y la consulta 2 es muy rápida.
Opción 4
- Índice no agrupado sobre (price, title).
- Índice agrupado sobre (title).
Se trata de la mejor opción. La consulta 1 y la consulta 2 son
muy rápidas.
Consideraciones sobre la actualización
Las consultas de procesamiento de transacciones en línea (OLTP)
incluyen algunos aspectos de recuperación de datos en los que se
deben buscar las filas para, después, poder modificarlas. Sin embargo,
si las consultas más importantes son de OLTP, hay algunos aspectos
adicionales que se deben tener en cuenta:
- Se deben mantener los índices. Cada modificación realizada
en una tabla indizada significará que será necesario actualizar
un índice o quizá más. Cuantas más columnas
tenga el índice, más trabajo será necesario realizar
para mantenerlo.
- Además de registrar cada fila de datos que se modifica, habrá
que registrar cada fila de índice que se modifique.
Directrices
- Para una aplicación que utilice principalmente OLTP, mantenga
el número de índices al mínimo.
- Las columnas de índices agrupados deben ser no volátiles.
Directrices para la indización
Mantenimiento de los índices
Índices agrupados
Sí hay un índice agrupado sobre una tabla, se deben insertar
las filas en el orden de la clave del índice agrupado. Si no hay
espacio en una página, es posible que sea necesario dividirla,
con lo que se generará una sobrecarga adicional.
Índices no agrupados
Un índice no agrupado tiene un puntero a cada fila de datos. Cada
vez que se inserta o elimina una fila, se deben ajustartodos los
índices no agrupados.
Si una operación UPDATE implica operaciones DELETE/INSERT completas
o se trata de una operación UPDATE diferida, se deben ajustar todos
los índices no agrupados para las filas eliminadas y las filas
insertadas. Aunque se realiza una operación UPDATE en un sitio
o en la misma página, será necesario ajustar todos los índices
que haya sobre cualquier columna que se modifique. Si dispone de índices
compuestos amplios, ya de por sí pueden suponer una gran sobrecarga.
Directrices para crear índices
Determinar la prioridad de todas las consultas
- Tener una comprensión profunda de todos los datos y de cómo
se utilizarán.
- Determinar las transacciones prioritarias para la base de datos.
Determinar la selectividad de cada consulta
- Determinar la selectividad de cada parte de la cláusula WHERE.
Representar la actividad de cada tabla
- Analizar la actividad que se produce sobre cada columna de la tabla.
Determinar las columnas que se deben indizar
- ¿Se utiliza la columna en la cláusula WHERE?
Si nunca se hace referencia a una columna en la cláusula WHERE
de una consulta o en la instrucción de modificación
de datos, no hay motivo para crear un índice sobre esa columna.
- ¿Se utiliza la columna como clave de combinación?
La creación de un índice sobre una columna que se utiliza
como clave de combinación mejora el rendimiento de la combinación,
ya que proporciona al optimizador de consultas la posibilidad de utilizar
un índice en lugar de realizar una exploración de la
tabla.
- ¿Se busca frecuentemente en esa columna?
Seleccionar la mejor columna candidata para un índice agrupado
Determinar qué otros índices son necesarios
- Determine el número mínimo de índices que se
pueden crear para cada tabla.
- Equilibre la ganancia de rendimiento del índice frente el mantenimiento
debido a actualizaciones.
- Compruebe si las columnas a las que se hace referencia en las cláusulas
WHERE de las consultas de más prioridad están indizadas.
- Si una consulta se ejecuta con poca frecuencia, tendrá que
considerar la creación de un índice mientras dure la actividad
específica para luego quitarlo. Por ejemplo, si todos los análisis
de informes o de resúmenes se producen a final de mes o final
de año, se pueden crear índices para utilizarlos durante
esas actividades y después se pueden quitar.
Determinar qué tipos de índices no agrupados se deben
crear
Probar el rendimiento de las consultas
- Después de crear los índices, pruebe el rendimiento
de las consultas de prioridad más alta.
- Ejecute SET SHOWPLAN ON, SET STATISTICS IO ON, SET STATISTICS TIME
ON y, a continuación, ejecute cada consulta.
Cuándo no se deben utilizar índices
Hay situaciones en que no deseará indizar. Entre estas situaciones
se incluyen:
- Si el optimizador nunca utiliza el índice.
- Si se va a devolver más de un 10 a 20% de las filas.
- Si la columna contiene solamente uno, dos o tres valores únicos
(selectividad baja).
- Si la columna que va a indizar es grande (> 20 bytes).
- Si la sobrecarga de mantener el índice es mayor que sus ventajas.
- Si la tabla es muy pequeña.
Equilibrar DSS con OLTP
Puesto que los requisitos de indizado son muy diferentes en los entornos
de DSS y OLTP, puede resultar muy difícil decidir la estrategia
de indizado si ambos entornos son necesarios.
Se pueden mantener copias independientes de los datos para que la recuperación
y modificación no se realicen exactamente sobre los mismos datos.
En este caso, hará falta una estrategia para reconciliar los datos.
Habrá que contrastar los costos y ventajas de indizar óptimamente
para ambos entornos con el costo que supondría mantener y reconciliar
dos conjuntos de datos.
Introducción al optimizador de consultas
El optimizador de consultas de SQL Server decide si un índice
es realmente bueno y, para cualquier consulta, qué índice
es el mejor para utilizarlo. El optimizador también decide cómo
se procesarán las combinaciones de varias tablas y seleccionará
el orden de las tablas y el método. También decide la mejor
forma de realizar las operaciones de actualización.
En el siguiente módulo se explicarán detalles acerca de
cómo obtiene y utiliza el optimizador de consultas de SQL Server
la información disponible para decidir el plan óptimo de
ejecución.
© 1997, Microsoft Corporation. Reservados todos los derechos.
La información contenida en este documento representa la visión
actual de Microsoft Corporation acerca de los asuntos abordados en la
fecha de su publicación. Como Microsoft debe responder a condiciones
de mercado variables, no debe interpretarse como un compromiso por parte
de Microsoft y Microsoft no puede garantizar la precisión de la
información que se presenta después de la fecha de publicación.
Este documento se proporciona con propósito informativo únicamente.
MICROSOFT NO CONCEDE GARANTÍAS, EXPRESAS O IMPLÍCITAS, EN
ESTE DOCUMENTO.
Microsoft y Windows NT son marcas registradas de Microsoft Corporation.
Intel es una marca registrada de Intel Corporation.
Otros nombres de productos y compañías mencionados aquí
pueden ser marcas comerciales de sus respectivos propietarios.
|
