R: & q de SQL Inserción de rendimiento de SQL
Siempre hay varias maneras de empujar el sobre de rendimiento de SQL Server, y a menudo no cuesta ningún dinero para hacerlo.
Paul S. Randal
El nuevo Normal
P. Estamos a punto de iniciar un proyecto que remodelará nuestro esquema de base de datos y estamos preocupados por cómo mucho a normalizar las cosas. ¿Tienes algún Consejo?
**R.**Ser cauteloso al empujar normalización demasiado lejos. El objetivo habitual de un ejercicio de normalización es la tercera forma Normal, o 3NF, donde todos los atributos clave no ayudan a definir la clave. Hay una frase útil que describe esta regla: "Los atributos definen la clave, la clave de toda y nada, pero la clave, así que me ayude Codd." (E.F. Codd definió originalmente 3NF en 1971; ver este enlace para obtener más información. La frase se basa en el juramento uno Jura en una sala.)
El problema con empujando para 3NF es que muchas personas van demasiado lejos, lo que yo llamo over-normalizing. Esto puede causar problemas de rendimiento enorme carga cuando las consultas tienen numerosas tablas para materializar las relaciones significativas. Una sucesión de combinaciones es entonces la única forma de llegar a las diversas entidades.
Por ejemplo, tenemos un cliente cuyo esquema fue escrita hace 10 años por un desarrollador de C# que no era particularmente SQL Server más experimentados. Cada entidad posible se almacena en su propia tabla y tiene un ID. Todos los atributos de entidad se almacenan en sus propias tablas, con su propio ID. Las relaciones mediante el cual las entidades pertenecen a otras entidades se almacenan en sus propias tablas. La historia de cómo ha cambiado cada atributo para cada entidad se almacena en su propia tabla, con su propio ID.
Así que una tabla de entidad particular puede tener referencias de clave externa a muchas otras tablas. Se trata de un esquema fuertemente normalizado donde hacer casi nada requiere unirse a 10 o más tablas.
Lo que es peor están que hay no hay estrategias de archiving. Hay muchos años de datos en la base de datos resultante en combinaciones que tienen que procesar grandes cantidades de datos. Varias cosas se deben hacer para solucionar este problema:
- Quitar datos antiguos para reducir el tamaño de los datos e implementar una estrategia de archiving automatizada, manteniendo el conjunto de datos activo pequeños.
- Examinar utilizando filtrados predicados de consulta para reducir la cantidad de operaciones en datos e índices.
- Considere la posibilidad de de-normalizing partes del esquema para permitir algunas tablas contraer juntos para reducir la complejidad de la combinación.
Aquí está la regla de oro a tener en cuenta al diseñar el nuevo esquema: Piense en las operaciones de T-SQL requiere efectivamente consultar los datos e intentar minimizar combinaciones excesivas o innecesariamente operan en conjuntos de datos muy grandes. Debe también prototipo de su diseño y ejecutar algunas consultas representativas en una alta carga de trabajo para ver si existen cuellos de botella diseño obvio.
Una vez que has puesto un esquema de producción resulta increíblemente difícil y caro para hacer cualquier cambio. Esto significa que puede pasar mucho tiempo afinar las consultas e indexación estrategias para compensar el rendimiento del esquema normalizado.
Rompecabezas de rendimiento
P. Nuestra mesa principal tiene un índice agrupado en una columna de identidad entero único. Filas de la tabla nunca se actualizan después de que se ha insertado y he mantenido el tamaño de fila razonablemente pequeño. Estoy encontrando de que, con un mayor número de conexiones simultáneas, se ralentice rendimiento de las consultas. ¿Tiene alguna idea de lo que podría ser el problema?
**R.**Ha diseñado su índice agrupado con nuestras directrices de mejores prácticas, donde debería estar la clave del índice agrupado único, estático, estrecha y creciente. Esto funciona perfectamente hasta llegar a una carga de trabajo de high-end.
Por ejemplo, con varios miles de conexiones simultáneas (o quizás incluso cientos) todos insertar en la tabla, va a estar bloqueando. Si miramos los bloqueos con el sys.dm_tran_locks vista de administración dinámica (DMV), no muestra nada mal. Todos los subprocesos tienen bloqueos de página IX (en uno de unas pocas páginas) y bloqueos clave X (sobre registros en esas páginas). Las cosas se ven normales, pero hay definitivamente algunas pasando bloqueo. El bloqueo sólo no implica bloqueos. Puede encontrar más información acerca de los bloqueos y bloqueo en SQL Server en En línea de libros.
Si nos fijamos en el sys.dm_os_waiting_tasks DMV verá que la mayoría de subprocesos en espera con un tipo de espera de PAGELATCH_EX en uno de unas pocas páginas. Esto es debido a un bloqueo no es suficiente para poder cambiar una copia en memoria de una página de archivo de datos. El sys.dm_os_waiting_tasks es uno de la DMV más útiles, por cierto, porque demuestra lo que esperan todos los "hilos" en SQL Server.
Que la copia de la página en memoria es realmente una estructura de datos, como SQL Server está preocupado. No se puede cambiar una estructura de datos con sólo un bloqueo relacional. Debe proteger la integridad de la estructura de datos desde varios subprocesos acceder y cambiar a la vez. Se trata de una ciencia de la computación básica fundamental. Puede administrar acceso exclusivo para cambiar la estructura de datos de SQL Server mediante un mecanismo de sincronización ligero llamado un pestillo, que yo he discutido aquí anteriormente.
Como el índice agrupado en una columna de identidad, inserciones será un patrón de inserción sólo anexar con muchas filas en cada página de datos. Esto es positivo. Sin embargo, esto significa que muchos hilos intentará insertar filas simultáneamente en la misma página de archivo de datos. Los bloqueos necesarios no causan bloqueo porque cada subproceso tiene bloqueos de página compatible y bloqueos de fila en filas individuales. Sin embargo, los subprocesos que todos necesitan adquirir una exclusiva acoplar en la página. Esto no es posible simultáneamente. A medida que aumenta la carga de trabajo, esto puede conducir a importantes problemas de performance de bloqueo y consulta.
Hay una variedad de soluciones para esto, incluyendo:
- Uso de particiones para repartidos y la carga de trabajo de insertar varias particiones de una tabla (o incluso varias tablas)
- Cambiar una clave de clúster compuesto naturalmente distribuida las inserciones en varios puntos en la tabla (a expensas de causando fragmentación)
La línea de fondo, sin embargo, es que el síntoma de bloqueo no siempre significa que el bloqueo es el problema.
Ponderación de rendimiento
P. Hemos tenido problemas de rendimiento con nuestro servidor SQL y nuestros desarrolladores decir que necesitamos un servidor más potente para ejecutar nuestra carga de trabajo. Estamos clásicos DBAs "involuntarios". ¿Puede darnos algún Consejo sobre qué otra cosa puede ayudar, que comprar más hardware?
**R.**Personas a menudo no dan cuenta de que el pobre desempeño es un síntoma de un problema fundamental con la forma en que utiliza SQL Server. La suposición es está llegando a los límites de capacidad de su hardware actual y es el momento de invertir en un servidor con CPUs más y más rápidos. Esto puede ser una suposición costosa porque mover a un servidor con más CPU abre más ventanas de tiempo y puede conducir a la contención aún mayor y menor rendimiento de la carga de trabajo.
Realmente hemos visto eso un par de veces debido a malas prácticas de programación T-SQL.
Hemos demostrado repetidamente cómo sería mucho más barato y más beneficioso para resolver problemas de rendimiento subyacentes en lugar de agregar hardware. Antes de tomar el mandrinado con nuevo hardware, hay muchas cosas que usted puede mirar a ver si es un problema solucionable. Estos son algunos ejemplos:
- Buscar en el sys.dm_os_waiting_tasks DMV para ver donde se está gastando el tiempo a la espera de recursos. Si el tiempo es dedicado a la espera de las aplicaciones de cliente procesar datos, optando por el hardware del servidor no hará una diferencia. Puede ver este patrón si muchas filas en el DMV habían salida lista ASYNC_NETWORK_IO como el tipo de espera.
- Examinar las latencias de lectura/escritura de subsistema I/O usando el sys.dm_io_virtual_file_stats DMV para ver si el subsistema de E/s está siendo impulsado demasiado duro. Esto podría indicar la necesidad de una estrategia mejor indexación. Tempdb también podría ser un cuello de botella, lo que podría indicar el uso excesivo de tablas temporales.
- Mire el falta índice DMV para ver si faltan índices de alto impacto.
- Mirar sys.dm_db_index_usage_stats para ver si está manteniendo — pero no usando: una tonelada de índices.
Cambios simples pueden tener un efecto significativo en el rendimiento de SQL Server. A veces simplemente ha crecido el volumen de datos y necesita más memoria del servidor para quitarse la presión el pool de buffer. A veces el subsistema de E/s realmente necesita un impulso. No es muy a menudo, sin embargo, que necesita comprar un servidor más grande, más rápido.
Líneas paralelas
P. Tenemos algunas consultas que causan problemas cuando se ejecutan en paralelo y nos gustaría limitar a ser único subproceso. ¿Cómo podemos hacerlo sin hacer todo solo hilo mediante el servidor de la opción de MAXDOP a uno?
**R.**Como antecedente, MAXDOP significa "grado máximo de paralelismo". Eso es básicamente cuántos subprocesos paralelos simultáneos puede utilizar una consulta cuando se ejecuta.
Es bueno que no eres "espasmos hasta desaparecer rodilla" y establecer todo MAXDOP, como paralelismo es una de las mejores características de rendimiento de SQL Server. Mucha gente hace eso, basado especialmente en mala asesoría indicando la prevalencia de CXPACKET espera (de hacer análisis de las estadísticas de espera) debe fijarse por detener el paralelismo.
Uno de los problemas con el uso de la opción de sp_configure para deshabilitar el paralelismo (aparte del hecho que afecta a todo en el servidor) es que cualquier persona con cualquier nivel de privilegio puede anular esta configuración utilizando una sugerencia de consulta MAXDOP en sus consultas. Siempre podría especificar una sugerencia de consulta uno de MAXDOP en todas las consultas que desee limitar. Eso puede ser poco práctico, aunque, con cientos o miles de consultas involucradas.
Dos métodos son mucho más eficaces en limitar el paralelismo:
- Aumentar el servidor todo el "umbral de costo para paralelismo". Se trata de un número arbitrario generado durante la compilación de la consulta. Se utiliza para decidir si se va a generar un plan de consulta que se puede ejecutar en paralelo o no. Al aumentar este número, puede prevenir eficazmente algunas consultas que no deberían ejecutar en paralelo de hacerlo. No se puede anular esta configuración mediante una sugerencia de consulta, o bien. Puede leer más acerca de esta configuración y cómo cambiarlo en un blog por Jonathan Kehayias aquí.
- También puede utilizar la función Resource Governor (en Enterprise Edition solamente). Esto le permite poner las consultas en "depósitos" (llamados grupos de carga de trabajo) y, a continuación, asignar un MAXDOP para cada cubo. Puede anular la opción de sp_configure MAXDOP pero no Resource Governor. Puede utilizar cualquier filtro desea decidir las consultas que van en los cubos. Puede tener varios cubos con diferentes ajustes de MAXDOP así. Más personas están utilizando este método para obtener un control preciso sobre el paralelismo.
.jpg)
Paul S. Randal es el director 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 y 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 experto en recuperación ante desastres, alta disponibilidad y mantenimiento de bases de datos, y es moderador habitual en congresos en todo el mundo. Blogs de él en SQLskills.com/blogs/paul, y se le puede encontrar en Twitter en twitter.com/PaulRandal.