sql-server - tuning - plan de ejecución y optimización de consultas sql server 2008 r2

Manejo de grandes bases de datos (14)

He estado trabajando en un proyecto web (asp.net) durante aproximadamente seis meses. El producto final está a punto de comenzar. El proyecto usa SQL Server como la base de datos. Hemos realizado pruebas de rendimiento con grandes volúmenes de datos, los resultados muestran que el rendimiento se degrada cuando los datos se vuelven demasiado grandes, digamos 2 millones de filas (problemas de tiempo de espera, respuestas retrasadas, etc.). Al principio estábamos usando una base de datos totalmente normalizada, pero ahora lo normalizamos parcialmente debido a problemas de rendimiento (para reducir las uniones). En primer lugar, ¿es la decisión correcta? Además, ¿cuáles son las posibles soluciones cuando el tamaño de los datos se vuelve muy grande, como el no. de los clientes aumentan en el futuro?

Me gustaría agregar más:

2 millones de filas son tablas de entidades, las tablas que resuelven las relaciones tienen filas mucho más grandes.
El rendimiento se degrada cuando los datos + no. de usuarios aumenta.
La desnormalización se realizó después de identificar las consultas muy utilizadas.
También estamos usando una gran cantidad de columnas xml y xquery. ¿Puede ser esta la causa?
Un poco fuera del tema, algunas personas en mi proyecto dicen que la consulta dinámica de sql es más rápida que un enfoque de procedimiento almacenado. Han realizado algún tipo de prueba de rendimiento para probar su punto. Yo pienso que lo opuesto es verdadero. Algunas de las consultas más utilizadas se crean dinámicamente cuando la mayoría de las demás consultas se encapsulan en procedimientos almacenados.

Al principio estábamos usando una base de datos totalmente normalizada, pero ahora lo hemos hecho parcialmente normalizado debido a problemas de rendimiento (para reducir las uniones).

Como dice el viejo refrán "normalizar hasta que duela, denormalise hasta que funcione".

Es bastante común en dbs grandes y de uso intensivo ver un grado de desnormalización para ayudar al rendimiento, por lo que no me preocuparía demasiado por eso ahora, siempre y cuando su rendimiento esté donde usted quiere y su código lo administre. los campos "denormalizados" no se vuelven demasiado onerosos.

¿Cuáles son las posibles soluciones cuando el tamaño de los datos se vuelve muy grande, como el no. de los clientes aumentan en el futuro?

Como no sabe demasiado sobre el dominio de su aplicación, es difícil decir cómo puede hacerlo a futuro, pero dividir los datos usados recientemente y antiguos en tablas separadas es un enfoque bastante común en las bases de datos con mucho tráfico: si el 95% de sus usuarios están consultar sus datos de los últimos 30/45 días, tener una tabla de "datos en vivo" que contenga, por ejemplo, los datos de los últimos 60 días y una "información antigua" para los elementos anteriores puede ayudar a su desempeño.

Una buena idea sería asegurarse de tener una amplia configuración de supervisión del rendimiento para que pueda medir el rendimiento de su base de datos a medida que aumentan los datos y la carga. Si encuentra un descenso notable en el rendimiento, ¡quizás sea hora de revisar sus índices!

Como dice el viejo refrán "normalizar hasta que duela, denormalise hasta que funcione".

¡Amo esto! Esto es típicamente el tipo de cosa que no debe ser aceptada más. Me imagino que, en DBASEIII veces, donde no podías abrir más de 4 tablas a la vez (a menos que cambies algunos de tus parámetros AUTOEXEC.BAT Y reinicies tu computadora, ¡ay! ...), hubo un cierto interés en la desnormalización .

Pero hoy veo esta solución similar a la de un jardinero que espera un tsunami para regar su césped. Utilice la regadera disponible (SQL profiler).

Y no olvide que cada vez que desnormaliza parte de su base de datos, disminuye su capacidad de adaptarse aún más, a medida que aumenta el riesgo de errores en el código, haciendo que todo el sistema sea cada vez menos sostenible.

Creo que es mejor mantener los datos de tipo OLTP desnormalizados para evitar que los datos centrales se "contaminen". Eso te morderá en el camino.

Si el cuello de la botella se debe a las necesidades de informes o de solo lectura, personalmente veo que no hay problema con las tablas de informes desnormalizadas además de las tablas de "producción" normalizadas; crea un proceso para enrollar al nivel que necesites para hacer las consultas ágiles. Un simple SP o proceso nocturno que periódicamente se acumula y desnormaliza las tablas que se usan solo de forma lectora a menudo puede hacer una gran diferencia en la experiencia del usuario.

Después de todo, ¿de qué sirve tener un conjunto de datos teóricamente limpio y perfectamente normalizado si nadie quiere utilizar su sistema porque es lento?

Después de haber analizado los índices y las consultas, es posible que desee simplemente por más hardware. Unos cuantos gigs más de ram podrían hacer el truco.

En el esquema de cosas, unos pocos millones de filas no son una base de datos particularmente grande.

Suponiendo que estamos hablando de una base de datos OLTP, denormalizar sin identificar primero la causa raíz de los cuellos de botella es una idea muy, muy mala .

Lo primero que debe hacer es perfilar la carga de trabajo de su consulta en un período de tiempo representativo para identificar dónde se está haciendo la mayor parte del trabajo (por ejemplo, usando el Analizador de SQL, si está usando SQL Server). Mire la cantidad de lecturas lógicas que realiza una consulta multiplicada por el número de veces ejecutadas. Una vez que haya identificado las diez consultas de peor rendimiento, debe examinar los planes de ejecución de la consulta en detalle.

Me voy a quedar en una extremidad aquí (porque usualmente es el caso), pero me sorprendería que tu problema tampoco sea

Ausencia de los índices de cobertura "correctos" para las consultas costosas
Configurado incorrectamente o debajo del subsistema de disco especificado

Esta respuesta SO describe cómo hacer un perfil para encontrar las consultas de peor rendimiento en una carga de trabajo.

En primer lugar, como muchos otros han dicho, unos pocos millones de filas no son grandes. La aplicación actual en la que estoy trabajando tiene varias tablas, todas con más de cien millones de filas en las que están todas normalizadas.

Sufrimos un rendimiento pobre, pero esto fue causado por el uso de la configuración de estadísticas de la tabla predeterminada. Insertar números pequeños de registros relativos al tamaño total de la tabla, es decir, insertar un millón de registros en una tabla que contiene más de 100 millones de registros no estaba causando una actualización automática de las estadísticas de la tabla y así obtendríamos planes de consulta pobres que se manifestaron como consultas seriales que se producen en lugar de paralelas.

En cuanto a si es la decisión correcta de desnormalizar, depende de su esquema. ¿Tiene que realizar consultas profundas con regularidad, es decir, un montón de combinaciones para obtener datos a los que normalmente necesita acceder, si es así, la desnormación parcial puede ser un paso adelante.

PERO NO ANTES de que haya verificado sus estrategias de estadísticas de indexación y tabla.
Compruebe que está utilizando consultas sensatas y bien estructuradas y que sus uniones están bien formadas. Verifique sus planes de consulta para que sus consultas realmente estén analizando de la forma esperada.

Como otros han dicho, SQL Profiler / Database Engine Tuning Advisor realmente hacen un buen trabajo.

Para mí, la desnormalización suele estar al final de mi lista de cosas que hacer.

Si aún tiene problemas, consulte la configuración del software y el hardware del servidor.

¿Están su base de datos y archivos de registro en discos físicos separados que usan controladores separados?
¿Tiene suficiente memoria?
¿El archivo de registro está configurado para crecer automáticamente? De ser así, el límite de autocrecimiento es bajo, es decir, está creciendo a menudo.

Esa puede no ser la decisión correcta. Identifique todas las interacciones de su base de datos y perfíllese de forma independiente, luego encuentre las ofensivas y elabore estrategias para maximizar el rendimiento allí. También activar los registros de auditoría en su base de datos y extraerlos podría proporcionar mejores puntos de optimización.

Interesante ... muchas respuestas aquí ...

¿La versión de rdbms / os es de 64 bits?

Me parece que el rendimiento se degrada varias veces. parte de la razón se debe sin duda a la indexación. ¿Ha considerado la partición de algunas de las tablas de una manera consistente con la forma en que se almacenan los datos? Es decir, crea particiones basadas en cómo entran los datos (según el orden). Esto le dará un gran aumento de rendimiento ya que la mayoría de los índices son estáticos.

Otro problema es la información xml. ¿Estás utilizando índices xml? De books on line (2008) "Utilizando el índice XML primario, se admiten los siguientes tipos de índices secundarios: RUTA, VALOR y PROPIEDAD".

Por último, ¿el sistema está diseñado actualmente para ejecutar / ejecutar una gran cantidad de sql dinámico? Si es así, tendrá una degradación desde una perspectiva de memoria ya que los planes deben generarse, generarse de nuevo y rara vez se deben volver a generar. Yo llamo a esto memoria revuelta o palpitaciones de memoria.

HTH

Normalmente, 2 millones de filas no son una base de datos muy grande, según el tipo de información que almacene. Por lo general, cuando el rendimiento se degrada, debe verificar su estrategia de indexación. El Asesor de ajuste del motor de base de datos de SQL Server puede ser de ayuda allí.

Puede haber un millón de razones para eso; utilice el Analizador de SQL y el Analizador de consultas para determinar por qué sus consultas se vuelven lentas antes de pasar por el camino del "cambio de esquema". No es improbable que todo lo que necesite hacer sea crear un par de índices y programar "estadísticas de actualización" ... ... pero como dije, Profiler y Query Analyzer son las mejores herramientas para descubrir lo que está pasando. .

Siempre hemos tratado de desarrollar usando una base de datos lo más cercana posible al "mundo real". De esta forma evitarás muchos errores como este, ya que cualquier desarrollador se volvería mental si su conexión no funcionaba durante la depuración. La mejor manera de solucionar problemas de rendimiento Sql OMI es lo que sugiere Mitch Wheat; perfil para encontrar las secuencias de comandos ofensivas y comenzar con ellas. La optimización de los scripts puede llevarlo lejos y luego debe mirar los índices. También asegúrese de que su servidor Sql tenga suficiente potencia, especialmente IO (disco) es importante. Y no lo olvides; caché es el rey. La memoria es barata; comprar más. :)

Tienes razón para hacer lo que funcione.
... siempre que se dé cuenta de que puede haber un precio que pagar más tarde. Parece que estás pensando en esto de todos modos.

Cosas para verificar:

Bloqueos

¿Están todos los procesos accediendo a las tablas en el mismo orden?

Lentitud

¿Hay alguna consulta haciendo tablescans?
- Compruebe si hay uniones grandes (más de 4 tablas)
- Revisa tus indeces

Ver mis otras publicaciones sobre consejos generales de rendimiento:

Unos pocos millones de registros son una pequeña base de datos para SQL Server. Puede manejar terrabytes de datos con muchas combinaciones, sin sudar. Es probable que tenga un problema de diseño o consultas muy mal escritas.

Felicitaciones por las pruebas de rendimiento antes de que comiences a funcionar. Es mucho más difícil arreglar esto después de que haya estado en producción durante meses o años.

Lo que hiciste es probablemente una mala elección. Si se desnormaliza, debe configurar activadores para asegurarse de que los datos permanezcan sincronizados. ¿Hiciste eso? ¿Cuánto aumentó su tiempo de inserción y actualización?

Mi primera suposición sería que no has puesto índices en las claves externas.

Otras suposiciones sobre lo que podría ser incorrecto incluyen, uso excesivo de elementos tales como: subconsultas correlacionadas funciones escalares vistas llamadas vistas cursores Tablas EAV falta de sargabilidad uso de select *

El diseño deficiente de la mesa también puede dificultar el buen desempeño. Por ejemplo, si sus tablas son demasiado anchas, acceder a ellas será más lento. Si a menudo está convirtiendo datos a otro tipo de datos para usarlos, entonces lo tiene almacenado incorrectamente y esto siempre será un arrastre en el sistema.

Dynamic SQl puede ser más rápido que un proceso almacenado, puede que no. No hay una respuesta correcta aquí para el rendimiento. Para la seguridad interna (no es necesario establecer derechos a nivel de tabla) y la facilidad de realizar cambios en la base de datos, los procesos almacenados son mejores.

Debe ejecutar el generador de perfiles y determinar cuáles son sus consultas más lentas. Consulte también todas las consultas que se ejecutan con mucha frecuencia. Un pequeño cambio puede pagar cuando la consulta se ejecuta miles de veces al día.

También deberías obtener algunos libros sobre la optimización del rendimiento. Esto lo ayudará con el proceso ya que los problemas de rendimiento pueden deberse a muchas cosas: Diseño de la base de datos Diseño de la consulta Indización de hardware, etc.

No hay una solución rápida y la desnormalización aleatoria puede ocasionarle más problemas que si no mantiene la integridad de los datos.

Primero asegúrese de que su base de datos esté razonablemente sana, ejecute DBCC DBREINDEX si es posible, DBCC INDEXDEFRAG y actualice las estadísticas si no puede pagar el rendimiento alcanzado.
Ejecute Profiler por un tiempo de muestra razonable, suficiente para capturar la mayoría de las funciones típicas, pero filtre por una duración mayor a algo así como 10 segundos, no le importan las cosas que solo toman unos pocos milisegundos, ni siquiera mire esas .
Ahora que tiene sus consultas de más larga duración, sintonícelas; obtenga los que se muestran más, mire los planes de ejecución en el Analizador de consultas, tómese un tiempo para comprenderlos, agregue índices donde sea necesario para acelerar la recuperación
mira crear índices cubiertos; cambie la aplicación si es necesario si está haciendo SELECT * FROM ... cuando solo necesita SELECT LASTNAME, FIRSTNAME ....
Repita el muestreo del perfilador, con una duración de 5 segundos, 3 segundos, etc. hasta que el rendimiento cumpla con sus expectativas.