SQL Q & A: Deshacer el daño
Eche una mirada a la selección del plan de consulta, las copias de seguridad excesivamente lentas y el proceso de reparar una base de datos de replicación.
Paul S. Randal
Plan de la consulta
P. ¿Al SQL Server es elegir un plan de consulta para ejecutar mis consultas, se tiene en cuenta los datos que se encuentra actualmente en la memoria?
**R.**La respuesta simple es que el optimizador de consultas no considera el búfer contenido piscina al elegir un plan de consulta. El optimizador de consultas evalúa diversos planes que estrecha hacia abajo de las opciones posibles. Busca el mejor plan que puede determinar en un plazo razonable. El optimizador de consultas no siempre identifica el mejor plan de absoluto. El optimizador de consultas no puede gastar una cantidad excesiva de tiempo de compilación del plan, pero siempre selecciona un plan "suficientemente bueno".
El optimizador de consultas debe saber acerca de las diversas tablas implicadas en la consulta. Encuentra esta información en los metadatos relacional para las tablas. Los metadatos relacional describen las columnas de la tabla, índices y restricciones, así como estadísticas sobre las distribuciones de valor en las distintas columnas (si se han creado estas estadísticas). Los metadatos de almacenamiento describen cómo la tabla y los índices realmente se almacenan en los archivos de datos. El optimizador de consultas no utiliza esta información para determinar un plan.
SQL Server no mantener un seguimiento de qué porciones de una tabla y sus índices están en la memoria en cualquier momento. El pool de buffer rastrea las páginas del archivo de datos de una base de datos en memoria. Sin embargo, nada en el SQL Server hace cualquier tipo de agregación automatizada. No, por ejemplo, será capaz de determinar que el 50 por ciento de índice dos de tabla X está en memoria, mientras que sólo el 5 por ciento del índice tres de mesa X está en la memoria.
El optimizador de consultas supone que nada está en la memoria, así que es probable que elegir un plan de consulta que implique la menor cantidad de física i/os. Planes con un montón de física i/os son lentos y costosos. Considerar una mesa con dos índices no agrupados que podría responder a una consulta de selección. El primer índice tiene todas las columnas necesarias. El segundo índice tiene todas las columnas necesarias, además de varias columnas adicionales.
El primer índice tendrá los registros de índice más pequeños, así que tendrá más filas de índice por página del archivo de datos. El optimizador elegirá este índice. Acceso a los registros de índice requerido para satisfacer que la consulta será necesario leer menos páginas de archivo de datos en memoria con entrada-salida física, comparado con utilizando el índice de la segundo. El segundo tiene registros más grandes y menos por la página del archivo de datos. Este razonamiento se llama optimización basada en el costo, y es cómo está diseñado el procesador de consultas en el SQL Server .
Sin embargo, ¿qué pasa si una gran parte del segundo, más amplio índice ya está en memoria y ninguno del primer índice es en la memoria? La consulta será necesario I/O físicas a leer el índice solicitado en la memoria. Esto será mucho más lenta que con el índice más amplio ya en la memoria. En ese caso, el plan de consulta es realmente subóptimo. Sin embargo, el optimizador de consultas no tiene manera de saber lo que está en la memoria, y esto es sólo un ejemplo.
Incluso con eso en mente, sin embargo, ¿qué pasa si el optimizador de consultas reconoció lo que era en memoria y generado un plan que utiliza el índice menos eficiente porque estaba ya en la memoria? Ese plan sería óptimo, siempre y cuando esa situación persistió. Si el índice más eficiente fue leído en la memoria para otra consulta, la consulta tendría un plan subóptimo. ¿¿El plan se pierde por lo que podría ser recompilado nuevamente?
Como Ingeniero de software anterior en el equipo SQL Server , sé que la complejidad de la ingeniería de mantener una visión agregada de que tablas e índices están en la memoria para ayudar a invalidación y selección de plan de consulta es extremadamente difícil. Probablemente esto añadiría un funcionamiento indeseable arriba para sólo un beneficio ocasional, y probablemente nunca sucederá.
Si usted está interesado en ver el grupo de búferes, comprobar hacia fuera el sys.dm_os_buffer_descriptors de vista de administración dinámica (DMV) y las diferentes consultas he puesto juntos en el buffers sección de mi blog SQLskills.
Copias de seguridad largos
P. Utilizamos trasvase para proporcionar una base de datos secundaria para elaboración de informes. Cada cierto tiempo, llevamos a cabo en un tema donde aplicar una copia de seguridad de registro a la base de datos secundaria tiene mucho más de lo habitual. ¿Tienes alguna idea de lo que podría causar esto?
**R.**Sí, he visto esta situación varias veces. Si está usando una base de datos secundaria envío registro de informes, esto significa que usas la opción con espera al restaurar las copias de seguridad del registro en la base de datos secundaria. Esto funciona en tres pasos:
- Escriba todos los archivos de registro de la copia de seguridad en el archivo de registro de base de datos.
- Realizar la parte de rehacer de recuperación (para asegurar que todas las operaciones de transacciones confirmadas están presentes en la base de datos).
- Realizar la parte de deshacer de recuperación (para asegurar que todas las operaciones de transacciones no comprometidas no están presentes en la base de datos).
Paso 3 escribe todos los registros generados por las operaciones de deshacer en un archivo especial llamado el archivo de deshacer. Esto significa que la base de datos está en modo de sólo lectura. También es transaccionalmente coherente para que los usuarios pueden tener acceso. La razón de registro se escribe en el archivo de deshacer es por lo que el registro de transacciones de la base de datos no es alterado de alguna manera. Esto le permite restaurar copias de seguridad de registro posteriores.
Cuando el proceso de restauración comienza en la base de datos secundaria, si existe un archivo de deshacer, hay otro paso realizado antes de los tres primeros pasos. Este paso inicial toma todos los registros en el archivo de deshacer y deshace los efectos en ellos. Esto esencialmente pone la base de datos detrás en el estado que tenía en el final del paso 2. Este estado de la base de datos es el mismo, como si se ha restablecido la copia de seguridad del registro anterior usando con NORECOVERY, en lugar de con la espera.
El problema que está experimentando es una copia de seguridad de registro está restaurando contiene una transacción de larga duración que no cometer antes del final de la copia de seguridad del registro. Esto significa que tiene que ser completamente deshecha como parte de la restauración de la copia de seguridad del registro. Durar este resultado en un archivo de deshacer grandes, que en sí mismo puede hacer recuperar un registro de copia de seguridad. Si la copia de seguridad de registro está restaurando también tiene una transacción no comprometida, de larga duración, entonces es la tormenta perfecta. Paso 3 también tomará mucho tiempo.
Esto puede suceder cuando la base de datos principal se encuentra en mantenimiento de índice y una copia de seguridad de registro termina al final de una operación de reconstrucción de índice de larga duración de un índice agrupado grande. La restauración inicial de esa copia de seguridad del registro en la base de datos secundaria lleva mucho más tiempo de lo habitual para completar debido a paso 3 en el proceso de restauración.
La siguiente copia de seguridad del registro en la base de datos principal también termina justo antes de una reconstrucción del índice. Cuando se vuelva en el secundario, el archivo de deshacer todo tiene que ser deshacer otra vez. Entonces ocurre la restauración del registro, y se genera otro archivo grande Deshacer para deshacer la segunda reconstrucción de índice no comprometidos.
Si la base de datos secundaria necesita acceso 24 x 7 para informar, usted debe ser consciente de esta posibilidad. En ese caso, cuidadosamente aumentar las operaciones de mantenimiento de índice en la base de datos principal con copias de seguridad del registro. Esto asegurará que sólo el índice completo, comprometido reconstrucciones están presentes en las copias de seguridad de registro está restauradas en la base de datos secundaria.
Una alternativa es pasar de trasvase a database mirroring. Aquí, registros continuamente se envían de la directora a la base de datos reflejada. Hay medidas adicionales que implica la anulación de registro de operaciones varias veces. Hay una relación inversa de la complejidad, como la desventaja de este enfoque es que informes debían usar copias instantáneas de base de datos.
Repetición para reparar
P. Cada cierto tiempo terminamos con los archivos dañados. Nuestras copias de seguridad también terminan dañado, así que tenemos que ejecutar una operación de reparación. La semana pasada, una de las bases de datos que debía reparar era una base de datos de publicación de replicación. En Microsoft Online de libros, se dice que todos los suscriptores deben reinicializarse después de reparar una base de datos de publicación. ¿Puede usted explicar por qué?
**R.**Si usted está considerando la opción de REPAIR_ALLOW_DATA_LOSS de consistencia de la base de datos (DBCC) CHECKDB (yo sólo diré "reparar" desde aquí) de comprobación, tienes que pensar dos veces si vas a reparar la base de datos es una base de datos de publicación de replicación. Si es posible, utilice sus copias de seguridad en lugar de ejecutar la reparación.
Si usas duplicación, lenguaje de manipulación de datos (DML) desencadena cambios de captura a la base de datos de publicación y las convierte en las operaciones lógicas. Si usas transaccional, análisis de logs de transacciones de base de datos captura los cambios de la base de datos de publicación. Entonces las operaciones registradas, físicas se convierten en operaciones lógicas. En ambos casos, las operaciones lógicas se aplican a las bases de datos de suscripción de replicación.
Ni mecanismo permite la captura de las operaciones de reparación. Las operaciones de reparación siempre son cambios físicos directos a las estructuras de base de datos. Estas son necesarias para corregir las inconsistencias en las estructuras, como una página de la base de datos, el registro de un tabla o un vínculo entre dos páginas. Son diferentes de los cambios físicos de la base de datos debido a consultas que realizar inserciones, actualizaciones o eliminaciones en tablas.
Estas operaciones no se traducen en operaciones lógicas puede aplicado a los suscriptores de la replicación de reparación. No hay ninguna operación lógica que puede expresarse utilizando Transact-SQL para el equivalente de los cambios estructurales directos que se realiza la reparación. Imaginar que una operación de reparación se ve obligada a eliminar una página de datos de un índice agrupado único (esencialmente eliminar algunos registros de la tabla). La suscripción no se reinicializa. Los registros aún existiría en la copia replicada de la tabla.
Si una operación de inserción posterior inserta registros con valores de la clave de clúster correspondientes a los registros eliminados por la operación de reparación, el agente de distribución fallaría al intentar aplicar los insertos en la copia replicada. La operación de reparación no habría aplicada a la base de datos de suscripción, por lo que producirá un error de infracción de clave duplicada cuando se intenta aplicar el inserto a la tabla duplicada. Esto significa que si una operación de reparación cambia cualquier tabla que forma parte de una publicación de replicación, la suscripción de replicación ya no es válida. Tendrá que reinicializarse.
Si la operación de reparación afecta a cualquiera de las tablas de metadatos de replicación, la publicación de replicación completa está en un estado incoherente. Replicación debe ser completamente eliminada y reconfigurada. Obviamente, esto es un proceso mucho más invasivo que sólo reinicializar una suscripción. La conclusión es que, si es posible, quiere evitar tener que reparar una base de datos de publicación de replicación.
.jpg)
Paul S. Randal es el director general de SQLskills.com, un director regional de Microsoft y un SQL Server MVP. Trabajó en el equipo de motor de almacenamiento SQL Server de Microsoft desde 1999 a 2007. Escribió DBCC CHECKDB/reparación para SQL Server 2005 y fue responsable por el motor de almacenamiento de la base 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. Blog en SQLskills.com/blogs/paul y te lo puedes encontrar en Twitter en twitter.com/PaulRandal.