Un cache de données avec une table In-Memory MSSQL

Secteur d'activité

Assurance
Contexte client

100 conseillers clients utilisent des widgets qui interrogent une vue SQL. Cette vue effectue de nombreux calculs et consomme beaucoup de CPU. Rapidement le CPU de la base de données atteint 100%.
Enjeux client

La charge CPU sur la base étant telle qu'elle risque de bloquer les autres application. En fin de journée, la requête ne réponds quasiment plus.
Contexte technologique
- SGBD : MS SQL Server 2016 EE
Délais

2 jours
Résultat

Une fois la procédure stockée compilée nativement utilisée en lieu et place de la requête sur la vue SQL, le CPU de la base de données retombe à 20%. La procédure renvois les données en moins de 1 ms au lieu de 10s.
C'est 10000 fois plus rapide !

Pourquoi un cache de données MSSQL ?

Dans le fonctionnement initial, chaque conseiller client dispose d’un widget sur son écran qui renvoi des indicateurs. De plus, ces indicateurs sont calculés dans la base de données grâce à une vue SQL complexe employant des CTE et des hints. Le widget se contente de faire un SELECT des champs sur la vue. Au démarrage, les tables sous-jacentes à la vue n’ont que peu de données et la vue se calcule rapidement. Plus les données augmentent et plus la vue est lente. Le widget est programmé pour demander de nouvelles informations toutes les 10s. Il n’y a pas de cache applicatif partagé de se fait on se retrouve vite avec 600 requêtes/minute sur la vue. Le CPU de la base de données commence alors à chauffer puis sature à tel point que les ennuis commencent.
Pour résoudre ce problème, nous prenons l’initiative de proposer une solution de cache de données dans MSSQL Server. Ceci permettra de « calculer » les données en passant par la vue seulement 6 fois par minute et non 600 fois. Ainsi, les données seront en mémoire et sans verrouillage donc très rapide à accéder. Au final, Il y aura toujours 600 demandes minutes mais sur des données pré-calculées.

Les principes du cache MSSQL dans notre cas d’usage :

Le cache est alimenté par une procédure stockée dédiée au rafraîchissement.
Cette procédure utilisera la vue existante qui est déjà testée et ne nécessite donc pas une nouvelle recette fonctionnelle.
Les données seront stockées dans une table in-memory pour plus de vélocité et moins de problème de verrous.
La table in-memory sera stockée dans une base de données séparée (nous verrons pourquoi plus loin).
Une procédure compilée nativement aura la charge de renvoyer les données du cache (de la table in-memory). Un simple select sur la table in-memory suffira. L’isolation level a snapshot permettra d’éviter tout problème de verrouillage
Un job MSSQL agent aura pour tache de lancer le rafraîchissement du cache (ie : de table in-memory) à une fréquence donnée.

Les éléments du cache

La database :

CREATE DATABASE [RELATIONS_CLIENT_IM]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'RELATIONS_CLIENT_IM', FILENAME = N'C:\DB_CRITICAL\DB\MUTUD\RELATIONS_CLIENT_IM.mdf' , SIZE = 65536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),
FILEGROUP [RELCLI_IM] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
( NAME = N'RELATIONS_CLIENT_IM_FS', FILENAME = N'C:\DB_CRITICAL\DB\MUTUD\RELATIONS_CLIENT_IM_FS' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'RELATIONS_CLIENT_IM_log', FILENAME = N'C:\DB_CRITICAL\DB\MUTUD\RELATIONS_CLIENT_IM_log.ldf' , SIZE = 65536KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

La table in-memory :

CREATE TABLE [dbo].[VSTORE_EtatDesAgents_Plateaux]
(
[userid] [VARCHAR](100) NULL,
[userName] [VARCHAR](100) NULL,
[status_name] [VARCHAR](103) NULL,
[departement] [VARCHAR](250) NULL,
[duration] [INT] NULL,
[auxReason] [INT] NULL,
[mediaType] [VARCHAR](10) NULL,
INDEX [VSTORE_EtatDesAgents_Plateaux_index] NONCLUSTERED
(
[userName] ASC,
[status_name] ASC,
[departement] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

La procédure de rafraîchissement du cache :

CREATE PROCEDURE [dbo].[sp_load_VSTORE_EtatDesAgents_Plateaux]
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT userid,
userName,
CASE
WHEN na_reason IS NULL THEN
status_name
ELSE
na_reason
END status_name,
departement,
duration,
LEN(na_reason) AS auxReason,
mediaType
INTO #VSTORE_EtatDesAgents_Plateaux
FROM [RELATIONS_CLIENTS].[dbo].[V_EtatDesAgents_Plateaux]
ORDER BY status_name,
departement,
userName
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
BEGIN TRAN
DELETE [dbo].[VSTORE_EtatDesAgents_Plateaux];
INSERT INTO [dbo].[VSTORE_EtatDesAgents_Plateaux] SELECT * FROM #VSTORE_EtatDesAgents_Plateaux;
COMMIT TRAN
END
GO