SQL Q & A: Velocidad y cuestión de tamaño
La velocidad relativa de consultas de SQL Server y el tamaño de los archivos de datos y registros de transacciones parecen ser factores de rendimiento primario.
Paul S. Randal
Plan de la consulta
P. Recientemente tuvimos un problema con una consulta que estaba tomando un tiempo para que se ejecute. La consulta corrió más rápido después de que hemos reconstruido el índice agrupado en la tabla que se trata. No podía ver cualquier análisis en el plan de consulta, así que ¿por qué la fragmentación han afectado el tiempo de ejecución de la consulta?
**R.**En este caso, parece fragmentación del índice no tenía nada que ver con el tiempo de ejecución de la consulta. Un plan de consulta no óptima fue probablemente la fuente del problema.
Al hacer una reconstrucción de índice, automáticamente se reconstruyen todas las estadísticas de la columna de ese índice. Es el equivalente de un análisis completo. Todos los valores de la columna se consideran al crear las estadísticas, por lo que crea una representación completa de la distribución de valor común.
Todos los planes de consulta creados usando esas estadísticas son esencialmente invalidados y va ser recompilados. Volver a compilar un plan significa la próxima vez que ejecute la consulta, el optimizador de consultas pasará por el proceso de elección de una nueva forma más óptima de producir resultados de la consulta. Este es el plan de consulta.
En este caso, su reconstrucción de índice activa una consulta plan recompilar. El nuevo plan fue mejor que el anterior. Hay varias posibles razones para esto. El mal rendimiento plan de consulta podría haber sido altamente óptimo y compilan de consultas rápidas permitidas cuando fue por primera vez. Como la distribución de los valores de datos de la tabla cambiada con el tiempo, el plan de consulta podría han vuelto menos óptimo.
El viejo plan de consulta podría han estado usando un índice no agrupado basado en el hecho de que una columna en particular (parte del índice no agrupado) fue altamente selectiva. Por lo tanto, tenía sentido utilizar el índice no agrupado para encontrar los valores de datos y, a continuación, más columnas de la tabla. Ha pedido una operación de búsqueda clave.
Si la distribución de datos cambia drásticamente tal que ya no era altamente selectiva de la columna, esto podría haber causado un gran número de costosas búsquedas claves. Teniendo en cuenta la nueva distribución de datos, un plan mejor hubiera sido usar un índice no agrupado diferente.
Cuando el índice agrupado fue reconstruido, se actualizaron las estadísticas. Esto causó un recompile de plan, que eligió el índice no agrupado más selectivo. Esto a su vez produjo un plan más eficiente.
Mientras yo estoy teorizar sobre la causa de la aceleración de la consulta, puedes ver lo que quiero decir sobre la reconstrucción de índice simplemente ser el detonante de la recompilación del plan. Podría no haber directamente fija la causa raíz del problema de rendimiento en primer lugar.
Más archivos, más espacio
P. Tengo un grupo de archivos con dos archivos y ambos son muy completo. Quiero añadir algo más de espacio para el grupo de archivos, así que voy a agregar más archivos de dos y luego tener SQL Server reequilibrar los datos a través de todos los cuatro archivos. ¿Es eso posible?
**R.**Lamentablemente, no es una buena manera de reequilibrar los datos a través de archivos en un grupo de archivos después de agregar nuevos archivos para el espacio adicional. He blogged en el pasado acerca de cómo tener más de un archivo por grupo de archivos puede conducir a un aumento del rendimiento de algunas cargas de trabajo. Es bien sabido que este es el caso.
Sin embargo, es una gran generalización. ¿Cuánta ganancia logrará depende el subsistema de E/S, el diseño del archivo de datos y la carga de trabajo. Habrá un punto en que el número de archivos de datos se convierte en demasiados y es realmente un detrimento del rendimiento. Echa un vistazo a estas entradas de blog en varios archivos de datos de benchmarking y múltiples archivos de datos en unidades de estado sólido (SSD).
SQL Server simplemente no tiene un mecanismo equilibrante de datos en un grupo de archivos. El archivo de datos donde vendrá la siguiente asignación es determinado por asignación de turnos y llenado proporcional. Round robin es donde las asignaciones ocurren de cada archivo de datos a su vez. Habrá una asignación del archivo uno y, a continuación, una asignación de archivo dos, luego volver a archivo uno nuevo. Sin embargo, las asignaciones se realizan de manera proporcional. Más asignaciones se realizan desde los archivos de datos que tienen proporcionalmente más espacio libre que otros archivos de datos en el grupo de archivos.
La premisa básica de llenado proporcional es que cada archivo tiene una ponderación, donde archivos con menos espacio libre tendrá una ponderación más alta. Archivos con un montón de espacio libre tendrán una ponderación inferior. Los archivos con ponderaciones inferiores se asignarán de con más frecuencia, lo que significa que los archivos con más espacio libre se asignarán de la mayoría.
Todo esto significa que al agregar nuevos archivos a un grupo de archivos completo, las asignaciones posteriores vendrá principalmente de los nuevos archivos. Tienen mucho menores ponderaciones de llenado proporcional que los archivos antiguos que tienen inherentemente más datos. Los nuevos archivos se convierten en puntos calientes de asignación, lo que potencialmente menor rendimiento general con algunas cargas de trabajo.
No se puede eludir el algoritmo de relleno proporcional. Tampoco puede usted cambiar las ponderaciones. Incluso intentar algo como reconstruir los índices en el grupo de archivos no funciona, como las asignaciones para los nuevos índices provendrán de los nuevos archivos de datos.
Si desea agregar más archivos a un grupo de archivos, la mejor manera es crear un nuevo grupo de archivos con más archivos. Luego mover los datos de tabla y de índice para el nuevo grupo de archivos utilizando el índice de crear... CON (DROP_EXISTING = ON) comando, especificando el nuevo grupo de archivos como la ubicación de destino. Después de que te has mudado todos los datos, se puede eliminar el grupo de archivos antiguo, vacía. Incluso puede mover datos de línea de negocio para el nuevo grupo de archivos, utilizando algún truco de Kimberly Tripp.
Borrar el registro
P. Recientemente tuve un problema con un archivo de registro de transacciones que creció muy grande. He podido reducirla. ¿Puede sugerir algunas cosas para mí comprobar?
**R.**Existen dos causas comunes de una consistencia de la base de datos comprobación SHRINKFILE (DBCC) no funciona correctamente en el registro de transacciones. Como nota, reducción de un archivo de registro no introduce fragmentación de índice perjudiquen su rendimiento en la manera que lo hace un encogimiento del archivo de datos. Sin embargo, aún debe ser una operación rara.
Un archivo de registro del encogimiento simplemente elimina cualquier porción actualmente no utilizado o inactivo de la transacción registro al final del archivo de registro de transacciones. Estas "partes" del registro de transacciones son conocidas como archivos de registro virtuales (VLFs). Hay dos problemas que pueden impedir ser capaz de reducir el VLFs: no realizar la operación real que permite VLFs vuelven inactivos y no tener VLFs inactivos al final del registro de transacciones.
VLFs vuelven inactivos mediante un proceso conocido como "borrar el registro." Puede hacer esto con un punto de control si utiliza el modelo de recuperación SIMPLE. También puede hacerlo con una copia de seguridad de registro de transacciones si utilizando los modelos de recuperación completa o BULK_LOGGED. Como los registros de transacciones en los VLFs no requieren SQL Server en modo alguno, puede hacer los VLFs inactivo.
SQL Server aún pueden requerir el registro graba para determinadas situaciones, como si son parte de una transacción de larga duración, si ellos no han sido analizados por el trabajo de agente de lector del registro de replicación, o si están en proceso de ser enviado a un espejo de la base de datos o réplica de grupo de disponibilidad. Puede preguntar por qué un registro de transacción en particular no "claro" con el siguiente comando de SQL Server:
SELECT [log_reuse_wait_desc] FROM sys.databases WHERE [name] = N'MyDBName';
Utilice la salida de ese comando como un indicador de qué hacer a continuación. Una vez que el registro de transacciones es capaz de borrar, si DBCC SHRINKFILE todavía no es capaz de reducir el registro, esto significa que sólo fue capaz de reducir hasta el VLF activa (o VLFs). Esto pueden ocurrir en el centro del archivo de registro de transacciones. En ese caso, realice la operación de remoción de registro y luego otro encogimiento.
Que usted tenga que hacer esto unas cuantas veces y, en última instancia, podría ser difícil o imposible reducir el registro de transacciones a su mínimo tamaño en una base de datos de producción ocupado. Sin embargo, estos problemas comunes debe ayuda usted reducir la transacción archivo de registro suficientemente satisfechos. Puedes leer más acerca de estos temas en mi de 2009 febrero TechNet Magazine artículo, "registro de comprensión y recuperación en SQL Server."
Integridad de la entrada-salida
P. Yo sigo viendo mensajes en el registro de errores de uno de mis instancias de SQL Server que i/os tienen que intentar varias veces antes de tener éxito. Esto parece inquietante para mí. ¿Puede explicar el significado de los mensajes?
**R.**Estos mensajes son instancias de mensaje 825. Este mensaje fue introducida en SQL Server 2005. Es una alerta temprana que el subsistema de E/S tiene problemas de integridad.
Si SQL Server emite una lectura I/O y la falla de I/O (ya sea el sistema operativo dice a SQL Server Error de E/S, o datos devueltos por el sistema operativo es juzgados por SQL Server como corrupto), SQL Server se volverá a intentar la operación de lectura cuatro veces más para ver si uno de ellos tendrá éxito. La premisa de esto es que a veces subsistemas de E/S tienen fallas transitorias, hasta volver a intentar una I/O error podría funcionar en un intento posterior. Esto evita la posibilidad inmediata de downtime.
Si ninguno de los reintentos éxito, SQL Server genera un error 823 o 824, y se interrumpe la conexión (como estos errores son gravedad 24). Si uno de los intentos de reintento tiene éxito, la carga de trabajo sigue siendo normal, y SQL Server escribe el mensaje 825 en el registro de errores.
El mensaje de 825 tiene el siguiente formato:
Msg 825, Level 10, State 2, Line 1.
Esto significa una lectura del archivo "J:\SQLskills\MyDatabase_DF1. FDN"en offset 0 000004AA188000 × sucedió después de fallar una vez con el error: checksum incorrecto (se espera: 0 × 33d1d136; real: 0 × 0a844ffd). Mensajes adicionales en el registro de eventos de SQL Server error log y sistema podrían proporcionar más detalles.
Esta situación de error amenaza la integridad de la base de datos y tendrá que corregir la situación. Completar un completo DBCC CHECKDB. Este error puede ser causado por muchos factores. Para obtener más información, consulte Libros en pantalla de SQL Server. Lo que realmente dice es que el subsistema de E/S está empezando a fallar. Una instalación similar existe en Exchange Server, donde se originó la idea de este mecanismo.
Aunque esta característica es útil, el mensaje 825 es sólo gravedad 10 (que significa informativo). A menos que usted está buscando a través de los registros de errores o tiene un agente alerta de mensaje 825, estos mensajes críticos pueden pasar inadvertidos. Sin embargo, debe tener una alerta fijado por 825 mensajes y actuar tan pronto como un mensaje de reintento de lectura ocurre. Puedes leer más acerca de este mensaje y sobre cómo configurar una alerta para recoger en este blog post.
.jpg)
Paul S. Randal es el director gerente 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 en Microsoft desde 1999 a 2007. Escribió DBCC CHECKDB/reparación para SQL Server 2005 y fue responsable por el motor de almacenamiento básico 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. Blogs de él en SQLskills.com/blogs/paul, y usted puede encontrarlo en Twitter en twitter.com/PaulRandal.