Options de configuration de serveur

S’applique à :SQL ServerAzure SQL Managed Instance

Vous pouvez gérer et optimiser les ressources de SQL Server et d’Azure SQL Managed Instance via des options de configuration, en utilisant SQL Server Management Studio ou la procédure stockée système sp_configure. Les options de configuration de serveur les plus fréquemment utilisées sont accessibles dans SQL Server Management Studio. Toutes les options de configuration sont accessibles avec sp_configure. Avant de paramétrer ces options, vous devez tenir compte de leurs conséquences sur votre système. Pour plus d’informations, consultez Voir ou changer les propriétés de serveur (SQL Server).

Catégories d’options de configuration

Les modifications de l’option de configuration prennent effet uniquement après l’émission de l’instruction RECONFIGURE (ou dans certains cas, RECONFIGURE WITH OVERRIDE). Si vous ne voyez pas l’effet d’une modification de configuration, vérifiez que la valeur d’exécution de l’option de configuration a changé.

Vous pouvez utiliser l’affichage catalogue sys.configurations pour déterminer la valeur de configuration (la colonne value) et la valeur d’exécution (colonne value_in_use) et si l’option de configuration nécessite un redémarrage du moteur de base de données (la colonne is_dynamic).

Si le moteur de base de données doit redémarrer, les options affichent la valeur modifiée uniquement dans la colonne value. Après le redémarrage, la nouvelle valeur apparaît à la fois dans la colonne value et la colonne value_in_use.

Certaines options nécessitent l’arrêt du serveur afin que la nouvelle valeur soit prise en considération. Si vous définissez la nouvelle valeur et que vous exécutez sp_configure avant de redémarrer le serveur, la nouvelle valeur apparaît dans la colonne value de la vue du catalogue sys.configurations, mais pas dans la colonne value_in_use. Quand vous redémarrez le serveur, la nouvelle valeur apparaît dans la colonne value_in_use.

La reconfiguration de certaines options invalide les plans dans le cache du plan, à l’origine de la compilation de nouveaux plans. Pour en savoir plus, consultez la rubrique DBCC FREEPROCCACHE.

Les options à configuration automatique sont celles que SQL Server ajuste en fonction des besoins du système. Dans la plupart des cas, il est inutile de définir les valeurs manuellement. Les exemples incluent l’option threads de travail maximum et l’option user connections.

La requête suivante peut être utilisée pour déterminer si des valeurs configurées ont été configurées, mais ne sont pas en vigueur :

SELECT *
FROM sys.configurations
WHERE [value] <> [value_in_use];

Si la valeur est la modification de l’option de configuration que vous avez effectuée, mais que value_in_use n’est pas identique, la commande RECONFIGURE n’a pas été exécutée ou a échoué, ou le moteur de base de données doit être redémarré.

Il y a deux options de configuration où value et value_in_use peuvent ne pas être les mêmes, ce qui est le comportement attendu :

• mémoire maximale du serveur (Mo) : la valeur configurée par défaut 0 s’affiche comme 2147483647 dans la colonne value_in_use.

• mémoire minimale du serveur (Mo) : la valeur configurée par défaut de 0 peut s’afficher comme 8 sur les systèmes 32 bits, ou 16 sur les systèmes 64 bits, dans la colonne value_in_use. Dans certains cas, si value_in_use s’affiche comme 0, la véritable valeur de value_in_use est 8 (32 bits) ou 16 (64 bits).

La colonne is_dynamic peut être utilisée pour déterminer si l’option de configuration nécessite un redémarrage. Une valeur de 1 dans la colonne is_dynamic signifie que, lorsque la commande RECONFIGURE est exécutée, la nouvelle valeur prend effet immédiatement. Dans certains cas, le moteur de base de données peut ne pas évaluer la nouvelle valeur immédiatement, mais le fait au cours normal de son exécution. Une valeur de 0 dans la colonne is_dynamic signifie que la valeur de configuration modifiée ne prend pas effet avant le redémarrage du moteur de base de données, même si la commande RECONFIGURE a été exécutée.

Pour une option de configuration qui n’est pas dynamique, il n’existe aucun moyen de savoir si la commande RECONFIGURE a été exécutée pour appliquer la modification de la configuration. Avant de redémarrer le moteur de base de données pour appliquer la modification de configuration, exécutez la commande RECONFIGURE pour vous assurer que toutes les modifications de configuration prennent effet lorsque le moteur de base de données redémarre.

Options de configuration

Le tableau suivant répertorie toutes les options de configuration disponibles, leurs plages de valeurs possibles, leurs valeurs par défaut, ainsi que les produits pris en charge (SQL Server ou Azure SQL Managed Instance). Les options de configuration sont signalées par des codes sous forme de lettres, comme suit :

• A = Options avancées, qui doivent être modifiées uniquement par un professionnel de base de données expérimenté, et qui nécessitent un paramètre show advanced options sur 1.

• RR = Options qui nécessitent un redémarrage du moteur de base de données.

• RP = Options qui nécessitent un redémarrage du moteur PolyBase.

• SC = Options à configuration automatique.

¹ Prend la valeur 1 au démarrage de SQL Server Agent. La valeur par défaut est 0 si SQL Server Agent est défini pour démarrer automatiquement pendant l’installation.

² La valeur zéro (0) configure automatiquement le nombre maximal de threads de travail en fonction du nombre de processeurs logiques. Pour en savoir plus, consultez le nombre maximal de threads de travail automatiquement configuré.

Contenu associé

• sp_configure (Transact-SQL)
• RECONFIGURE (Transact-SQL)
• DBCC FREEPROCCACHE (Transact-SQL)