PREGUNTAS Y RESPUESTAS DE SQL Área de desbordamiento de fila, las copias de seguridad diferenciales y más

Paul S. Randal

P recientemente actualizar una aplicación se ejecute en SQL Server 2005. Una de las cosas que ha aprovechado es la capacidad para que las filas superiores a 8.060 bytes por lo que pueden permitir a los usuarios crear más campos de datos sin apareciendo un mensaje de error de SQL Server. Ahora que esta aplicación está en el entorno de producción, tiene problemas de rendimiento de algunas consultas de búsqueda que utiliza para ejecutar bien antes el cambio de esquema. Ha comprueban la fragmentación de los índices varios y todo lo que es CORRECTA. ¿Por qué se las consultas ejecutan lentamente en SQL Server 2005?

A La característica estás utilizando, fila de desbordamiento, es fantástico para permitir que la fila ocasional a ser más largo que 8.060 bytes, pero no resulta idóneo para la mayoría de las filas que se va a oversized y puede provocar una caída en el rendimiento de las consultas, tal como está experimentando.

La razón para esto es que cuando una fila se va a convertirse en sobredimensionados, una de las columnas de longitud variable en la fila se inserta "desactiva fila". Esto significa que la columna se toman de la fila en la página de datos o índice y mueve a una página de texto. En lugar del antiguo valor de columna, se sustituye un puntero que señala la ubicación nueva del valor de columna del archivo de datos.

Éste es exactamente el mismo mecanismo utilizado para almacenar regulares columnas de unidad de NEGOCIO (objeto grande), como el XML, texto, imagen o varchar (max). Observe que si el esquema de la tabla contiene varias columnas de longitud variable, no hay ninguna garantía de que la misma columna se empujará desactiva la fila cuando varias filas se convierten en extra.

Este mecanismo puede crear un problema de rendimiento. De repente una consulta para recuperar una columna de longitud variable de una sola fila de una tabla que necesite una E/s adicional si se ha insertado la columna desactiva fila (para leer en la página de texto que contiene la ubicación fuera de la fila del valor). Si se oversized varias filas, una consulta para recuperar varias filas en la misma columna de longitud variable puede tener rendimiento impredecible, dependiendo de cuántos de los valores han ha insertado desactiva la fila.

En su caso, una consulta de realizar un recorrido de intervalo o la exploración de tabla para una lista de selección que incluye una columna de longitud variable es sufrir de rendimiento deficiente debido a la fila de desbordamiento y sus efectos. No importa si los índices están fragmentados perfectamente, cuando una columna de longitud variable se ha insertado desactiva la fila, esencialmente se interrumpe el análisis anteriormente eficaz puesto que es necesario leer la página de texto que contiene el valor desactiva la fila de una E/s aleatorias.

Desbordamiento fila todavía es muy útil para las filas sobredimensionadas ocasionales. Sin embargo, si el rendimiento de las consultas es importante, debe no ser un componente muy explotado en el diseño.

P ha sólo introducido la creación de reflejos de bases de datos entre dos grupos de conmutación por error como una manera de obtener geográfica de redundancia de menor que el costo de replicación de red (SAN, Storage Area Network) del área de almacenamiento. Los centros de datos son de la misma ciudad, por lo que estamos podrán utilizar la creación de reflejos sincrónicos. El problema es que cuando se produce una conmutación por error en el clúster local, la base de datos reflejada conmuta al clúster remoto, que no es el comportamiento que deseamos. ¿Cómo puede se evitar esto ocurra? Deseamos que la conmutación por error que suceda sólo si el clúster local no está disponible.

A para una mayor disponibilidad, creación de reflejos está configurado con un servidor testigo modo que conmutaciones por error se realicen automáticamente si el principal no está disponible. La idea es que si todo el clúster local deja de funcionar, la creación de reflejos de bases de datos se conmutación por error al clúster de la segundo y la aplicación pueda continuar.

El problema se produce cuando se produce una conmutación por error del clúster. La conmutación por error tarda más que se produzca que el valor de tiempo de espera predeterminado de la creación de reflejos de bases de datos. El servidor testigo y el servidor reflejado (lo que significa que la instancia activa de SQL Server en el clúster de segundo) coinciden que no se ven el principal y, a continuación, el servidor reflejado inicia una creación de reflejos de conmutación por error al clúster de la segundo.

El método más sencillo para evitar esto es quitar el servidor testigo para que la creación de reflejos de bases de datos no no automáticamente conmutación por error si el clúster local deja de funcionar. Por supuesto, esto reduce disponibilidad, mientras una persona, a continuación, se necesita para iniciar una conmutación por error.

La segunda opción es modificar el valor de tiempo de espera de predeterminado de la creación de reflejos de bases de datos. Esto es el número de vez por segundo "pings" que la entidad principal debe fallan para responder a antes de que está declarado como no disponible. Esta configuración se denomina el tiempo de espera asociado y tiene un valor predeterminado de 10. Puede encontrar el valor de tiempo de espera actual de la base de datos utilizando el código siguiente:

SELECT [mirroring_connection_timeout]
  FROM master.sys.database_mirroring 
  WHERE [database_id] = DB_ID ('mydbname');
GO

El valor de tiempo de espera puede cambiarse mediante el siguiente código:

ALTER DATABASE mydbname 
  SET PARTNER TIMEOUT <timeoutvalue>;
GO

Para este escenario, el tiempo de espera asociado debe ser superior a la hora habitual tarda del clúster de conmutación por error que se produzca en el clúster local establecido. Esto puede ser un poco complicado determinar dado la variabilidad en el tiempo necesario para ejecutar recuperación en la base de datos reflejada cuando se produce la conmutación por error del clúster, pero debe podrá determinar un límite superior. El problema con este método es que el valor de tiempo de espera es posible que tienen que ser minutos, que pueden ser inaceptables para cuando se produce un desastre real.

P la estrategia de copia de seguridad implica la completa y copias de seguridad del registro, pero se ha oído que debe agregar tiempo de restauración de copias de seguridad diferenciales para disminuir. Realizar una copia de seguridad completa una vez que las copias de seguridad de registro de una semana y por horas. Intentó agregar copias de seguridad diferenciales diarias, pero una cosa impar que haya advertido es que las copias de seguridad diferenciales al final de la semana son cerca con el mismo tamaño como la copia de seguridad semanal completa. Estaba en la impresión de que son incrementales, igual que las copias de seguridad del registro. ¿Estoy falta algo?

A la misunderstanding aquí es alrededor de la naturaleza de las copias de seguridad diferenciales. A diferencia de las copias de seguridad del registro, las copias de seguridad diferenciales no son incrementales. Una copia de seguridad diferencial contiene todas las extensiones de archivo de datos que han cambiado desde la copia de seguridad completa anterior (y esto se aplica a bases de datos, grupo de archivos y copias de seguridad de nivel de archivo).

Cuando se cambia una extensión (un grupo lógico de ocho contiguas páginas de datos archivo) de cualquier forma, se marca en una página de mapa de bits especial denomina el mapa diferencial (o más conocido como el mapa de diferencias). Hay un mapa de diferencia para cada campo de 4 GB de cada archivo de datos. Cuando se realiza una copia de seguridad diferencial, el subsistema de copia de seguridad examina todas las asignaciones de diferencias y copia todas las extensiones modificadas, pero no se restablecen los mapas de diferencias. Esto significa que si varias extensiones se modifican entre copias de seguridad diferenciales sucesivas, será mayores las copias de seguridad posteriores. Sólo se restablecen los mapas de diferencia cuando se realiza una copia de seguridad completa.

Si la carga de trabajo de la aplicación es tal que el contenido de la base de datos se cambia mucho en un breve período de tiempo (por ejemplo, dentro de una semana), a continuación, una copia de seguridad semanal completa será casi el mismo tamaño que una copia de seguridad diferencial que se realizó inmediatamente antes de completo siguiente copia de seguridad. Esto explica el comportamiento que está observando.

Es correctos en pensar que las copias de seguridad diferenciales ofrecen un medio para reducir el tiempo de restauración en una situación de recuperación de desastres. Si la estrategia de copia de seguridad es realizar copias de seguridad semanales completas y copias de seguridad del registro por horas, una restauración totalmente actualizada requeriría las siguientes:

• Realizar una copia de seguridad registro de la cola (todos los registros generados desde la copia de seguridad del registro más reciente).
• Restaurar la copia de seguridad completa de la base de datos más reciente.
• Restaurar todos los copias de seguridad del registro, en secuencia, desde la última copia de seguridad completa de la base de datos.
• Restaurar la copia de seguridad registro de la cola.

Esto podría requerir una gran cantidad de copias de seguridad del registro que se va a restaurar, especialmente si el desastre se produce justo antes de la siguiente copia de seguridad completa vencimiento. (Un escenario más desfavorable significaría 24 + 24 + 24 + 24 + 24 + 24 + copias de seguridad del registro 23 que se va a restaurar!) Al agregar las copias de seguridad diferenciales diarias a esta estrategia, la secuencia de restauración cambia a esta:

• Realizar una copia de seguridad registro de la cola (todos los registros generados desde la copia de seguridad del registro más reciente).
• Restaurar la copia de seguridad completa de la base de datos más reciente.
• Restaurar la copia de seguridad diferencial más reciente.
• Restaurar todos los copias de seguridad del registro, en secuencia, desde la copia de seguridad diferencial más reciente.
• Restaurar la copia de seguridad registro de la cola.

Esto puede quitar la necesidad de restaurar mucho de copias de seguridad del registro, como restaurar una copia de seguridad diferencial es esencialmente el mismo que restaurar todas las copias de seguridad del registro en el período cubierto por la copia de seguridad diferencial.

El muy peor caso en un escenario donde se realizó una copia diferencial diaria sería 23 copias de seguridad del registro, incluso en el último día de la semana. La desventaja de una de copias de seguridad diferenciales no se incremental es que puede tomar más espacio, pero eso casi siempre un equilibrio merece la pena para reducir el tiempo de restauración.

P dispone de un clúster de conmutación por error de dos nodos. Cada nodo ejecuta una sola instancia de SQL Server 2005. Me sigue los consejos comunes de configurar cada instancia sólo se utilizan al 50 % de la memoria disponible. Ahora tengo problemas ya que la carga de trabajo en ambas instancias necesita más memoria para mantener los mismos niveles de rendimiento. AL quitar la limitación de memoria o que sea mayor, CREO que le ejecutar en problemas cuando uno de los casos se produce un error a través de y se están ejecutando en un solo nodo. ¿Qué recomienda?

A Le responder esta pregunta para el caso de dos nodos, dos de instancia, pero todo lo siguiente también se aplica a otras configuraciones de varias instancias (los clústeres de conmutación por error N-1, donde hay N nodos y las instancias de N-1 SQL Server).

Muchas personas experimentan una carga alta (consumiendo más de 50 % de memoria del servidor) en ambas instancias y no tienen en cuenta el efecto en las cargas de trabajo cuando ambas instancias terminan de ejecutarse en un único nodo después de que se produzca una conmutación por error. Sin ninguna configuración especial, es posible para la distribución de memoria entre las instancias para convertirse en desproporcionada, por lo que se ejecuta bien una carga de trabajo y el otro se ralentiza para un rastreo.

Con SQL Server 2000, la recomendación es cada instancia a un máximo de 50 % de memoria de nodo de clúster de límite. Esto se debe el administrador de memoria de SQL Server 2000 no responde a la presión de memoria, si SQL Server tarda, por ejemplo, 80 % de memoria del nodo, no proporcionará lo nuevo. En una situación de conmutación por error, esto significa otra instancia simplemente iniciando sólo podría tener un 20 % de la memoria disponible. Al limitar ambas instancias a un máximo de 50 % de memoria de un nodo, una instancia de la conmutación por error se garantiza que al 50 % de la memoria. El problema con esto es que la carga de trabajo en cada instancia también es limitado a 50 % de la memoria.

Con SQL Server 2005 (y SQL Server 2008), el administrador de memoria puede responder a la presión de memoria por lo que ya no es adecuado el máximo del 50 %. Pero sin algún tipo de limitación, si dos instancias se ejecutan en un nodo de clúster, es posible que presión entre sí hasta que se alcanza una distribución desproporcionada de memoria.

La respuesta es establecer cada instancia que una cantidad mínima de memoria, por lo que no se se para liberar demasiada memoria. Un valor común para una instalación de dos nodos, dos de instancia es que cada instancia configurada para un mínimo de 40 % de la memoria. Esto significa que cuando se ejecuta cada instancia en un nodo independiente, pueden consumir toda la memoria que desean. Cuando se produce una conmutación por error, cada instancia se garantiza que una determinada cantidad de memoria para mantener un nivel establecido de rendimiento de carga de trabajo, con un poco de izquierda a través de que se va a compartir entre ellos. Aunque esto significa que el rendimiento de ambos cargas de trabajo puede colocar en una situación de conmutación por error (como esperado), no se limita en absoluto para la gran mayoría del tiempo cuando se ejecuta cada instancia en un nodo de clúster independiente.

S. Paul Randal es el director de administración de SQLskills.comy un MVP de SQL Server. Trabajado en el equipo de motor de almacenamiento de SQL Server en Microsoft desde 1999 a 2007. Paul escribió DBCC CHECKDB y reparar para SQL Server 2005 y era responsable del motor de almacenamiento principal durante el desarrollo de SQL Server 2008. Paul es un experto en recuperación ante desastres, una alta disponibilidad y mantenimiento de la base de datos y un moderador habitual en conferencias de todo el mundo. Blogs en SQLskills.com/blogs/paul.