SQL Q & A: Mantenimiento de registros e índices
Algunas formas clave de mantener SQL Server funcionando de manera eficiente son preservar las copias de seguridad de registros y mantener adecuadamente los índices.
Paul S. Randal
No dividir la cadena
P: He sido definir una estrategia de copia de seguridad para nuestras bases de datos. Mi plan consiste en las copias de seguridad del registro de transacciones para que se puede realizar la recuperación ante desastres con poca pérdida de datos. He ha investigando algunos de los problemas que podría encontrar y he leído varias veces que deben tener cuidado de no romper la cadena de copia de seguridad del registro. ¿Puede explicar qué es y cómo se puede convertir roto?
A: Es una buena pregunta y es algo que pase por alto de muchas personas. La cadena de copia de seguridad del registro (a veces simplemente llamada a la cadena del registro) hace referencia a una serie continua de las copias de seguridad de registro de transacciones que abarcan el tiempo entre la copia de seguridad de datos más reciente (completa o diferencial) y el punto en el que desea restaurar. Una secuencia de restauración de ejemplo sería el siguiente:
- La copia de seguridad completa más reciente
- A continuación, en la copia diferencial de base de datos más reciente
- A continuación, todas las transacciones registro copias después de
Mayoría de los usuarios conservar copias de seguridad de registro alrededor de más de la transacción en caso de una de las copias de seguridad resulta dañada y tiene que restaurar una copia de seguridad reciente de menos datos. Puede obtener más información acerca de las copias de seguridad y restauraciones en los dos artículos de de TechNet Magazine de escribí el año pasado, “ de copias de seguridad de descripción SQL Server” y “ recuperación de desastres, copias de seguridad mediante . ”
Si cualquiera de las copias de seguridad del registro necesarios están dañado o no está disponible para la secuencia de restauración que ha elegido, se romperá la cadena de copia de seguridad del registro y no se puede restaurar pasado que elija. Si sólo una de las copias de seguridad del registro está dañada, es posible que pueda obligar a que restore con la opción WITH CONTINUE_AFTER_ERROR. Que podría forzar una restauración de las entradas del registro de transacciones está dañado, que podría dar lugar a daños en la base de datos. Estaré muy dudas acerca de si acerca de cómo forzar a este tipo de restauración.
Una operación que puede dar lugar a una copia de seguridad del registro necesaria no esté disponible es una copia de registro “ fuera de banda ”, lleva a cabo sin garantizar que se guarda una copia de seguridad del registro. Puede hacer esto para proporcionar una copia a un desarrollador, por ejemplo. Esta copia de seguridad del registro es parte de la cadena de copia de seguridad del registro, ya que es el único que se va a contener los registros generados desde la copia de seguridad anterior.
Es decir, a menos que utilice la opción WITH COPY_ONLY, que realiza la copia de seguridad, sino que también permite la copia de seguridad de registro de siguiente de eficazmente copias de seguridad el mismo conjunto de registros. Ver mi blog, “ BACKUP WITH COPY_ONLY, ” para obtener información detallada acerca de cómo evitar la interrupción de la cadena de copia de seguridad .
Un ejemplo común de más de una operación de división de la cadena de copia de seguridad del registro es uno que le impide realizar una copia de seguridad del registro de transacciones durante el funcionamiento normal. Estos tipos de operaciones se incluyen:
- Cambiar a la recuperación SIMPLE modelo y, a continuación, a FULL o BULK_LOGGED
- Volcar el registro de SQL Server 2005 y versiones anteriores mediante el … BACKUP LOG WITH NO_LOG o TRUNCATE_ONLY opciones
- Restaurar una base de datos desde una instantánea de base de datos
Es necesario realizar una copia de seguridad de datos (completa o diferencial) después de cualquiera de estas operaciones para permitir que las copias de seguridad del registro continuar. Esto se denomina reinicio de la cadena de copia de seguridad del registro.
Una cosa más que: Al contrario que mito popular, realizar una copia de seguridad completa o diferencial es no romperá la cadena de copia de seguridad del registro y, en realidad, no tiene ningún efecto en el registro de las copias de seguridad de ningún tipo.
Los índices de clúster
P: Muchas de las tablas en nuestra base de datos de SQL Server 2008 Don tiene un índice agrupado. He oído que podría tener problemas de rendimiento de E/s adicionales de la causa de registros reenviados. ¿Puede decirme cómo puedo comprobar esto, y qué puedo hacer al respecto?
A: Un montón es una tabla que no tiene un índice agrupado. Es inherentemente sin ordenar. Para aquellos lectores que Don saber acerca de registros reenviados en los montones y cómo se usan, vea la entrada de blog, “ reenvío y registros reenviados y el tamaño de la parte posterior de puntero , ” para obtener más detalles. Registros reenviados en montones pueden producir operaciones de E/S aleatorias adicionales durante el proceso de consulta, lo que a su vez conduce a un bajo rendimiento.
Para comprobar si tiene consultas que se va a procesar reenviadas los registros de la forma más fácil es mirar el contador de rendimiento de registros reenviados por segundo en el objeto de rendimiento de los métodos de acceso. A continuación, utilice la función de administración dinámica sys.dm_db_index_physical_stats con el modo detallado, con algunas de las tablas de la base de datos y devolverá el número de registros reenviados por cada tabla en la columna forwarded_record_count del resultado. Consulte en este tema en los libros en pantalla para obtener más detalles.
La peor para quitar registros reenviados consiste en crear un índice agrupado y, a continuación, colóquelo de nuevo. Esto hace que todos los no agrupados en la tabla se regeneran automáticamente dos veces, lo que es una enorme pérdida de recursos. Ver mi blog para obtener más información: “Lo que ocurre con los índices no agrupados cuando se cambia la estructura de tabla?”
La forma más fácil de eliminar permanentemente y evitar que los registros reenviados en montones es crear los índices agrupados. No se desea obtener en “ frente a un índice agrupado. montón ” debate aquí sobre por qué se deben dispone de índices agrupados en la mayoría de los casos, en lugar de montones. Ver blog del mi esposa Kimberly Tripp de “ de la clave de la organización por clústeres ” serie en esto para obtener más detalles. Le animo a evaluar el uso de los índices agrupados.
Cuando los registros de una tabla aumentan el tamaño, puede causar registros reenviados cuando no hay espacio suficiente. Otra forma de evitar que se reenvían los registros, es por lo tanto impedir que los registros de cambio de tamaño. Por ejemplo, esto podría significar, utilizando valores predeterminados para las columnas de longitud variable.
En SQL Server 2008, hay una nueva instrucción REBUILD de … de ALTER TABLE que le permite volver a generar los montones. Esto funciona del mismo modo que la instrucción REBUILD de ALTER INDEX … le permite volver a generar los índices. Microsoft agregó esta instrucción para la característica de compresión de datos, pero que funciona para nuestros fines. Consulte en este tema en los libros en pantalla para obtener más detalles.
Mantenimiento del índice
P: He cambiado nuestras rutinas de mantenimiento del índice para utilizar vuelve a generar el índice en línea, pero sigue viendo los problemas de bloqueo a veces, cuando ejecutan las rutinas de mantenimiento. ¿Por qué es esto? Pensé que las operaciones de índice en línea Don utilizar bloqueos, por lo que no veo ningún bloqueo. ¿Es este comportamiento esperado o me haciendo algo mal?
A: Está viendo el comportamiento esperado. Hay un bloqueo de tabla compartidos necesario al comienzo de la operación, mientras que la operación inicializa (un proceso muy rápido). Esto se elimina inmediatamente. Este bloqueo se debe poner en cola como cualquier otro bloqueo, y se impide que las consultas nuevas realizar modificaciones en la tabla hasta que se pueden conceder y liberar el bloqueo de nuevo.
Este bloqueo no puede adquirir hasta que haya realizado todas las consultas de modificación que se está ejecutando. Esta operación puede tardar bastante tiempo, dependiendo de la carga de trabajo. Esto significa que el bloqueo puede producirse al principio de una operación de índices en línea.
Al final de la operación, tendrá que tomar un bloqueo de modificación del esquema, piense en esto como un bloqueo exclusivo, para permitir que se complete. Esto también sucede muy rápidamente. A continuación, coloque inmediatamente. Este bloqueo impedirá que cualquier tipo de consultas nuevas en la tabla (lectura o escritura) hasta que concesión y liberación del bloqueo.
Una vez más, no se puede adquirir este bloqueo hasta que SQL ha finalizado todas ejecución lectura, o escribir consultas. Nuevamente, esto significa que es la posibilidad de bloqueo.
En resumen, aunque el nombre de la función de las operaciones de índice en línea, es necesario seguir dos bloqueos a corto plazo que pueden causar problemas de bloqueo. La ganancia a través de las operaciones de índices sin conexión tradicional no es que para la mayoría de la operación de índice, no bloqueos, y por lo tanto, en general se aumenta la simultaneidad. Las notas del producto “ de operaciones de indización en pantalla de SQL Server 2005 ” tiene mucha más información acerca de cómo funcionan estas operaciones.
Reducir el tiempo de mantenimiento de índices
P: He heredado algunos sistemas en los trabajos de mantenimiento de índices regulares tardan mucho tiempo en ejecutarse y generar gran cantidad de E/S, pero no realizo las reconstrucciones índice porque los índices no están obteniendo fragmentados. Me gustaría reducir el trabajo realizado, ya no aparece ningún aumento del rendimiento. ¿Puede recomendar una estrategia para ayudar a?
A: Se trata de un problema bastante frecuente. Deriva de la forma en que los trabajos de mantenimiento de índices determinan qué índices para volver a generar o reorganizar.
Muchas personas que se ejecuta la función de administración dinámica sys.dm_db_index_physical_stats (se ha mencionado anteriormente) en todos los índices de la base de datos, a continuación, elija si desea volver a generar, reorganizar o no hacer nada. Esta decisión base en el avg_fragmentation_in_percent, la page_count y los valores de avg_page_space_used_in_percent utilizando una cláusula WHERE en la salida.
El problema es que la fragmentación de índices no se almacena en memoria, al igual que otras estadísticas. Esta función debe leer y procesar cada índice para determinar el alcance de la fragmentación. Si la mayoría de los índices de la base de datos es estática o cambia muy despacio (en términos de fragmentación), a continuación, que no se vuelve a generar o reorganizar. Comprobando su fragmentación cada vez que ejecute un trabajo de mantenimiento de índices es esencialmente una pérdida de tiempo.
Las vistas de administración más dinámicas admiten “ predicado inserción abajo, ” donde los únicos datos que se procesa están la que coincide con el predicado de la cláusula WHERE. Sin embargo, sys.dm_db_index_physical_stats es una función, no es una vista, por lo que no puede hacer esto. Esto se que debe filtrar de forma manual y se lo pide a la función para procesar los índices que se sabe que tienen el potencial de fragmentación y puede ser necesario volver a generar o reorganizar.
Se recomienda la supervisión de la fragmentación a lo largo de varias semanas. De este modo obtendrá una idea de que los índices son merece la pena comprobar también la fragmentación, en lugar de comprobar todo el contenido. Una vez que la lista de los índices, crear una tabla con el nombre de tabla, el umbral de nombre y la fragmentación de índice para realizar ninguna acción. Es posible que algunos índices pueden tener más fragmentación antes de que afectan al rendimiento de otros usuarios. Éste será el “ tabla controlador ”, a continuación, se utiliza para dirigir el trabajo de mantenimiento de índices. Debe recorrer en iteración todos los índices que se describe en la tabla y sólo se ejecuta la función de sys.dm_db_index_physical_stats en ellos.
He implementado esto para varios clientes. En algunos casos, ha reducido el tiempo de ejecución de la tarea de mantenimiento de índices de horas a 15 minutos o menos. Que es simplemente a partir de no ejecutar esta función en los índices estáticos. También se puede ir más allá y realizar un seguimiento de con qué frecuencia se vuelve a generar un índice y potencialmente, cambiar FILLFACTOR del índice automáticamente, lo que es de esperar una nueva reducción en el trabajo realizado por el trabajo de mantenimiento de índices.
Para obtener más información acerca de los diversos métodos de realizar el mantenimiento de índices, consulte la entrada de blog, “ de importancia de mantenimiento de índicesde ” y obtener una explicación detallada de lo que ocurre en el interior de la función, vea también mi blog, registrar, “ Inside sys.dm_db_index_physical_stats. ”
Gracias a l de Kimberly. Tripp de SQLskills.com para su revisión técnica de mes este.
.jpg)
Paul S. Randal es el director de administración 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 de Microsoft de 1999 a 2007. Escribió DBCC CHECKDB/reparación para SQL Server 2005 y fue responsable del motor de almacenamiento del núcleo durante el desarrollo de SQL Server 2008. Randal es un experto en recuperación ante desastres, alta disponibilidad y mantenimiento de la base de datos y un moderador habitual en conferencias en todo el mundo. Le que blogs en SQLskills.com/blogs/paul y puede encontrar en Twitter en Twitter.com/PaulRandal.