SQL q & A: desfragmentaciones y desastres
Los registros de errores y los archivos de las bases de datos temporales rápidamente pueden crecer más allá de lo controlable. Estas tácticas de configuración pueden ser de ayuda.
Paul S. Randal
Valor predeterminado para la desfragmentación
P. He sido abordar algunos problemas de mantenimiento de bases de datos que tenemos en nuestro servidor, y estoy teniendo en cuenta la fragmentación del índice. ¿En lugar de emplear tiempo en averiguar un factor de relleno para cada índice, sería más fácil definir sólo el factor de relleno predeterminado para esa instancia? ¿Está haciendo los inconvenientes?
**R.**Generalmente no recomiendo establecer el valor de fillfactor para toda la instancia de cualquier cosa, el valor predeterminado: 100 por ciento. Cambiar esa configuración puede provocar el espacio desaprovechado en la base de datos.
Al crear índices en una tabla, normalmente sólo una de ellas coincidirá el modelo de inserción de la tabla. Todos los demás índices se fragmentarán inevitablemente. Como el modelo de inserción es de sólo anexar (no inserciones aleatorias de tener una clave aleatoria, como un identificador único global [GUID]), tiene sentido para no tener el mayor índice fragmentado.
El índice más grande es siempre el índice agrupado, como el índice agrupado es la tabla. Contiene todos los registros de datos con todas las columnas de tabla. Tiene sentido para el índice agrupado será la que obtener no fragmentado. Quitar la fragmentación del índice agrupado es más caro (en términos de registro de transacciones, tiempo y espacio de disco) que si se hace para cualquier otro índice.
Como normalmente no consigue fragmentado el índice agrupado, por lo tanto, tiene sentido que el valor de fillfactor se establece en 100. No requerirá ningún espacio adicional. Puede haber otras razones que se obtiene el índice agrupado fragmentado (tales como de las actualizaciones de las columnas de longitud variable que hacen más filas de la tabla), pero que suele ser true.
Si establece el valor de fillfactor toda la instancia en cualquier elemento excepto del 100 por ciento, tendrá que configurar específicamente el llenado de todos los índices agrupados que no obtenga fragmentados. Si no lo hace, utilizará el valor de fillfactor de toda la instancia nueva y dejar espacio cuando se vuelve a generar. Esto esencialmente desperdicia espacio en la base de datos. Decide hacerlo, pero generalmente no ha considerado una práctica recomendada.
Como hay una gran variedad de índices en cualquier base de datos, sería poco habitual buscar un valor de fillfactor único es óptimo para todos los índices. Normalmente resulta mejor desde una perspectiva de la capacidad de administración para dejar el valor de fillfactor toda instancia solo. A continuación, puede establecer específicamente llenado inferior en sólo dichos índices que las necesiten.
Reinicie las respuestas
P. Algunos servidores en nuestro entorno con poca frecuencia se reinicien. Aunque esto es algo bueno en muchos sentidos, significa que el registro de errores de SQL Server puede crecer ineficaz. Parece que llene de decenas de miles de mensajes de finalización de copia de seguridad para el que no tengo ningún uso. ¿Hay algo que puedo hacer para que los registros de errores más pequeños y más fáciles de administrar?
**R.**Hay dos cosas que puede hacer: corte hacia abajo en los mensajes de copia de seguridad y configurar la administración del registro de error. Cada vez que se completa una copia de seguridad, escribirá una entrada en el registro de errores. Esto es de poca utilidad. Finalización de copia de seguridad correcta es la norma, no es un error.
Hay una marca de seguimiento documentado: indicador de traza 3226, que evitará que los mensajes de confirmación de copia de seguridad. Debe agregar a la lista de indicadores de traza de inicio (mediante el Administrador de configuración de SQL Server). Para habilitar esta opción sin tener que detener y reiniciar SQL Server primero, puede activar también ese rastro marcar utilizando el comando "DBCC TRACEON (3226, -1)." Los medios de-1 para el indicador de traza se aplican globalmente. El equipo de SQL Server escrito sobre el indicador de traza en el 2007.
También se puede configurar la administración de log de error dentro de la SQL Server de Management Studio (SSMS). Abra el Explorador de objetos en SSMS y conéctese a SQL Server. Expandir el cuadro de administración, haga clic en los registros de SQL Server y seleccione Configurar. Cuando aparezca el cuadro de diálogo Configurar registros de Error de SQL Server, marque la opción "Limitar el número de archivos de registro de errores antes de reciclarlos". Seleccione 99 registros de errores. Esto reemplaza el número predeterminado de seis registros guardados del error.
El paso de la configuración final es lo que limitará el tamaño de cada registro de errores. Indique a SQL Server para crear un nuevo registro de errores de cada día (llamado "bicicleta" el registro de errores). Para ello, crear una diaria de agente de SQL Server que simplemente hace "EXEC sp_cycle_errorlog." Después de esto, los registros de error deben ser fácil de administrar.
Dominar Tempdb
P. Nuestro volumen de datos ha crecido enormemente durante los últimos dos años. Nuestro tempdb siempre parece rellenar lo que la unidad está en. Ejecutar algunas consultas complejas, por lo que hacemos un lote de uso de tablas temporales. ¿Puede dar cualquier asesoramiento acerca de cómo reducir el uso de tempdb?
R: el uso de Tempdb es un problema perenne para los usuarios de SQL Server. Hay sólo un único tempdb para cada instancia de SQL Server, para que tenga cuidado de cómo se utiliza.
Uno de los usos más comunes de tempdb es de tablas temporales. Estas tablas permiten a los resultados intermedios de complejas agregaciones o uniones para conservar y pasan a formar parte de una consulta más grande. A menudo puede ser una manera eficaz para dividir una consulta larga y compleja, pero no siempre es el caso. A veces los desarrolladores acostumbrarse a usar tablas temporales de forma predeterminada, en lugar de hacer las pruebas de rendimiento para ver o no utilizar una tabla temporal es más eficaces.
El problema con la creación de un resultado intermedio que se establece en una tabla temporal es que puede interrumpir la canalización de datos eficiente a través de una consulta compleja. Fuerza el optimizador de consultas para procesar la creación de la tabla temporal o población por separado de las operaciones siguientes que utilizan la tabla temporal. A veces, el optimizador de consultas produce un plan de consulta más eficaz si no utiliza una tabla temporal. También puede ejecutar más eficientemente con una construcción diferente de consulta como una tabla derivada o una expresión de tabla común.
Si una tabla temporal es la mejor manera de dividir la consulta, hay dos cosas que puede hacer para reducir el uso de tempdb:
- Cree los índices apropiados: Asegúrese de que los índices sólo creados en la tabla temporal son realmente útiles para un posterior procesamiento (verificación esto mediante el análisis y comprobación que indiza el plan de consulta está utilizando). Los índices no agrupados que se crean en cada columna de tabla temporal están improbable que sea útil. También asegúrese de que los índices se crean después de rellenar la tabla temporal, por lo que tienen las estadísticas para ayudar a que el optimizador de consultas utiliza el índice.
- Minimizar el tamaño de la tabla temporal: Asegúrese de que las columnas sólo se conservan en la tabla temporal son aquellos utilizados para su posterior procesamiento, en caso contrario son una pérdida completa de espacio. Tablas temporales se crean a menudo con una instrucción SELECT * construcción con no pensado para las columnas que son realmente necesarios. Si está procesando un amplio conjunto de resultados, este espacio desperdiciado podría aumentar realmente.
Copias de seguridad incorrectas
P. La semana pasada se estrelló nuestro SAN. Sufrimos algunas pérdidas de datos en nuestra base de datos de producción. Las copias de seguridad más recientes se almacenaban en el SAN junto con los archivos de base de datos, por lo que los que estaban dañados. También encontramos nuestras copias de seguridad ligeramente antiguos estaban dañados así: a veces el mismo daño. ¿Cómo podemos evitar esta situación en el futuro?
**R.**Esta es una situación común: no hay buenas copias de seguridad y un punto único de falla, lo que lleva finalmente a la pérdida de datos.
El primer problema es que las copias de seguridad están dañados. Debe implementar una estrategia de pruebas para determinar si la copia de seguridad está dañado o contiene una base de datos dañada de la copia de seguridad. Hay varias partes:
- Implementar una comprobación de coherencia con regularidad para la base de datos de producción. Esto significa ejecutar el comando DBCC CHECKDB en la propia base de datos de producción o una copia de la base de datos.
- Habilitar las sumas de comprobación en la base de datos de producción (si no está ya activada). Utilice la opción WITH CHECKSUM en todas las copias de seguridad. Esto probará las sumas de comprobación de página, como se leen las páginas del archivo de datos para su inclusión en la copia de seguridad, lo que ayuda a evitar la creación de una copia de seguridad con una base de datos dañada.
- Implementar una comprobación de validez de las copias de seguridad después de que se tomaron. Esto implica tomar la copia de seguridad a otra instancia de SQL Server y a restaurar (mediante la opción WITH CHECKSUM nuevo), o al menos, realizar la copia de seguridad mediante la suma de comprobación con RESTORE VERIFYONLY. El objetivo es restaurar la base de datos y ejecutar DBCC CHECKDB. Esto también es una buena forma de quitarse de encima la carga de trabajo de comprobación de coherencia del servidor de producción.
- Implementar una programación regular de prueba donde practicar la restauración de la base de datos de producción de copias de seguridad disponibles.
Otra forma de probar fácilmente la validez de las copias de seguridad del registro de transacciones es crear un trasvase de registros secundario. Esto restaurará constantemente las copias de seguridad del registro de transacciones de producción. También proporciona una copia redundante de la base de datos.
El segundo problema es que las copias de seguridad se almacenan en el mismo subsistema de E/s como la base de datos. Esto significa que no tiene protección contra fallas del subsistema de E/s.
Debe guardar copias de todos los backups en un subsistema de E/s independiente de la base de datos de producción, lo ideal sería una ubicación totalmente independiente. Ayudar las copias locales de las copias de seguridad con recuperación ante desastres más rápida y copias remotas garantizan la recuperación ante desastres siempre es posible si el almacenamiento local sea dañada o destruida.
Otra cosa a tener en cuenta es alentador simulacros de recuperación ante desastres regular. Imagine que un desastre y llevando a cabo el plan de recuperación ante desastres para determinar su efectividad. Mi artículo de abril de 2011, "SQL Server: proteger los datos a toda costa," explica esto desde una perspectiva de administración.
Preparación de recuperación ante desastres es como la seguridad. Una de las directrices que se repite con frecuencia es la "defensa profunda". Cuantas más opciones que tiene para la recuperación y los problemas potenciales más anticipar y evitar de manera proactiva, más probable que podrá recuperarse de un desastre dentro de los acuerdos de nivel de servicio el tiempo de inactividad y pérdida de datos.
.jpg)
**Paul S. Randal**es el director general de SQLskills.com, director regional de Microsoft y MVP de 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 de información de núcleo durante el desarrollo de SQL Server de 2008. Randal es experto en recuperación ante desastres, alta disponibilidad y mantenimiento de bases de datos, y es moderador habitual en conferencias en todo el mundo. Blog en SQLskills.com/blogs/paul y puede encontrarlo en Twitter en Twitter.com/PaulRandal.