Optimiser le stockage des bases de données

Effectué

Pour optimiser le stockage des bases de données, vous devez prendre en compte le remplissage proportionnel et la configuration de tempdb.

Comprendre les performances des E/S

Les performances d’e/s peuvent être essentielles pour une application de base de données. Azure SQL élimine le placement physique des fichiers, mais il existe des méthodes pour vous assurer que vous disposez des performances d’E/S dont vous avez besoin.

Les opérations d’entrée/sortie par seconde (IOPS) peuvent être importantes pour votre application. Vérifiez que vous avez choisi le bon niveau de service et les vCores appropriés pour vos besoins d’IOPS. Découvrez comment mesurer les IOPS de vos requêtes locales si vous migrez vers Azure. Si vous avez des restrictions sur les IOPS, vous pouvez voir des attentes d’E/S longues. Dans le modèle d’achat des vCores, vous pouvez faire un scale-up des vCores, ou passer au modèle Critique pour l’entreprise ou Hyperscale si vous n’avez pas assez d’IOPS. Pour les charges de travail de production, en cas d’utilisation de DTU, nous vous recommandons de passer au niveau Premium.

La latence d’e/s est un autre composant clé pour les performances d’e/s. Pour une latence d’e/s plus rapide pour les Azure SQL Database, envisagez Critique pour l’entreprise ou Hyperscale. Pour augmenter la latence d’E/S pour SQL Managed Instance, passez à Critique pour l’entreprise ou augmentez la taille de fichier ou le nombre de fichiers de la base de données. L’amélioration de la latence du journal des transactions peut nécessiter l’utilisation de transactions multi-instructions.

Fichiers et groupes de fichiers

Les professionnels de SQL Server utilisent souvent des fichiers et des groupes de fichiers pour améliorer les performances d’e/s via l’emplacement physique des fichiers. Azure SQL n’autorise pas les utilisateurs à placer des fichiers sur des systèmes de disque spécifiques. Toutefois, Azure SQL a des engagements en matière de ressources pour les performances d’E/S concernant les taux, les E/S par seconde et les latences. De cette façon, le fait d’interdire à l’utilisateur de placer physiquement des fichiers peut être un avantage.

Azure SQL Database n’a qu’un seul fichier de base de données (Hyperscale en possède généralement plusieurs), et la taille maximale est configurée par le biais des interfaces Azure. Il n’existe aucune fonctionnalité permettant de créer d’autres fichiers.

Azure SQL Managed Instance prend en charge l’ajout de fichiers de base de données et la configuration des tailles, mais pas le placement physique des fichiers. Vous pouvez utiliser le nombre de fichiers et les tailles de fichier de SQL Managed Instance pour améliorer les performances d’E/S. En outre, les groupes de fichiers définis par l’utilisateur sont pris en charge pour SQL Managed Instance dans le but de faciliter la gestion.

Décrire le remplissage proportionnel

Lorsque vous insérez 1 gigaoctet de données dans une base de données SQL Server avec deux fichiers de données, vous pouvez vous attendre à ce que chaque fichier augmente d’environ 512 mégaoctets. Toutefois, ce n’est pas toujours le cas. SQL Server distribue les données en fonction de la taille de chaque fichier. Par exemple, si les deux fichiers de données sont de 2 gigaoctets, les données sont distribuées uniformément. Mais si un fichier est de 10 gigaoctets et que l’autre est de 1 gigaoctets, environ 900 Mo entrent dans le fichier le plus grand et 100 Mo dans le plus petit. Ce comportement est courant dans n’importe quelle base de données, mais dans la base de données tempdb nécessitant beaucoup d’écriture, un modèle d’écriture inégale peut créer un goulot d’étranglement dans le plus grand fichier, car il gère davantage d’écritures.

Configurer Tempdb dans SQL Server

SQL Server détecte le nombre de processeurs disponibles pendant l’installation et configure le nombre approprié de fichiers, jusqu’à huit, avec même le dimensionnement. En outre, les comportements des indicateurs de trace 1117 et 1118 sont intégrés au moteur de base de données, mais uniquement pour tempdb. Pour les charges de travail lourdes tempdb, il peut être utile d’augmenter le nombre de fichiers tempdb au-delà de huit, correspondant au nombre de processeurs sur votre ordinateur.

Vous utilisez tempdb de la même façon dans SQL Server et Azure SQL. Notez toutefois que votre capacité à configurer tempdb est différente, notamment en ce qui concerne le placement des fichiers, le nombre et la taille des fichiers ainsi que les options de configuration de tempdb.

SQL Server utilise tempdb pour différentes tâches au-delà du stockage de tables temporaires définies par l’utilisateur. Il est utilisé pour les tables de travail qui stockent les résultats de requête intermédiaires, les opérations de tri et le magasin de versions pour le contrôle de version des lignes, entre autres fins. En raison de cette utilisation étendue, il est essentiel de placer tempdb sur le stockage à latence la plus faible disponible et de configurer correctement ses fichiers de données.

Les fichiers de base de données de tempdb sont toujours automatiquement stockés sur des disques SSD locaux, ce qui évite les problèmes de performances d’E/S.

Les professionnels de SQL Server utilisent souvent plusieurs fichiers de base de données pour partitionner les allocations des tables de tempdb. Pour Azure SQL Database, le nombre de fichiers est mis à l’échelle avec le nombre de vCores (par exemple, deux vCores égaux à quatre fichiers) avec un maximum de 16. Le nombre de fichiers n’est pas configurable en utilisant T-SQL sur tempdb, mais vous pouvez le faire en changeant l’option de déploiement. La taille maximale de tempdb est mise à l’échelle conformément au nombre de vCores. Vous recevez 12 fichiers avec SQL Managed Instance, indépendamment du nombre de vCores.

L’option MIXED_PAGE_ALLOCATION de base de données est définie sur OFF et AUTOGROW_ALL_FILES est définie sur ON. Vous ne pouvez pas le configurer, mais, comme pour SQL Server, ce sont les valeurs par défaut recommandées.

La fonctionnalité d’optimisation des métadonnées de tempdb introduite dans SQL Server 2019, qui peut réduire la forte contention de verrous, n’est pas disponible actuellement dans Azure SQL Database ou Azure SQL Managed Instance.

Configuration de la base de données

En règle générale, vous configurez une base de données avec les instructions et ALTER DATABASE T-SQLALTER DATABASE SCOPED CONFIGURATION. La plupart des options de configuration pour les performances sont disponibles pour Azure SQL. Consultez la référence T-SQL ALTER DATABASE et ALTER DATABASE SCOPED CONFIGURATION pour connaître les différences entre SQL Server, Azure SQL Database et Azure SQL Managed Instance.

Dans Azure SQL Database, le modèle de récupération par défaut est une récupération complète, ce qui garantit que votre base de données peut respecter les contrats de niveau de service Azure (SLA). Cela signifie que la journalisation minimale pour les opérations en bloc n’est pas prise en charge, sauf si tempdbla journalisation minimale est autorisée.

Configuration MAXDOP

Le degré maximal de parallélisme (MAXDOP) peut affecter les performances des requêtes individuelles. SQL Server et azure SQL handle MAXDOP de la même façon. Lorsqu’une MAXDOP valeur est plus élevée, plus de threads parallèles sont utilisés par requête, ce qui accélère potentiellement l’exécution de la requête. Toutefois, ce parallélisme accru nécessite des ressources de mémoire supplémentaires, ce qui peut entraîner une sollicitation de la mémoire et affecter les performances de stockage. Par exemple, lors de la compression de rowgroups dans un columnstore, le parallélisme nécessite davantage de mémoire, ce qui peut entraîner une pression de la mémoire et un découpage de rowgroup.

À l’inverse, la définition de MAXDOP sur une valeur inférieure peut réduire la pression de la mémoire, ce qui permet au système de stockage d’effectuer plus efficacement. Cela est important dans les environnements avec des ressources de mémoire limitées ou des demandes de stockage élevées. En configurant soigneusement MAXDOP, vous pouvez équilibrer les performances des requêtes et l’efficacité du stockage, ce qui garantit une utilisation optimale des ressources de processeur et de stockage.

Vous pouvez configurer MAXDOP dans Azure SQL à l’aide des techniques suivantes (comme dans SQL Server) :

• ALTER DATABASE SCOPED CONFIGURATION pour configurer MAXDOP est pris en charge pour Azure SQL.
• La procédure stockée pour « degré maximal de parallélisme sp_configure » est prise en charge pour SQL Managed Instance.
• MAXDOP les indicateurs de requête sont entièrement pris en charge.
• La configuration MAXDOP avec Resource Governor est prise en charge pour SQL Managed Instance.