SQL Q & A: La reducción, crecimiento y volver a diseñar bases de datos
Las bases de datos SQL se incluyen en todas las formas y tamaños y esquemas. Este mes, le ayuda nuestro experto en SQL con condensación, crecimiento y volver a diseñar las bases de datos.
Paul S. Randal
La base de datos de reducción de increíble
**P:**De vez en cuando nos estamos obliga a que se ejecute reducción en nuestras bases de datos debido a la falta de espacio en disco, aunque hacerlo puede causar problemas de rendimiento se puede saber. Nos ocupamos de cualquier fragmentación de índices más adelante. ¿Puede explicar por qué parece ejecutarse más lentamente de algunas bases de datos de otros usuarios, incluso cuando no son de tamaño similar reducción?
**R:**Me alegro de que está cognizant de los efectos secundarios de la ejecución de una reducción de la base de datos. También me doy cuenta de que a veces es sólo inevitable.
Actividades simultáneas de la base de datos y el esquema de las tablas de la base de datos pueden afectar al tiempo de ejecución de una operación de reducción de la base de datos. Esto significa que dos bases de datos del mismo tamaño, pero con distintos esquemas podrían tardar considerablemente diferentes periodos de tiempo para reducir el tamaño.
Reducir funciona migrando las páginas del archivo de datos para consolidar el espacio libre al final del archivo, que, a continuación, devuelve al sistema de archivos (por lo que reduce el tamaño del archivo de fecha). Para mover una página de un archivo de datos, SQL Server debe adquirir un bloqueo exclusivo en la página. Esto significa que nadie más puede tener ningún bloqueo o los registros en la página. Si no hay actividad simultánea en la base de datos implica la adquisición de bloqueos, reducción no se puede bloquear y, a continuación, tiene que esperar para adquirir el bloqueo que necesita. Esto hará que la reducción a tardar más tiempo en ejecutarse que si no hay ninguna otra actividad en la base de datos.
Otro factor cuando la reducción mueve a una página de un archivo de datos es que, si las otras estructuras de base de datos tienen físicos punteros a datos en la página que se va a mover, debe actualizar los punteros físicos con la nueva ubicación de la página. Esto no supone ningún problema excepto cuando una tabla es un montón (no dispone de ningún índice agrupado) o cuando una tabla tiene uno o más columnas de objetos grandes (LOB) almacenan fuera de fila (independientemente de que el registro de datos de tabla).
Cuando una tabla es un montón, todos los índices no agrupados en el montón contienen punteros físicos a los registros de datos de la tabla. Cuando la reducción mueve a una página de datos de la tabla, los índices no agrupados deben actualizarse. SQL Server para ello, una llamada en el procesador de consultas para realizar el mantenimiento de índices en las filas de índices no agrupados de 100 a la vez.
Cuando una tabla tiene datos de la unidad de negocio de fuera de la fila, seleccione los registros de datos a los datos de la unidad de negocio de fuera de la fila. Sin embargo, hay servicios-puntero no de los datos de la unidad de negocio para los registros de datos. Esto significa que cuando la reducción mueve a una página de texto (que contiene datos de LOB fuera de la fila), todos los registros de datos que apuntan a los datos de la unidad de negocio en la página se deben actualizar. Como hay punteros de seguridad, debe realizar un recorrido de tabla para buscar los registros de datos correcto para la actualización. Como puede imaginar, este proceso puede ser bastante lenta para una tabla con una gran cantidad de datos de la unidad de negocio.
Aunque reducción puede ser lenta, en adelante de SQL Server 2005 ofrece a través de la columna percent_complete de la vista de administración dinámica sys.dm_exec_requests para informar del progreso. También puede supervisar el contador de rendimiento de bytes por segundo de reducir el movimiento de datos en el objeto de rendimiento de bases de datos para ver la velocidad de progreso de reducción.
IT permiten Auto-incrementar
**P:**Soy un administrador de base de datos nueva y he ha leer una gran cantidad de información en pantalla acerca de las prácticas recomendadas para configuración de la base de datos. Estoy confundido por las vistas en conflicto si crecimiento automático debe estar habilitado o no. ¿Puedo simplemente desactivar, sin que se produzca algún problema?
**R:**La respuesta es que siempre debe habilitar crecimiento automático, pero no se basan en él. La general consiste en supervisar transacciones y datos de registro de tamaño/uso de archivos y proactivamente crecer ellos (o a investigar repentino crecimiento inesperado). Habilitar crecimiento automático de los casos de emergencias cuando gana el jugador que es responsable de supervisar el uso/tamaño de archivo no está disponible inmediatamente para administrar los archivos.
Si crecimiento automático no está habilitada para el registro de transacciones de archivos y los rellenos de archivo, después, escriba toda la se evitará la actividad de la base de datos hasta que realice más espacio disponible en el registro de transacciones. Y si crecimiento automático no está habilitada para las operaciones de mantenimiento de la base de datos que se puede producir un error en la reconstrucción de índices, las operaciones de inserción o de archivos de datos.
La parte complicada es averiguar el valor de crecimiento automático. En adelante en SQL Server 2005, el crecimiento automático de predeterminados para los archivos de registro de transacciones es 10 por ciento y 1 MB para los archivos de datos. Sin embargo, un crecimiento automático de en el porcentaje significa que a medida que aumentan en los archivos, por lo tanto, también es el nivel de crecimiento automático. Esto también significa que el tiempo necesario también puede aumentar si se Don tiene habilitada la inicialización de instantánea de archivos. Por lo tanto, ambos tipos de archivo deben tener un crecimiento automático fijo por lo tanto, crecimiento automático es predecir el comportamiento.
Puede ser especialmente problemática para los archivos de registro de transacciones que tengan un muy grande o en porcentaje de crecimiento automático. Inicialización instantánea de archivos no es posible, por lo que cualquier espacio de archivo recién asignado debe inicializarse en cero. Mientras que la inicialización de cero se lleva a cabo, todos escriben actividades para el registro de transacciones están bloqueadas. Por lo que resulta conveniente para equilibrar el transacción de registro de archivo de crecimiento automático, por lo que resulta de gran tamaño pueden continuar la suficiente que las operaciones de durante un tiempo, pero no demasiado grande que el flujo de operaciones se interrumpe durante demasiado tiempo.
Para los archivos de datos, un crecimiento automático de 1 MB es retrasos pequeño, pero es difícil determinar el valor correcto. Esto depende de si desea que el crecimiento automático para una medida provisional de emergencia o para reemplazar la administración de tamaño de archivo de datos manual. También depende de la cantidad de espacio nuevo necesita diariamente para dar cabida a los datos que se va a insertar en la base de datos. El resultado es: Se debe habilitar el crecimiento automático y se establece a una cantidad adecuada, no de porcentaje.
Esquema de almacenamiento
**P:**Estoy rediseñando nuestro esquema de base de datos para que las consultas son más eficaces. Algunas de las tablas tienen una gran cantidad de datos de caracteres y me gustaría para asegurarse de que estoy almacenarla en la forma más eficaz. ¿Existen instrucciones o procedimientos recomendados que se puede compartir?
**R:**La forma en que se decide almacenar los datos de la unidad de negocio puede tener un gran efecto en el rendimiento de la consulta, por lo que es vital que puede elegir la técnica adecuada. Un análisis detallado de todas las opciones que se está fuera del alcance de esta columna, pero aquí tiene algunas instrucciones:
¿En primer lugar, los datos siempre será menos de 8.000 bytes? Si es así, pruebe un tipo de datos que está (n) char o varchar (n), pero no uno de los tipos de datos LOB true como XML, (n)varchar(max), varbinary (max), texto (n) o la imagen a menos que sea absolutamente necesario. Si la causa del tamaño de los datos requiere un tipo de datos LOB es true, no utilice texto (n) o la imagen como datos de estos tipos se han dejado de utilizar en SQL Server 2005. No son tan funcionales como los demás, más recientes LOB tipos de datos.
En segundo lugar, si se requiere un tipo de unidad de negocio es true, tenga en cuenta si se va a almacenar los datos de fila (la misma tabla de registro de datos como las demás columnas de la tabla) o fuera de la fila (almacenados en las páginas del archivo de datos independiente con un vínculo desde el registro de datos de tabla). Si se utiliza con frecuencia los datos de la unidad de negocio, sería mejor almacenarlo en la fila tal como las consultas pueden recuperar con más eficacia. De lo contrario, es mejor que ésta se guarda fuera de la fila. Las consultas ocasionales pagar un costo ligeramente mayor para recuperar los datos de la unidad de negocio, pero los registros de datos será menores, por lo general un mejor rendimiento de las consultas y de almacenamiento de datos más densa. Tenga en cuenta que sólo se pueden almacén LOB en filas de datos hasta 8.000 bytes, o cualquier cantidad dada de las demás columnas en el registro de datos es posible: después de que ha insertado automáticamente fuera de la fila.
En tercer lugar, si una tabla contiene una columna de la unidad de negocio, se impedirán los operaciones de índice en línea para los índices que incluyen la columna de la unidad de negocio. Por definición, esto afecta a de índice agrupado la tabla. Por este motivo, algunas personas almacenan los datos de la unidad de negocio en una tabla totalmente independiente (partición vertical de esta columna de la unidad de negocio) y, a continuación, realizan una operación de combinación entre la tabla principal y la tabla de la unidad de negocio cuando es necesario para los datos de la unidad de negocio mediante una consulta. Esto supone un poco más espacio de almacenamiento a causa de la complejidad de la sintaxis de JOIN, pero permite más opciones de la estrategia de mantenimiento de índices.
Es posible que también se preocupan de ancho fijo en lugar de los tipos de datos de longitud variable y, posiblemente incluso que requieren un acceso rápido de transmisión por secuencias a los datos, en cuyo caso debe tener en cuenta el tipo de datos de FILESTREAM de SQL Server 2008. Para obtener un análisis más exhaustivo de todos los tipos de almacenamiento de datos LOB, ver mi blog, “ de importancia de la selección de la derecha la técnica de almacenamiento de LOB ”.
Los saldos y cheques críticas
**P:**Estoy repasar las prácticas de mantenimiento de la base de datos de nuestra compañía y voy a iniciar la ejecución de comprobaciones DBCC en nuestras bases de datos críticos. ¿Con qué frecuencia debo ejecutar una comprobación en cada base de datos?
**R:**Comprobaciones de coherencia proactiva son una parte vital de cualquier plan de mantenimiento de la base de datos completa, las bases de datos de usuario y del sistema. También es importante utilizar un método de comprobación de la página. Las bases de datos de SQL Server 2005 y versiones posteriores, habilitar las sumas de comprobación de página. Para las bases de datos de SQL Server 2000, utilice la detección de páginas rasgadas.
Por lo que respecta a las comprobaciones de coherencia, resulta difícil dar una respuesta absoluta para la frecuencia con que se ejecuta. Por lo general, se recomienda ejecutarlas tantas veces como sea posible, al menos una vez por semana. La frecuencia óptima de comprobaciones de coherencia para el es un clásico “ depende. ”
Éstos son algunos de los factores a tener en cuenta:
En primer lugar, ¿cuál es la ventana de mantenimiento? Comprobaciones de consistencia consumen una gran cantidad de CPU, memoria y recursos de E/s, por lo que si la ventana Mantenimiento de cuándo de repuesto estos recursos es menor que el tiempo necesario para ejecutar todas las comprobaciones de coherencia, es posible que no pueda comprobar todas las bases de datos a la vez. Es posible que deba realizar las comprobaciones de coherencia a través de una semana completa o incluso descargar las comprobaciones de coherencia del sistema que no sea de producción (al restaurar una copia de seguridad y ejecutar las comprobaciones de coherencia en la base de datos restaurada).
¿En segundo lugar, cómo estable es el subsistema de E/s se almacenan las bases de datos? Si el subsistema de E/s está teniendo problemas, debe ejecutar comprobaciones de coherencia tan a menudo como sea posible obtener la indicación más temprana posible de daños en la. Según mi experiencia, más tiempo que se lleva a desapercibidos daños, el modo más intenso obtiene y más difícil es recuperar la base de datos sin dejar de cumplir el objetivo de punto de recuperación y el objetivo de tiempo de recuperación.
La conclusión es que lo es para usted y su nivel de comodidad. En agosto de 2009, lleva a cabo una encuesta en mi blog y fuera de los 276 encuestados, 37 por ciento semanalmente ejecutar comprobaciones de coherencia y una más de 25 por ciento ejecutarlas cada día. Puede ver los resultados completos de la encuesta, junto con información mucho más en averiguar la frecuencia de comprobación al www.sqlskills.com/BLOGS/PAUL/post/Importance-of-running-regular-consistency-checks.aspx de .
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 experto en recuperación ante desastres, alta disponibilidad y mantenimiento de bases de datos, y es 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.