Meilleures pratiques SQL Server, partie I: configuration – Serveur d’impression

Suis-je le seul à trouver que les guides de bonnes pratiques de Microsoft SQL Server sont un peu pénibles à parcourir? D'une manière ou d'une autre, j'en doute. Après avoir été frustré de lire de nombreux guides techniques, guides de bonnes pratiques, articles TechNet et articles de blog rédigés par des experts SQL, j'ai pensé qu'il serait utile de compiler un simple article sur les meilleures pratiques SQL Server.

Le but de cet article n'est pas de se plonger dans les paramètres du serveur SQL en profondeur, mais plutôt de parcourir certaines des choses que vous devez examiner lors de l'architecture ou du dépannage des problèmes de performances du serveur SQL.

Instance partagée vs instance dédiée

Si une application possède un grand nombre de schémas / procédures stockées, cela peut potentiellement avoir un impact sur d'autres applications qui partagent la même instance SQL. Les ressources d'instance pourraient potentiellement être divisées / verrouillées, ce qui entraînerait à son tour des problèmes de performances pour toutes les autres applications avec des bases de données hébergées sur l'instance SQL partagée.

Le dépannage des problèmes de performances peut être pénible, car vous devez déterminer quelle instance est la cause première, ce qui pourrait ne pas être si facile.

Cette question est généralement mise en balance avec les coûts du système d'exploitation et des licences SQL. Si les performances de l'application sont primordiales, une instance dédiée est fortement recommandée.

Microsoft accorde une licence au serveur SQL au niveau du serveur par cœur et non par instance. Pour cette raison, les administrateurs sont tentés d'installer autant d'instances de serveur SQL que le serveur peut en gérer, pour économiser sur les coûts de licence SQL, ce qui peut entraîner des problèmes de performances majeurs en cours de route.

Choisissez des instances SQL dédiées autant que possible.

Séparez les fichiers SQL sur différents disques

SQL Server accède aux données et aux fichiers journaux avec des modèles d'E / S très différents. L'accès au fichier de données est principalement aléatoire tandis que l'accès au fichier journal des transactions est séquentiel. Le stockage sur disque en rotation nécessite le repositionnement de la tête de disque pour un accès aléatoire en lecture et en écriture. Les données séquentielles sont donc plus efficaces que l'accès aléatoire aux données. La séparation des fichiers qui ont des modèles d'accès différents aide à minimiser les mouvements de tête de disque et optimise ainsi les performances de stockage.

Utilisez RAID 10 pour les fichiers binaires utilisateur, les données, les fichiers journaux et TempDB pour des performances et une disponibilité optimales.

Dimensionnement TempDB

Gonflez de manière proactive les fichiers TempDB à leur taille maximale pour éviter la fragmentation du disque.

Des conflits de pages peuvent se produire sur les pages GAM, SGAM ou PFS lorsque SQL doit écrire sur des pages système spéciales pour allouer de nouveaux objets. Les loquets protègent (verrouillent) ces pages en mémoire. Sur un serveur SQL occupé, il peut prendre beaucoup de temps pour obtenir un verrou sur une page système dans tempdb. Cela se traduit par des temps d'exécution des requêtes plus lents et est connu sous le nom de conflit de verrou.

Une bonne règle de base pour créer des fichiers de données tempdb:

• Pour <= 8 cœurs
  • Fichiers de données Tempdb = # de cœurs
• Pour> 8 cœurs

À partir de SQL Server 2016, le nombre de cœurs de processeur visibles par le système d'exploitation est automatiquement détecté lors de l'installation et, en fonction de ce nombre, SQL calcule et configure le nombre de fichiers Tempdb requis pour des performances optimales. La configuration automatique des fichiers tempdb en fonction du nombre de cœurs CPU disponibles est un grand pas en avant et donc bravo à Microsoft pour avoir introduit cette nouvelle fonctionnalité géniale great

Une autre chose qui mérite d'être examinée par rapport à tempdb est l'indicateur de trace 1118 (extensions complètes uniquement)

Microsoft KB2154845 indique que l'indicateur de trace 1118 peut aider à réduire les conflits d'allocation dans tempdb. L'indicateur de trace 1118 indique à SQL Server d'éviter les «extensions mixtes» et d'utiliser les «extensions complètes». https://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx

Avec l'indicateur de trace 1118 activé, chaque objet nouvellement alloué dans chaque La base de données sur l'instance obtient ses propres 64 Ko de données privés. L'impact est plus important dans tempdb où la plupart des objets sont créés.

Configuration de la mémoire

• mémoire minimale du serveur
• mémoire maximale du serveur
• max threads de travail
• index créer de la mémoire
• mémoire minimum par requête

Mémoire minimale du serveur

L'option de mémoire minimale du serveur définit la quantité minimale de mémoire dont l'instance SQL dispose. Étant donné que SQL est un porc de mémoire qui mâche tout ce que la RAM lui réserve, il est peu probable que vous rencontriez cela à moins que le système d'exploitation sous-jacent ne demande trop de mémoire au serveur SQL. Les technologies de virtualisation mettent en jeu ce paramètre.

Mémoire max du serveur

L'option max server memory définit la quantité maximale de mémoire que l'instance SQL peut utiliser. Il est généralement utilisé s'il existe plusieurs applications exécutées en même temps que SQL et que vous souhaitez garantir que ces applications disposent de suffisamment de mémoire pour fonctionner correctement.

Certaines applications n'utiliseront que la mémoire disponible au démarrage et n'en demanderont pas davantage même si nécessaire. C'est là que le paramètre de mémoire maximale du serveur entre en jeu.

Sur un cluster / batterie SQL par exemple, plusieurs instances SQL peuvent être en concurrence pour les ressources. La définition d'une limite de mémoire pour chaque instance SQL afin que les différentes instances SQL ne la suppriment pas sur la RAM garantira les meilleures performances.

N'oubliez pas de laisser au moins 4 à 6 Go de RAM au système d'exploitation pour éviter les problèmes de performances.

Nombre maximum de threads de travail

L'option max worker threads permet d'optimiser les performances lorsqu'un grand nombre de clients sont connectés au serveur SQL. Normalement, un thread de système d'exploitation distinct est créé pour chaque demande de requête. Si des centaines de connexions simultanées sont établies avec SQL, un thread par demande de requête consommerait de grandes quantités de ressources système. L'option max threads de travail permet d'améliorer les performances en permettant à SQL de créer un pool de threads de travail pour traiter un plus grand nombre de requêtes de requête.

La valeur par défaut est 0, ce qui permet à SQL de configurer automatiquement le nombre de threads de travail au démarrage. Cela fonctionne pour la plupart des systèmes. Le nombre maximal de threads de travail est une option avancée et ne doit donc pas être modifié sans l'autorisation d'un DBA expérimenté.

Quand dois-je configurer SQL pour utiliser plus de threads de travail? Si la longueur moyenne de la file d'attente de travail pour chaque planificateur est supérieure à 1, vous pouvez bénéficier de l'ajout de plus de threads au système, mais uniquement si la charge n'est pas liée au processeur ou subit d'autres attentes importantes. Si l'une de ces choses se produit, l'ajout de fils de discussion n'aiderait pas car ils finiraient par attendre aussi.

Index Créer de la mémoire

L'option de création de mémoire d'index est une autre option avancée qui ne devrait généralement pas être touchée. Il contrôle la quantité maximale de RAM initialement allouée pour créer des index. La valeur par défaut de cette option est 0, ce qui signifie qu'elle est gérée automatiquement par SQL Server. Cependant, si vous rencontrez des difficultés pour créer des index, envisagez d'augmenter la valeur de cette option.

Mémoire minimale par requête

Lorsqu'une requête est exécutée, SQL essaie d'allouer la quantité optimale de mémoire pour qu'elle s'exécute efficacement. Par défaut, le paramètre de mémoire minimale par requête alloue> = 1024 Ko pour chaque requête à exécuter. La meilleure pratique consiste à laisser ce paramètre à la valeur par défaut de 0, pour permettre à SQL de gérer dynamiquement la quantité de mémoire allouée pour les opérations de création d'index. Si toutefois le serveur SQL a plus de RAM qu'il n'en faut pour fonctionner efficacement, les performances de certaines requêtes pourraient être améliorées si vous augmentez ce paramètre. Tant qu'il y a de la mémoire disponible sur le serveur, qui n'est pas utilisée par SQL, aucune autre application ou le système d'exploitation, le renforcement de ce paramètre peut améliorer les performances globales du serveur SQL. Mais s'il n'y a pas gratuit mémoire disponible, l'augmentation de ce paramètre nuirait probablement aux performances globales plutôt que de l'aider.

Configuration CPU

Hyper-Threading

Hyper-Threading est l'implémentation propriétaire d'Intel Simultaneous Multithreading (SMT) qui améliore la parallélisation des calculs (multitâche) effectués sur des microprocesseurs x86. Le matériel qui utilise l'hyper-threading permet aux CPU hyper-thread logiques d'apparaître comme CPU physiques au système d'exploitation. SQL voit alors les CPU physiques que le système d'exploitation présente et peut donc utiliser les processeurs hyper-threadés.

La mise en garde ici est que chaque version de SQL Server a ses propres limites sur la puissance de calcul qu'elle peut utiliser.

https://msdn.microsoft.com/en-us/library/ms143760.aspx

NUMA (accès mémoire non uniforme)

NUMA est une méthode d'optimisation d'accès à la mémoire qui permet d'augmenter la vitesse du processeur sans augmenter la charge sur le bus du processeur. Si NUMA est configuré sur le serveur sur lequel SQL sera installé, vous n'avez pas à vous inquiéter car SQL est compatible avec NUMA et fonctionne bien sur le matériel NUMA sans aucune configuration spéciale.

Affinité du processeur

Il est peu probable que vous ayez à modifier les valeurs par défaut d'affinité du processeur, sauf si vous rencontrez des problèmes de performances, mais il vaut toujours la peine de comprendre ce qu'ils sont et comment ils fonctionnent.

SQL prend en charge l'affinité du processeur au moyen de deux options:

• Masque d'affinité CPU
• Masque d'E / S d'affinité

SQL utilise tous les CPU disponibles à partir du système d'exploitation. Il crée des ordonnanceurs sur tous les processeurs pour tirer le meilleur parti des ressources pour une charge de travail donnée. Lors du multitâche, le système d'exploitation ou d'autres applications sur le serveur SQL peut basculer les threads de processus d'un processeur à un autre. SQL est une application gourmande en ressources et les performances peuvent donc être affectées lorsque cela se produit. Pour minimiser, nous pouvons configurer les processeurs de manière à ce que toute la charge SQL soit dirigée vers un groupe de processeurs présélectionné. Ceci est réalisé en utilisant le masque d'affinité CPU.

L'option de masque d'E / S d'affinité lie les E / S de disque SQL à un sous-ensemble de CPU. Dans les environnements de traitement transactionnel en ligne SQL (OLTP), cette extension peut améliorer les performances des threads SQL émettant des opérations d'E / S.

Remarque: l'affinité matérielle pour les disques individuels ou les contrôleurs de disque n'est pas prise en charge.

Degré maximal de parallélisme (MAXDOP)

Par défaut, SQL utilise tous les processeurs disponibles lors de l'exécution des requêtes. Bien que cela soit idéal pour les requêtes volumineuses, cela peut entraîner des problèmes de performances et limiter la simultanéité. La configuration MAXDOP dépend de la machine du serveur SQL: un ordinateur SMP (Symmetric Multiprocessing), un ordinateur NUMA (Non-Uniform Memory Access) ou des processeurs hyperthreading.

Utilisez les instructions suivantes de Microsoft lorsque vous configurez la valeur MAXDOP (SQL2005 +):

Consultez ce guide des meilleures pratiques MS pour plus de détails: https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

Coût maximal du parallélisme

La valeur par défaut est définie sur 5. Le chiffre du seuil de coût est utilisé par l'optimiseur lors de l'évaluation de plans à plusieurs threads. 5 est un paramètre très bas qui ne convient qu'aux applications purement OLTP.

Remarque: DatAdvantage est une application OLAP, pas une application OLTP.

Pour les systèmes non OLTP, je recommande de commencer avec ce paramètre à 50 environ et de régler vers le haut ou vers le bas selon le cas. Assurez-vous de mesurer les requêtes critiques dans votre application et d'ajuster si nécessaire.

Quelques autres paramètres méritent d'être mentionnés

Initialisation instantanée des fichiers

Bien que techniquement une autorisation Windows, l'octroi de l'autorisation «Effectuer des tâches de maintenance de volume» à SQL lui donne un coup de pouce quand vient le temps de développer des fichiers de données.

Par défaut, Windows écrit un tas de zéros chaque fois qu'un utilisateur demande de l'espace. Si je crée un fichier de 1 Mo, Windows écrira 1 Mo de zéros sur le disque pour initialiser correctement le fichier. Accorder à SQL ces autorisations signifie que, lors de la demande d'espace pour les fichiers de données, SQL indique à Windows de marquer l'espace comme utilisé et de le remettre immédiatement à SQL, ce qui accélère la croissance des fichiers de données.

Compression de sauvegarde

À partir de SQL Server 2008r2, une case à cocher active la compression de sauvegarde. Les sauvegardes sont plus petites, prennent moins de temps et les restaurations prennent même moins de temps. Ce paramètre est une évidence vraiment!

Connexion administrateur dédié à distance (DAC)

Ce paramètre n'entre vraiment en jeu que pour faciliter le dépannage lorsque SQL est devenu détraqué.

Lorsque vous vous connectez via le DAC, SQL Server fournit une connexion dédiée, un planificateur d'UC et une mémoire. Le dépannage à distance d'une instance SQL indexée à 100% d'utilisation du processeur est beaucoup plus facile lorsque vous avez des ressources dédiées à votre disposition! Vous devez être connecté à SQL physiquement sur la console ou à distance via RDP pour utiliser le DAC distant. Encore une fois, ce paramètre est un peu évident. Réglez-le et oubliez-le!

Conclusion

SQL Server peut fournir les performances et l'évolutivité pour prendre en charge les applications de base de données de production à condition que les meilleures pratiques soient suivies.

J'espère que ce message vous a été utile.

Dans mon prochain article, je passerai en revue certaines des meilleures pratiques concernant SQL Server dans un environnement virtualisé.

Il s'agit d'une série en plusieurs parties sur les meilleures pratiques SQL Server. Lisez la partie II ici.

Sources:

https://msdn.microsoft.com/en-us/library/mt590198(v=sql.1).aspx

https://social.technet.microsoft.com/Forums/sqlserver/en-us/home?category=sqlserver

https://technet.microsoft.com/en-us/library/mt590198(v=sql.1).aspx

https://blogs.technet.microsoft.com/dataplatforminsider/

https://blogs.msdn.microsoft.com/sqlserverstorageengine/

https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

http://blogs.msdn.com/sqlperf/default.aspx

https://blogs.msdn.microsoft.com/sqltips/

https://www.brentozar.com/