logo
logo
  • Accueil
  • Conseils & expertises
    • Architecture
    • Datascience
    • Audit de performance
    • Dataviz
    • Databases
    • Intégration de données
  • Formations
  • Références
  • Qui sommes nous ?
  • Blog
  • Carrière
  • Contact

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

  • icone secteur activite

    Secteur d'activité

    Assurance
  • icone contexte client

    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%.
  • icone enjeux client

    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.
  • icone contexte technologique

    Contexte technologique

    • SGBD : MS SQL Server 2016 EE
  • icone délais

    Délais

    2 jours
  • icone résultat

    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 :

  1. Le cache est alimenté par une procédure stockée dédiée au rafraîchissement.
  2. Cette procédure utilisera la vue existante qui est déjà testée et ne nécessite donc pas une nouvelle recette fonctionnelle.
  3. Les données seront stockées dans une table in-memory pour plus de vélocité et moins de problème de verrous.
  4. La table in-memory sera stockée dans une base de données séparée (nous verrons pourquoi plus loin).
  5. 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
  6. 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 :

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

Table in-memory :

  1. CREATE TABLE [dbo].[VSTORE_EtatDesAgents_Plateaux]
  2. (
  3. [userid] [VARCHAR](100)  NULL,
  4. [userName] [VARCHAR](100)  NULL,
  5. [status_name] [VARCHAR](103)  NULL,
  6. [departement] [VARCHAR](250)  NULL,
  7. [duration] [INT] NULL,
  8. [auxReason] [INT] NULL,
  9. [mediaType] [VARCHAR](10)  NULL,
  10.  
  11. INDEX [VSTORE_EtatDesAgents_Plateaux_index] NONCLUSTERED
  12. (
  13. [userName] ASC,
  14. [status_name] ASC,
  15. [departement] ASC
  16. )
  17. )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
  18. GO

Procédure de rafraîchissement du cache :

  1. CREATE PROCEDURE [dbo].[sp_load_VSTORE_EtatDesAgents_Plateaux]
  2. AS
  3. BEGIN
  4.  
  5. SET NOCOUNT ON;
  6.  
  7. SELECT DISTINCT userid,
  8. userName,
  9. CASE
  10. WHEN na_reason IS NULL THEN
  11. status_name
  12. ELSE
  13. na_reason
  14. END status_name,
  15. departement,
  16. duration,
  17. LEN(na_reason) AS auxReason,
  18. mediaType
  19. INTO #VSTORE_EtatDesAgents_Plateaux
  20. FROM [RELATIONS_CLIENTS].[dbo].[V_EtatDesAgents_Plateaux]
  21. ORDER BY status_name,
  22. departement,
  23. userName
  24. OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
  25.  
  26. BEGIN TRAN
  27.  
  28. DELETE [dbo].[VSTORE_EtatDesAgents_Plateaux];
  29. INSERT INTO [dbo].[VSTORE_EtatDesAgents_Plateaux] SELECT * FROM #VSTORE_EtatDesAgents_Plateaux;
  30.  
  31. COMMIT TRAN
  32. END
  33. GO

Procédure de restitution des données du cache :

  1. CREATE PROCEDURE [dbo].[sp_get_EtatDesAgents_Plateaux]
  2. WITH NATIVE_COMPILATION, SCHEMABINDING
  3. AS BEGIN ATOMIC WITH
  4. (
  5. TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
  6. )
  7.  
  8. SELECT [userid]
  9. ,[userName]
  10. ,[status_name]
  11. ,[departement]
  12. ,[duration]
  13. ,[auxReason]
  14. ,[mediaType]
  15. FROM [dbo].[VSTORE_EtatDesAgents_Plateaux]
  16.  
  17. END
  18. 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%

MSSQL Cache Results on cpu usage

 

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 !

  • Optimiser le rendement chaufferie en usine
  • Architecture Tableau server cluster sur le cloud AWS

EXPERTISES

  • Architecture IT
  • Audit de performance IT
  • Datascience & IA
  • Dataviz
  • Intégration de données
  • Databases
  • FORMATIONS
  • ETUDES DE CAS
  • QUI SOMMES NOUS ?
  • BLOG
  • CARRIERE
  • CONTACT

Suivez-nous

  • @2performance
  • @2performance
  • aetperf.github.io

@ARCHITECTURE & PERFORMANCE     Mentions légales

Crée par