Partager via

Planifier votre adoption des fonctionnalités OLTP en mémoire dans SQL Server

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Cet article décrit la manière dont l'adoption des fonctionnalités en mémoire dans SQL Server affecte les autres aspects de votre système métier.

Remarque

A. Adoption des fonctionnalités OLTP en mémoire

Les sous-sections suivantes décrivent les facteurs que vous devez prendre en compte lorsque vous envisagez d’adopter et d’implémenter des fonctionnalités en mémoire.

A.1 Prérequis

L’un des prérequis pour utiliser les fonctionnalités en mémoire peut concerner l’édition ou le niveau de service du produit SQL. Pour plus d’informations sur les prérequis, consultez :

A.2 Prévoir la quantité de mémoire active

Votre système dispose-t-il d’assez de mémoire active pour prendre en charge une nouvelle table optimisée en mémoire ?

Microsoft SQL Server

Une table à mémoire optimisée qui contient 200 Go de données nécessite plus de 200 Go de mémoire active dédiée pour sa prise en charge. Avant d’implémenter une table optimisée en mémoire contenant une grande quantité de données, vous devez prévoir la quantité de mémoire active supplémentaire que vous devrez peut-être ajouter à votre serveur. Pour obtenir des conseils sur l’estimation, consultez :

Des conseils similaires sont disponibles pour Azure SQL Managed Instance :

Azure SQL Database

Dans le cas d’une base de données hébergée dans le service cloud Azure SQL Database, le niveau de service que vous choisissez a un impact sur la quantité de mémoire active que votre base de données est autorisée à consommer. Vous devez prévoir de surveiller l’utilisation de la mémoire de votre base de données à l’aide d’une alerte. Pour plus d’informations, consultez :

Variables de table optimisées en mémoire

Une variable de table déclarée comme étant à mémoire optimisée est parfois préférable à une #TempTable traditionnelle située dans la base de données tempdb. Les variables de table peuvent permettre une amélioration des performances, sans utiliser d'importantes quantités de mémoire active.

A.3 La table doit être mise hors connexion pour être convertie en table optimisée en mémoire

Certaines fonctionnalités ALTER TABLE sont disponibles pour les tables optimisées en mémoire. Toutefois, vous ne pouvez pas émettre une instruction ALTER TABLE pour convertir une table basée sur disque en une table optimisée en mémoire. Au lieu de cela, vous devez effectuer manuellement une série d’étapes. Voici différentes méthodes permettant de convertir une table basée sur disque en table optimisée en mémoire.

Scripts manuels

L’une des méthodes de conversion d’une table basée sur disque en table optimisée en mémoire consiste à coder vous-même les étapes nécessaires de Transact-SQL.

  1. Suspendez l’activité d’application.

  2. Effectuez une sauvegarde complète.

  3. Renommez la table basée sur disque.

  4. Émettez une instruction CREATE TABLE pour créer votre table optimisée en mémoire.

  5. Utilisez INSERT INTO dans votre table optimisée en mémoire avec un sous-SELECT de la table basée sur disque.

  6. Supprimez (DROP) votre table basée sur disque.

  7. Effectuez une autre sauvegarde complète.

  8. Reprenez l’activité d’application.

Conseil d’optimisation par mémoire

L’outil Conseiller d’optimisation de la mémoire peut générer un script permettant d’implémenter la conversion d’une table basée sur disque en une table optimisée en mémoire. Cet outil est installé avec SQL Server Data Tools (SSDT).

Fichier .dacpac

Vous pouvez mettre à jour votre base de données sur place à l’aide d’un fichier .dacpac géré par SSDT. Dans SSDT, vous pouvez spécifier les modifications apportées au schéma qui est encodé dans le fichier .dacpac.

Vous utilisez des fichiers .dacpac dans le contexte d’un projet Visual Studio de type Database.

A.4 Déterminer si les fonctionnalités OLTP en mémoire sont adaptées à votre application

Pour savoir si les fonctionnalités OLTP en mémoire peuvent améliorer les performances de votre application, consultez :

B. Fonctionnalités non prises en charge

Les fonctionnalités qui ne sont pas prises en charge dans certains scénarios OLTP en mémoire sont décrites dans :

Les sous-sections suivantes abordent certaines des fonctionnalités non prises en charge les plus importantes.

B.1 Capture instantanée d’une base de données

Lorsqu’une table optimisée en mémoire ou un module est créé pour la première fois dans une base de données, aucun SNAPSHOT (capture instantanée) de la base de données ne pourra plus être créé. Voici pourquoi :

  • Le premier élément optimisé en mémoire rend impossible la suppression du dernier fichier du FILEGROUP optimisé en mémoire.
  • Aucune base de données qui dispose d’un fichier dans un FILEGROUP optimisé en mémoire ne peut prendre en charge un SNAPSHOT (capture instantanée).

En général, une capture instantanée est utile pour les itérations de test rapides.

B.2 Requêtes de bases de données croisées

Les tables optimisées en mémoire ne prennent pas en charge les transactions entre bases de données . Vous ne pouvez pas accéder à une autre base de données à partir de la même transaction ou de la même requête qui accède également à une table mémoire optimisée.

Les variables de table ne sont pas transactionnelles. Par conséquent, les variables de tables optimisées en mémoire peuvent être utilisées dans les requêtes de bases de données croisées.

B.3 Indicateur de table READPAST

Aucune requête ne peut appliquer l’indicateur de table READPAST à toutes les tables optimisées en mémoire.

L’indicateur READPAST est utile lorsque plusieurs sessions accèdent à un même ensemble de lignes et le modifient, comme dans une file d’attente de traitement.

B.4 RowVersion, Sequence

  • Aucune colonne ne peut être marquée pour RowVersion dans une table optimisée en mémoire.

  • SEQUENCE ne peut pas être utilisé avec une contrainte dans une table à mémoire optimisée. Par exemple, vous ne pouvez pas créer une contrainte DEFAULT avec une clause NEXT VALUE FOR. Vous pouvez utiliser plusieurs SEQUENCE avec des instructions INSERT et UPDATE.

C. Maintenance administrative

Cette section décrit les différences qui existent au niveau de l’administration de base de données lorsque des tables optimisées en mémoire sont utilisées.

C.1 Réinitialisation de la valeur initiale de la propriété Identity, incrément > 1

Pour réattribuer une valeur à la colonne IDENTITY,DBCC CHECKIDENTne peut pas être utilisé dans une table optimisée en mémoire.

La valeur d’incrément est limitée à exactement 1 pour une colonne IDENTITY dans une table optimisée en mémoire.

C.2 DBCC CHECKDB ne peut pas valider les tables optimisées en mémoire

La commande DBCC CHECKDB n’a aucun effet lorsque la cible est une table optimisée en mémoire. Les étapes suivantes permettent de contourner ce problème :

  1. Sauvegardez le journal des transactions.

  2. Sauvegardez les fichiers dans le FILEGROUP optimisé en mémoire pour un système Null. Le processus de sauvegarde appelle une validation de somme de contrôle.

    Si l’altération est identifiée, passez aux étapes suivantes.

  3. Pour le stockage temporaire, copiez les données de vos tables optimisées en mémoire dans des tables basées sur disque.

  4. Restaurez les fichiers du FILEGROUP optimisé en mémoire.

  5. Dans les tables optimisées en mémoire, utilisez INSERT INTO pour les données que vous avez temporairement stockées dans les tables basées sur disque.

  6. Supprimez (DROP) les tables basées sur disque qui ont contenu temporairement les données.

D. Performances

Cette section décrit les situations dans lesquelles les excellentes performances des tables optimisées en mémoire peuvent être freinées.

D.1 Observations relatives aux index

Tous les index d’une table optimisée en mémoire sont créés et gérés par les instructions de table CREATE TABLE et ALTER TABLE. Vous ne pouvez pas cibler une table optimisée en mémoire avec une instruction CREATE INDEX.

L'index non-cluster traditionnel d'arbre B (B-tree) est souvent le choix le plus simple et le plus logique lorsque vous implémentez une table à mémoire optimisée pour la première fois. Plus tard, après avoir vu comment votre application fonctionne, vous pourrez envisager l’utilisation d’un autre type d’index.

Remarque

De manière générale, la documentation SQL Server utilise le terme B-tree en référence aux index. Dans les index rowstore, le moteur de base de données implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux index sur les tables à mémoire optimisée. Pour plus d’informations, consultez le Guide de conception et d’architecture d’index SQL Server et Azure SQL.

Deux types d’index doivent être abordés dans le contexte d’une table optimisée en mémoire : les index de hachage et les index Columnstore.

Pour une présentation des index de tables optimisées en mémoire, consultez :

Index de hachage

Les index de hachage peuvent être les plus rapides pour accéder à une ligne spécifique par le biais de la valeur exacte de sa clé primaire, à l’aide de l’opérateur '='.

  • Les opérateurs inexacts comme '!=', '>' ou 'BETWEEN' nuiront aux performances si vous les utilisez avec un index de hachage.

  • Un index de hachage peut ne pas constituer le meilleur choix si la vitesse de duplication de la valeur de clé est trop élevée.

  • Ne sous-estimez pas le nombre de compartiments dont peut avoir besoin votre index de hachage, afin d’éviter que les compartiments ne contiennent de longues chaînes. Pour plus d’informations, consultez :

Index columnstore non cluster

Les tables optimisées en mémoire fournissent un débit élevé de données transactionnelles d’entreprise dans le paradigme appelé traitement transactionnel en ligne ou OLTP. Les index ColumnStore fournissent un débit élevé d’agrégations et un traitement similaire appelé Analyse. Ces dernières années, la meilleure approche disponible pour répondre aux besoins en matière d’OLTP et d’analyse était d’avoir des tables séparées, ce qui impliquait un déplacement important de données et une certaine duplication de données. Aujourd’hui, une solution hybride plus simple est disponible. Elle consiste à utiliser un index columnstore dans une table optimisée en mémoire.

  • Un index columnstore peut être généré dans une table basée sur disque, même comme index cluster. Toutefois, dans une table optimisée en mémoire, l’index columnstore ne peut pas être mis en cluster.

  • Les colonnes LOB (Large Object) et hors ligne d’une table optimisée en mémoire empêchent la création d’un index columnstore dans la table.

  • Aucune instruction ALTER TABLE ne peut être exécutée dans une table optimisée en mémoire si celle-ci a un index columnstore.

    • Depuis août 2016, Microsoft envisage, à court terme, d’améliorer les performances de recréation d’index columnstore.

D.2 Colonnes LOB et colonnes hors ligne

Les Large Objects (LOB) sont des colonnes de types tels que varchar(max). Le fait d’avoir deux colonnes LOB dans une table optimisée en mémoire ne nuit pas suffisamment aux performances pour poser problème. Toutefois, évitez d’avoir plus de colonnes LOB que le nécessitent vos données. Le même conseil s’applique pour les colonnes hors ligne. Ne définissez pas une colonne comme nvarchar(3072) si varchar(512) suffit.

Pour plus d’informations sur les colonnes LOB et hors ligne, consultez :

E. Limitations des procédures natives

Certains éléments de Transact-SQL ne sont pas pris en charge dans les modules T-SQL compilés en mode natif, y compris les procédures stockées. Pour plus d’informations sur les fonctionnalités prises en charge, consultez :

Pour plus d'informations sur les problèmes liés à la migration de modules Transact-SQL utilisant des fonctionnalités non prises en charge en vue d'une compilation native, consultez :

Outre les restrictions relatives à certains éléments de Transact-SQL, il existe également des restrictions quant aux opérateurs de requête pris en charge dans les modules T-SQL en mode compilation native. En raison de ces limitations, les procédures stockées compilées en mode natif ne conviennent pas aux requêtes analytiques qui traitent de grands jeux de données.

Aucun traitement parallèle dans une procédure native

Le traitement parallèle ne peut pas faire partie d’un plan de requête d’une procédure native. Les procédures natives sont toujours monothread.

Types de jointure

Ni les jointures de hachage ni les jointures de fusion ne peuvent faire partie d'un plan de requête d'une procédure native. Des jointures de boucles imbriquées sont utilisées.

Aucune agrégation de hachage

Lorsque le plan de requête d’une procédure native nécessite une phase d’agrégation, seule l’agrégation de flux est disponible. L’agrégation de hachage n’est pas prise en charge dans un plan de requête de procédure native.

  • L'agrégation de hachage est préférable lorsque les données provenant d'un grand nombre de lignes doivent être regroupées.

F. Conception de l’application : transactions et logique des nouvelles tentatives

Une transaction impliquant une table à mémoire optimisée peut devenir dépendante d'une autre transaction qui implique la même table. Si le nombre de transactions dépendantes atteint la valeur maximale autorisée, toutes les opérations dépendantes échouent.

Dans SQL Server 2016 :

  • La valeur maximale autorisée est de huit transactions dépendantes. Huit correspond également au nombre maximal de transactions dont une transaction peut dépendre.
  • Le numéro de l’erreur est 41839. (Dans SQL Server 2014, le numéro de l’erreur est 41301).

Vous pouvez renforcer vos scripts Transact-SQL par rapport à une possible erreur de transaction en leur ajoutant une logique de nouvelle tentative . La logique de nouvelle tentative peut aider en cas d’appels UPDATE et DELETE fréquents, ou si la table optimisée en mémoire est référencée par une clé étrangère d’une autre table. Pour plus d’informations, consultez :

Contenu connexe