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 rapides à 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 :
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
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
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
Procédure de restitution des données du cache :
- CREATE PROCEDURE [dbo].[sp_get_EtatDesAgents_Plateaux]
- WITH NATIVE_COMPILATION, SCHEMABINDING
- AS BEGIN ATOMIC WITH
- (
- TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
- )
- SELECT [userid]
- ,[userName]
- ,[status_name]
- ,[departement]
- ,[duration]
- ,[auxReason]
- ,[mediaType]
- FROM [dbo].[VSTORE_EtatDesAgents_Plateaux]
- END
- GO
Job de l’agent chargé de lancer les rafraichissements :
Résultat :
Après la mise en production est immédiat. En effet le CPU consommé sur le serveur de base de données passe de 100% à 25%
Au final on pourra avoir 1000 voir 10000 télé-conseillés sans problème car la requête tourne en moins d’une milliseconde !