R: & SQL Q Fine Tuning para un rendimiento óptimo
Índices duplicados, las operaciones de restauración cancelada y espigas de I/O pueden causar problemas de rendimiento, pero puede trabajar alrededor de ellos.
Paul S. Randal
Índices duplicados
**Q.**SQL Server parece que me permiten crear índices que son exactamente iguales en la misma tabla. ¿Cómo esto ayuda mi rendimiento de carga de trabajo? ¿Usará distintas consultas copias diferentes del mismo índice?
**A.**Es lamentable que SQL Server permite índices duplicados, ya que no proporcionan ningún beneficio alguno. De hecho, los índices duplicados pueden ser perjudiciales en muchos sentidos.
Un índice duplicado se produce cuando las claves de índice son exactamente lo mismo que otro índice, especificada en el mismo orden y con la misma especificación ASC o DESC. Las columnas incluyen (si existe) también son las mismas (aunque las columnas incluidas pueden especificarse en cualquier orden).
SQL Server sólo usar uno de los índices duplicados para ayudar con las consultas, pero debe mantener todos los índices de una tabla durante inserción, actualización y eliminación operaciones. Esto significa que cada vez que hay una acción insert o delete en la tabla, debe reflejarse en todos los índices. Lo mismo es cierto para las actualizaciones, si las columnas se actualiza forman parte del índice.
Este mantenimiento de índices utiliza recursos adicionales y genera registros de transacciones adicionales: todos por índices que son esencialmente inútiles. Estos índices duplicados tener espacio extra en disco y espacio adicional en copias de seguridad — y las páginas necesarias para el mantenimiento de índices toman demasiado espacio adicional en la memoria.
Índices duplicados son propensas a fragmentarse. También requieren recursos adicionales durante la eliminación de fragmentación del índice normal. El registro de transacciones adicionales registros de mantenimiento de índices y eliminación de fragmentación también puede conducir a disminuir el desempeño de funciones de alta disponibilidad (HA) como reflejo de la base de datos y la replicación transaccional.
SQL Server no proporciona ninguna advertencia que sólo ha creado un índice duplicado, por lo que la responsabilidad recae sobre usted para evitar hacerlo. Comprobar si ya tiene índices duplicados no es pequeña. Se trata de secuencias de comandos todas las definiciones de índices y manualmente comparando ellos o extensa programación análisis de los catálogos del sistema. El año pasado, Kimberly Tripp publicado un completa solución a este problema.
Cuidado con la restauración
**Q.**Recientemente he tenido que cancelar una actualización de larga duración. Después de la operación de deshace, el siguiente backup diario de registro de transacción era enorme. Esperaba que sea muy pequeño, ya que nada había cambiado en la base de datos. ¿Puede explicar este anomoly?
**A.**Se trata de un error bastante común. ¿Si revertir una gran operación, la siguiente copia de seguridad diferencial debe ser pequeño, derecha? Mal.
Cualquier momento que SQL Server realiza un cambio en la base de datos, suceden dos cosas. En primer lugar, genera registros de transacciones que describen el cambio. En segundo lugar, para cualquier archivo de datos páginas modificadas por el cambio, se establece el bit correspondiente en un mapa de bits diferencial. Esto significa que esas páginas deben ser respaldadas por la siguiente copia de seguridad diferencial.
Cuando restaure una operación, SQL Server tiene que deshacer los cambios la operación hecha. Esto significa que examina todos los registros de registro de transacciones generados por la parte delantera de la operación. Tiene que deshacer los cambios en el orden inverso. Cada registro de transacciones describe un solo cambio a la base de datos como parte de la operación. Para revertir ese cambio, tienes que hacer otro cambio en la base de datos que niega el efecto del cambio original. Por ejemplo, usted podría revertir una inserción de registro eliminando el registro. El efecto neto es que el registro no existe.
Aquí es la parte más confusa: cada cambio realizado durante la restauración es realmente un cambio de base de datos (aunque uno especial). Para cada cambio en la base de datos, debe existir un registro de transacciones. Así que incluso los cambios realizados durante una restauración deben estar correctamente conectados. Esto significa que una reversión de la gran operación generará no solo registro registros de transacciones para la parte delantera de la operación, sino también para la restauración. Copias de seguridad de registro de transacciones copia todos los registros de transacciones, contabilidad para la copia de seguridad del registro de transacciones grandes.
Cuando la parte delantera de la operación hace que el mapa de bits diferencial tienen bits porque partes de la base de datos han cambiado, no se puede borrar los bits en el mapa de bits diferencial nuevamente porque ha cambiado la base de datos. No importa si el cambio finalmente se deshace. Las páginas del archivo de datos han cambiado todavía (dos veces, de hecho) y por lo tanto deben ser respaldadas por la copia de seguridad diferencial.
El quid de la cuestión es que incluso cuando una operación se deshace, todavía se cambia la base de datos. Todos los backups deben reflejar esos cambios.
En busca de espigas
**Q.**Estoy solución de un problema donde vemos periódicos I/O de espigas de uno de nuestros servidores SQL. He reducido hasta los puestos de control usando PerfMon, pero no puedo decir de qué base de datos es el principal culpable. ¿Cómo puedo que perforar aún más?
**A.**Los puestos de control existen por dos razones. En primer lugar, que se actualizan las páginas del archivo de datos con lo que se ha escrito en el registro de transacciones. SQL Server utiliza un mecanismo denominado registro de escritura anticipada, donde se describen los cambios de la base de datos en el registro de transacciones antes de que se refleja en los archivos de datos. Esto garantiza la durabilidad de los cambios en el caso de un accidente. En segundo lugar, reducen la cantidad de carga de E/s constante escribiendo sólo los datos cambiados páginas del archivo periódicamente, en lugar de hacerlo después de cada cambio a cada página del archivo de datos.
Los puestos de control se producen por separado para cada base de datos. Están activa basada en una serie de factores, incluyendo el intervalo de recuperación: éste es el servidor de SQL estimar que suficiente de las transacciones se ha generado desde el último punto de comprobación para que choque recuperación tardará aproximadamente un minuto (por defecto).
Esta cifra equivale a la generación de decenas de miles de registros de transacción individual. Las páginas de archivos de datos más cambian por estos registros de registro de transacciones, mayor la cantidad de E/s que deben realizarse por puestos de control de base de datos.
Puede realizar un seguimiento de los puntos de comprobación utilizando el contador "Checkpoint páginas/seg" en SQL Server: Buffer Manager performance object. Sólo da un recuento total a través de todas las bases de datos en la instancia de SQL Server. Para determinar qué base de datos se está "marcada" en cualquier momento, deberás utilizar indicadores de traza.
Si enciende los indicadores de traza 3502 (seguimiento de impresión cuando se produce un punto de control), 3504 (imprimir detalles de seguimiento sobre el punto de control) y 3605 (permitir rastrear imprime para ir al registro de errores), serás capaz de determinar qué base de datos es contabilidad para los picos de I/O debido a los puestos de control.
Puede habilitar estos indicadores de seguimiento mediante el comando:
DBCC TRACEON (3502, 3504, 3605, -1)
Deshabilitar nuevamente mediante el comando:
DBCC TRACEOFF (3502, 3504, 3605, -1)
Puntos de comprobación posteriores producirá salida similar al siguiente en el registro de errores:
2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 started (8) 2011-12-30 05:07:14.390 spid17s About to log Checkpoint begin. 2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 phase 1 ended (8) 2011-12-30 05:07:14.830 spid17s FlushCache: cleaned up 4307 bufs with 201 writes in 441 ms (avoided 23 new dirty bufs) 2011-12-30 05:07:14.830 spid17s average throughput: 76.30 MB/sec, I/O saturation: 198, context switches 392 2011-12-30 05:07:14.830 spid17s last target outstanding: 15, avgWriteLatency 2 2011-12-30 05:07:14.830 spid17s About to log Checkpoint end. 2011-12-30 05:07:14.830 spid17s Ckpt dbid 21 complete
Esto le permite ver qué base de datos está marcada y coinciden con la información de PerfMon. A continuación, puede investigar por qué hay tantos datos ser cambiados entre los puestos de control, realizar los puestos de control más frecuentes para reducir la espiga de I/O, o aumentar la capacidad del subsistema de E/s.
Preocupaciones de consolidación
**Q.**Mi empresa ha establecido una nueva política que requiere que consolidar lo más posible para reducir los costos de hardware. Yo estoy siendo empujado para reducir el número de instancias de SQL Server para ahorrar costos de licencias. ¿Existen directrices a cuántas bases de datos por cada instancia de SQL Server tienen sentido?
**A.**La respuesta a esta pregunta es un gran "depende". La lista de factores incluye el tamaño de las bases de datos, los tipos de cargas de trabajo que se están ejecutando, la volatilidad de los datos, el tipo de mantenimiento necesario y la recuperación ante desastres y requisitos de alta disponibilidad.
Cada instancia de SQL Server tiene una cantidad limitada de espacio en memoria para almacenar páginas de archivo de datos está procesadas en cualquier momento (esto se conoce como el pool de buffer). Las bases de datos más tienes en una instancia con diferentes cargas de trabajo que requieren procesamiento todos, más competencia habrá entre las cargas de trabajo para el espacio de búfer en la agrupación.
Esto puede conducir a golpear la memoria búfer. Habrá renovación constante para hacer espacio para nuevas páginas de archivo de datos se lee desde el disco. También habrá grandes cantidades de lectura I/O con latencias de lectura más que aceptable. Todos estos factores degradan el rendimiento de la carga de trabajo.
Si las distintas cargas de trabajo conllevan cambios de base de datos, también será I/O de escritura de los puestos de control periódicos. Con numerosas bases de datos consolidadas en una sola instancia, puede haber múltiples puntos de comprobación que se producen simultáneamente. Esto podría causar las latencias de I/O de escritura — frenando las operaciones de control y seguir contribuyendo a la degradación de la performance de la carga de trabajo.
Mantenimiento regular de bases de datos también se convierte en un problema con un gran número de bases de datos. Si cada base de datos requiere índice y mantenimiento de estadísticas, comprobación de la consistencia y copias de seguridad, puede ser un desafío para programar todas estas operaciones para todas las bases para que no entren en conflicto entre sí y poner más carga de E/s en el servidor.
Las bases de datos más que están en una instancia, más difícil se vuelve a utilizar las tecnologías nativas de alta disponibilidad de SQL Server para protegerlos a todos. Es más probable que necesitará algún tipo de tecnología de replicación a nivel de subsistema de E/s, incluso desde una perspectiva de la facilidad de administración. Esto significa inversión de capital adicional que podría compensar el ahorro en costos de consolidación de servidores.
La consolidación es un tema enorme. Plenamente haciendo justicia está fuera del alcance de una sola columna. Esto es suficiente para la reflexión para hacerte cauteloso de over-consolidating. En la otra cara, es posible que tenga muchas bases de datos pequeñas con cargas de trabajo mínimas que podría alojar en una sola instancia sin problemas. Como dije antes, depende.
.jpg)
Paul S. Randal es el director de SQLskills.com, un director regional de Microsoft y un MVP de SQL Server. Trabajó en el equipo de motor de almacenamiento de SQL Server en Microsoft desde 1999 a 2007. Escribió DBCC CHECKDB y reparación para SQL Server 2005 y fue responsable por el motor de almacenamiento básico durante el desarrollo de SQL Server 2008. Randal es un experto en recuperación ante desastres, alta disponibilidad y mantenimiento de base de datos y es un presentador regular en conferencias en todo el mundo. Blogs en SQLskills.com/blogs/paul y él puede encontrarlo en Twitter en Twitter.com/PaulRandal.