SQL Server - Consultas ejecutivas sobre en una DB de mision critica

Tengo una DB que pesa casi 70 Gb, tiene un alto nivel transaccional y un numero considerable de usuarios concurrentes todos los días; por lo que su consumo de memoria es considerable también. El servidor dispone de 32 Gb RAM sin restricción para SQL server.

Sobre esta DB se requieren aplicar procesos de business intelligence para obtener consultas ejecutivas para la toma de decisiones; hemos logrado varias de estas consultas mediante el siguiente esquema: tabla en producción -> vistas (para no trabajar con datos de producción) -> procedimientos almacenados -> funciones -> regreso a vistas, los procedimientos almacenados son llamados por un front-end desarrollado específicamente para mostrar esta informacion.

El tipo de consultas es p.ej:

Ventas por día: tabla ventas - tabla ventas detalle - grupo de artículos (agrupado por fecha)

Ventas por tipo de artículos: tabla ventas - tabla ventas detalle - grupo de artículos (agrupado por catalogo de artículos)

Ventas por mes: tabla ventas - tabla ventas detalle - grupo de artículos (agrupados por días del mes)

Ventas por sector, ventas por tienda, etc. Todas las consultas cruzan datos con "join"

La tabla ventas detalle cuenta con mas de 5 millones de registros; al filtrar en las vistas se procesan 250 mil registros en promedio según la consulta (procedimiento almacenado) que sea ejecutada, el front-end recibe un máximo de 90 registros para mostrar.

Cuando este esquema opera, el consumo de memoria se dispara de inmediato y consume casi el total disponible en minutos. Este es el motivo de la consulta, estamos abiertos a escuchar sugerencias sobre el esquema (que no sabemos si es el idóneo), sabemos que una sentencia "select" mal escrita o pobremente escrita es desastrosa en el uso de recursos.

Gracias desde ya por sus opiniones y comentarios.

HK

Menciona que VERSION-EDICION de SQL Server manejas

Dices: "El servidor dispone de 32 Gb RAM sin restricción para SQL server"

¿Y el OS? (sistema operativo), ¿Con que memoria trabajara?

Seguramente si ejecutas un SP_WHO2, tendras muchas conexiones en BACKGROUND.

¿Cuantos procesadores tienes?, ¿Esta la base TEMPDB con el numero suficiente de archivos de registro dependiendo del dato anterior?
¿Hay indices?, ¿Hay PLANES DE MANTENIMIENTO?, Tus tablas que tienen MILLONES de registros, ¿No estan particionadas?

Gracias Isaias por tus comentarios, con gusto proporciono enseguida los datos que preguntas:

Menciona que VERSION-EDICION de SQL Server manejas: MS SQL Server 2008 R2 Ent Ed 64bits Sp2

Dices: "El servidor dispone de 32 Gb RAM sin restricción para SQL server"

¿Y el OS? (sistema operativo), ¿Con que memoria trabajara? ¿Cuantos procesadores tienes?, Win Srv 2008 Std SP2 x64; 32 Gb RAM (es el tope para esta version de WinSrv), Doble Intel Xeon 2.6 Ghz Quadcore

Seguramente si ejecutas un SP_WHO2, tendras muchas conexiones en BACKGROUND. Negativo, solo tengo una conexión aún cuando opera el esquema descrito.

¿Esta la base TEMPDB con el numero suficiente de archivos de registro dependiendo del dato anterior? No se responderte esta pregunta, si me das un poco mas de detalles con gusto te proporciono el dato.
¿Hay indices?, ¿Hay PLANES DE MANTENIMIENTO?, Tus tablas que tienen MILLONES de registros, ¿No estan particionadas?
El rol principal de este servidor es de producción para un Sistema Administrativo Integral (ventas, contabilidad, almacenes, pagos, etc), la base de datos tiene casi 3 mil tablas; el esquema descrito es un beta adicional para reportes ejecutivos y business intelligence (BI). Con base en esto la respuesta a los indices es SI, si tiene indices de acuerdo al Sistema, y SI la base de datos está particionada "modularmente" (digamos un archivo de datos por cada modulo del sistema). SI hay planes de mantenimiento diario para backup y para reducción de archivos. Desafortunadamente hoy en dia es un único hardware que desempeña todas las funciones: app server, db server y "BI" server. La buena noticia es que estamos migrando a una nueva plataforma de vservers donde separamos las funciones. Los dos vservers tienen las mismas características que el actual a excepción de que son solo dualcore.

Espero que la información sea de utilidad y nuevamente agradezco tus comentarios.

Si le dejas los 32 GB a SQL Server, tu sistema operativo y los demas aplicativos que tienes, NO contaran con memoria.

Debes topar la memoria a SQL Server. dejale uno 25 GB para SQL y el resto para tus operaciones de OS

A partir de 2005, la base TEMPDB, debe tener tantos archivos adicionales NDF como procesadores tenga, hasta un maximo de 8.

En tu PLAN DE MANTENIMIENTO, debes incluir la REGENERACION DE INDICES y la ACTUALIZACION DE ESTADISTICOS, ademas claro, de la verificacion de la INTEGRIDAD DE TU BASE DE DATOS.

Cuando una tabla crece de forma exponencial (MILLONES DE RGISTROS), esta(s) deben particionarse, manejarse en diferentes filegroups.

Isaias nuevamente gracias por los comentarios.

Si le dejas los 32 GB a SQL Server, tu sistema operativo y los demas aplicativos que tienes, NO contaran con memoria.
Debes topar la memoria a SQL Server. dejale uno 25 GB para SQL y el resto para tus operaciones de OS
Los 32 es el total de RAM que tiene el hardware, las opciones de asignación de memoria para SQL están por default.

A partir de 2005, la base TEMPDB, debe tener tantos archivos adicionales NDF como procesadores tenga, hasta un maximo de 8.
Esta recomendación para TEMPDB aplica solo a LDF o también para MDF?

Cuando una tabla crece de forma exponencial (MILLONES DE RGISTROS), esta(s) deben particionarse, manejarse en diferentes filegroups
La DB del sistema está particionada, un filegroup por cada modulo del sistema.

Respecto al esquema de creación de consultas ejecutivas, cual es tu opinión?
DBProducción -> Vistas -> Stored procedures <- Functions <-Vistas

Las vistas son para no utilizar directamente datos de producción.
Los SP son invocados desde el front-end ("exec sp1"), enviando al usuario no mas de 90 registros para mostrar.

Saludos.

Son Vistas materializadas, o simplemente Vistas???

La diferencia es grande, la vista materializada es una copia de tus datos ya alineados y desnormalizados generalmente lo que permite una consulta mas rápida.

La vista (normal) sigue tomando los datos originales es solo un query y cada que la llamas se ejecuta (aquí puede tardar).

La desventaja es que las vistas materializadas requieren de un tiempo de refresco por lo que no tienes información en línea, si tus informes o consultas ejecutivas, se realizan de acumulados donde lo que esta ocurriendo el dia de hoy no tiene tanta relevancia quizás esta seria una solución.

Te doy un ejemplo:
Si la consulta ejecutiva es por ejemplo consolidados de ventas a corte mensual, quizás el recargo de la vista materializada te tome 15 min. pero esto lo dejas por la noche, cuando consulten tus ejecutivos tendrán la imagen y los datos consolidados a la noche previa, y la respuesta de la consulta será muy rápida por que solo presentara los datos que ya sumo (no los millones de registros para sumar), desventaja que dicha consulta será la misma a las 8 de la mañana como las 6 de la tarde, por que las ventas del dia no se reflejaran hasta que vuelva a refrescar la vista en la noche...

Espero esto te brinde algo mas de luz....

Rafael Gracias por tus comentarios.
Hasta ahora hemos trabajado con vistas "normales" (querys).
Sin duda la utilidad de las consultas ejecutivas son los datos en linea para algunos reportes. Justo esta semana terminamos algunas pruebas con lo que comentas sobre consultas en linea y acumuladas por dia o por mes. La conclusión por el momento será trabajar algunas en línea (las menos) y otras acumuladas.
Cualquier sugerencia es bienvenida; mientras tanto agradezco nuevamente tus comentarios, por supuesto que arrojaron luz sobre el tema.
Un saludo.

Mis respuestas a tus RESPUESTAS:


Los 32 es el total de RAM que tiene el hardware, las opciones de asignación de memoria para SQL están por default.
R = Pesima decision

Esta recomendación para TEMPDB aplica solo a LDF o también para MDF?
R= Claramente indique que son NDF, por lo tanto solo aplica a los MDF.

La DB del sistema está particionada, un filegroup por cada modulo del sistema.
R= Me refiero a las TABLAS con millones de registros, no a la base de datos.

DBProducción -> Vistas -> Stored procedures <- Functions <-Vistas
R= Rafael ya te dio un comentario sobre las VISTAS, ahora bien, si tu proceso es de EXPLOTACION DE INFORMACION, jamas, pero jamas en la vida ataques una base de tipo OLTP (On Line Transacction Processing) con consultas pesadas.

Piensa en un esquema de Datawarehouse o bien, puedes crear una base INSTANTANEA (Snapshot), donde las consultas "VUELAN", ya que son solo de tipo LECTURA.

Isaias gracias por los comentarios.
Como comentaba en la respuesta anterior a Rafael después de realizar pruebas creo que declinaremos (por el momento y para tener los primeros resultados) con vistas en linea y algunas acumuladas.
Tengo algunas dudas finales.
1. En tu experiencia, que porcentaje de memoria de un DB server es conveniente tener asignado al DBMS?
2. Sugieres un esquema DWH, ademas del VisualStudio, recomiendas alguna herramienta de terceros con esta funcionalidad? He visto varias en la web.
3. Una snapshot te refieres a un esquema de replicación?

Nuevamente gracias y un saludo.