SQL Server performance y cache.

Optimización de SQL Server mediante cache en SQL.

Hace unas semanas tuve que implementar un proceso de optimización para SQL Server.

El problema inicial es una aplicación que solicita datos a un motor SQL Server, produce un query que demora unos 12 segundos para obtener los datos a enviar al cliente, ese query se ejecuta durante todo el dia, todas las veces que lo pida un cliente, pero desde "negocios" acordaron actualizar el proceso cada 8 horas. Existe un estimado de 5000 usuarios activos pidiendo sus datos. La medida del resultado del query es de unos 300 kb.

También existe otro proceso similar donde se definió una actualización cada hora.
Hay miles de usuarios pidiendo decenas de veces los mismos datos cada NN segundos.

Aunque 5000 usuarios no es un número muy grande, miles de sus peticiones durante todo el dia tiran al servidor SQL, el consumo de CPU es muy alto, aumenta la demora para resolver cada petición (de 12 a 40 segundos) hasta que entra en un espiral del que no puede salir, la cantidad de peticiones comienza a ser mayor que la cantidad que puede resolver.

Como muchas veces pasa, el aplicativo tiene muchos años, el volúmen de datos aumentó mucho, diferentes razones hacen que los 5000 clientes hagan varias peticiones (de 2x a 5x en algunos casos), dependiendo de cuantos equipos usan durante el dia, usan mas de un equipo (PCs, teléfonos, tables...) al final todo implica miles de peticiones cada minuto.

Luego de varias pruebas encontramos que existe un máximo de 10 peticiones que se pueden realizar al mismo tiempo y que el motor SQL puede resolver, si se aumenta ese valor comienzan a aumentar los tiempos de respuesta de cada una, si continúan o aumentan las peticiones ya no hay solución.

Es un aplicativo crítico, visible desde internet, muy usado todo el dia y con datos que deben ser actualizados cada hora ó cada ocho horas.

Luego de darle unas cuantas vueltas y evaluar varias alternativas, lo mas rápido y simple era implementar un cache del lado del motor SQL. Si, conocemos Redis y otras soluciones de cache, pero las otras soluciones requerían muchos mas cambios en distintos componentes, el "cache de SQL" es un punto único a cambiar. (un SP.)

La solución requería unas cuantas horas de programación de parte de un DBA experto, pruebas y salir al "live" rápidamente.


SOLUCION IMPLEMENTADA

Crear una tabla caché para cada proceso (2 tablas independientes), que contiene unos 25 campos (que son los que se envían desde el motor SQL al Aplicativo), ademas de tres campos adicionales, una fecha/hora (timestamp), un campo "id_usuario" y un campo ID_PK (PK, incremental).

En los 25 campos se guardaría los registros procesados (entre 100 y 500) que son los datos pedidos por los usuarios, en el campo fecha guardamos un timestamp del momento del cálculo, el campo ID_USUARIO contendría el ID del usuario que hizo la petición y el IDPK incremental sirve para que queden almacenados en el mismo órden en que fueron generados.

Los datos deben ser únicos, agrupados y ordenados, estas condiciones son las que demoran el query original.

Luego creamos un índice para que la lectura de esos registros para cada usuario sera la mas rápida posible.

El campo ID_USUARIO tiene datos redundantes pero me permitirá simplificar los procesos y tiempos. Se podría usar otra tabla normalizada, pero por razones de optimización lo descartamos.

Desde el APP se invocaba a un SP (Stored Procedure) pasando un identificador de usuario. El SP devolvía el join/where/group/order de unas 30 tablas en formato de 25 campos por 100 a 500 registros. (los dos SPs eran similares aunque el contenido era distinto)

Las tablas caché miden actualmente unos 2gb cada una, pero resuelven el problema de las NN peticiones repetidas, limitando el reproceso a una/ocho horas.

El nuevo SP incluye estas partes:
* El campo pasado como parámetro se usa para buscar en la tabla "cache" si tenemos datos para ese usuario, obtiene el primer registro y obtiene la fecha de la última grabación.
* Si no había datos, tendremos que generarlos.
* Si había datos pero había pasado el tiempo de "Caché válido", tendremos que generarlo.
* En caso contrario enviamos los registros que tenemos en nuestra tabla caché.
(ese proceso envía los datos en 1 segundo, pasando de 12-30 segs a solo 1)

* El SP entonces calcula los datos actualizados (demora entre 12 y 30 segundos).
* Se los envía al usuario.
* Borra los registros de la tabla cache para ese usuario.
* Luego los guarda en el cache (agregando timestamp, IDUsuario y el automático del IDPK)


SP Simplificado.

CREATE PROCEDURE proceso1
@user_id int -- parametro de entrada (ID)
begin

.......

-- @CUANDO es ahora
-- cuando es el campo de generación del cache.

SET @ACEPTO = ISNULL((
-- cuantos minutos pasaron desde la ultima peticion
SELECT TOP 1 ABS(DATEDIFF(minute, cuando, @CUANDO)) FROM CACHE WHERE user_id = @user_id
),0)

SET @ACEPTO2 = ISNULL((
-- en caso de tener cache cual fue la fecha de generacion
SELECT TOP 1 FECHAFIN FROM CACHE WHERE user_id = @user_id
),'2017-01-01')

-- solución para renovar el cache luego de 60 minutos.
-- si pasaron menos de 60 minutos,
-- mas de CERO, es decir "Tengo cache"
-- y los datos guardados corresponden a hoy
IF ( @Acepto > 0 AND @Acepto < 60 and @Acepto2 = @DiaFinD )
begin
-- el cache todavía es válido, lo envío
select * from CACHE WHERE user_id = @user_id;
END
ELSE
begin
-- aqui reprocesar y guardar en cache
-- muchos selects, se guarda el resultado en una tabla temporal @1
create table @1 ()
insert into @1 select * from xxxxxxx

-- borro datos del cache para ese usuario
DELETE FROM CACHE WHERE user_id = @user_id

-- meto en el cache los datos nuevos para ese usuario
INSERT INTO CACHE
SELECT *, @user_id, CONVERT(date, @diaFinD), @cuando FROM @t1

SELECT * FROM @t1
END