Consultas ejecutivas en una DB sobre SQL Server
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 -> rergreso 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 intersectan 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 sintaxis "select" mal escrita es desastrosa en el uso de recursos.
Aquí tengo una observación, es mejor separar la base de datos transaccional del BI, con mucha más razón si tu BD pesa 70GB.
Una duda que se me presenta es la siguiente, según lo que comentas, los reportes gerenciales las obtiene online, en otras palabras. Si necesito obtener la venta de todo un mes, ¿en ese momento realiza el select desde tu BD transaccional?
Te diré, todo lo que tiene que ver con BI, debes construirte una BD para ello y consultarla de esa BD. Me parece que el problema va por ahí. Al crear una BD para tus consultas ejecutivas, como las llamas, esas tablas las poblarás por ejemplo en la madrugada. Con eso tu BD transaccional quedará sólo para tus operaciones y tu otra BD para tus consultas gerenciales.
No te olvides también de verificar el plan de ejecución de tus consultas para ver el recorrido y revisar también la lectura que realiza esas consultas a nivel físico y lógico.
Si tienes algo más por ahí encantado de poder compartir algunas experiencias.
Cuídate.
