Comment régler Microsoft SQL Server pour les performances

Publié: 2022-03-11

Pour fidéliser ses utilisateurs, toute application ou site Web doit fonctionner rapidement. Pour les environnements critiques, un retard de quelques millisecondes dans l'obtention des informations peut créer de gros problèmes. Comme la taille des bases de données augmente de jour en jour, nous devons récupérer les données aussi vite que possible et réécrire les données dans la base de données aussi vite que possible. Pour nous assurer que toutes les opérations s'exécutent correctement, nous devons régler notre serveur de base de données pour les performances.

Dans cet article, je décrirai une procédure étape par étape pour le réglage des performances de base sur l'un des meilleurs serveurs de base de données du marché : Microsoft SQL Server (SQL Server, en abrégé).

#1 Trouver les coupables

Comme pour tout autre logiciel, nous devons comprendre que SQL Server est un programme informatique complexe. Si nous avons un problème avec lui, nous devons découvrir pourquoi il ne fonctionne pas comme prévu.

À partir de SQL Server, nous devons extraire et transmettre des données aussi rapidement et aussi précisément que possible. S'il y a des problèmes, quelques raisons de base, et les deux premières choses à vérifier, sont :

Les paramètres matériels et d'installation, qui peuvent nécessiter une correction car les besoins de SQL Server sont spécifiques
Si nous avons fourni le code T-SQL correct pour que SQL Server implémente

Même si SQL Server est un logiciel propriétaire, Microsoft a fourni de nombreuses façons de le comprendre et de l'utiliser efficacement.

Si le matériel est OK et que l'installation a été effectuée correctement, mais que SQL Server fonctionne toujours lentement, nous devons d'abord déterminer s'il existe des erreurs liées au logiciel. Pour vérifier ce qui se passe, nous devons observer les performances des différents threads. Ceci est réalisé en calculant les statistiques d'attente de différents threads. Le serveur SQL utilise des threads pour chaque demande d'utilisateur, et le thread n'est rien d'autre qu'un autre programme à l'intérieur de notre programme complexe appelé SQL Server. Il est important de noter que ce thread n'est pas un thread du système d'exploitation sur lequel le serveur SQL est installé ; il est lié au thread SQLOS, qui est un pseudo système d'exploitation pour SQL Server.

Les statistiques d'attente peuvent être calculées à l'aide de la vue de gestion dynamique (DMV) sys.dm_os_wait_stats , qui fournit des informations supplémentaires sur son état actuel. Il existe de nombreux scripts en ligne pour interroger cette vue, mais mon préféré est le script de Paul Randal car il est facile à comprendre et possède tous les paramètres importants pour observer les statistiques d'attente :

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

Lorsque nous exécutons ce script, nous devons nous concentrer sur les premières lignes du résultat car elles sont définies en premier et représentent le type d'attente maximum.

Nous devons comprendre les types d'attente afin de pouvoir prendre les bonnes décisions. Pour en savoir plus sur les différents types d'attente, nous pouvons consulter l'excellente documentation de Microsoft.

Prenons un exemple où nous avons trop de PAGEIOLATCH_XX . Cela signifie qu'un thread attend des lectures de page de données du disque dans le tampon, qui n'est rien d'autre qu'un bloc de mémoire. Nous devons être sûrs de comprendre ce qui se passe. Cela ne signifie pas nécessairement un sous-système d'E/S médiocre ou pas assez de mémoire, et l'augmentation du sous-système d'E/S et de la mémoire résoudra le problème, mais seulement temporairement. Pour trouver une solution permanente, nous devons comprendre pourquoi tant de données sont lues sur le disque : quels types de commandes SQL en sont la cause ? Lisons-nous trop de données au lieu de lire moins de données en utilisant des filtres, tels que les clauses where ? Y a-t-il trop de lectures de données en raison de balayages de table ou d'index ? Pouvons-nous les convertir en recherches d'index en implémentant ou en modifiant des index existants ? Sommes-nous en train d'écrire des requêtes SQL qui sont mal comprises par SQL Optimizer (un autre programme de notre programme serveur SQL) ?

Nous devons penser sous différents angles et utiliser différents cas de test pour trouver des solutions. Chacun des types d'attente ci-dessus nécessite une solution différente. Un administrateur de base de données doit les rechercher de manière approfondie avant de prendre toute mesure. Mais la plupart du temps, trouver des requêtes T-SQL problématiques et les régler résoudra 60 à 70 % des problèmes.

#2 Trouver des requêtes problématiques

Comme mentionné ci-dessus, la première chose que nous pouvons faire est de rechercher les requêtes problématiques. Le code T-SQL suivant trouvera les 20 requêtes les moins performantes :

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

Nous devons être prudents avec les résultats; même si une requête peut avoir une durée d'exécution moyenne maximale, si elle ne s'exécute qu'une seule fois, l'effet total sur le serveur est faible par rapport à une requête qui a une durée d'exécution moyenne moyenne et s'exécute plusieurs fois par jour.

#3 Requêtes de réglage fin

Le réglage fin d'une requête T-SQL est un concept important. La chose fondamentale à comprendre est de savoir dans quelle mesure nous pouvons écrire des requêtes T-SQL et implémenter des index, afin que l'optimiseur SQL puisse trouver un plan optimisé pour faire ce que nous voulions qu'il fasse. Avec chaque nouvelle version de SQL Server, nous obtenons un optimiseur plus sophistiqué qui couvrira nos erreurs d'écriture de requêtes SQL non optimisées, et corrigera également les bogues liés à l'optimiseur précédent. Mais, quelle que soit l'intelligence de l'optimiseur, si nous ne pouvons pas lui dire ce que nous voulons (en écrivant une requête T-SQL appropriée), l'optimiseur SQL ne pourra pas faire son travail.

SQL Server utilise des algorithmes de recherche et de tri avancés. Si nous maîtrisons bien les algorithmes de recherche et de tri, nous pouvons la plupart du temps deviner pourquoi SQL Server prend des mesures particulières. Le meilleur livre pour en savoir plus et comprendre ces algorithmes est The Art of Computer Programming de Donald Knuth.

Lorsque nous examinons des requêtes qui doivent être affinées, nous devons utiliser le plan d'exécution de ces requêtes afin de savoir comment le serveur SQL les interprète.

Je ne peux pas couvrir tous les aspects du plan d'exécution ici, mais à un niveau de base, je peux expliquer les choses que nous devons considérer.

Nous devons d'abord déterminer quels opérateurs prennent en charge la majeure partie du coût de la requête.
Si l'opérateur prend beaucoup de frais, nous devons en connaître la raison. La plupart du temps, les scans coûteront plus cher que les recherches. Nous devons examiner pourquoi une analyse particulière (analyse de table ou analyse d'index) se produit au lieu d'une recherche d'index. Nous pouvons résoudre ce problème en implémentant des index appropriés sur les colonnes de la table, mais comme pour tout programme complexe, il n'y a pas de solution fixe. Par exemple, si la table est petite, les balayages sont plus rapides que les recherches.
Il existe environ 78 opérateurs, qui représentent les différentes actions et décisions du plan d'exécution de SQL Server. Nous devons les étudier en profondeur en consultant la documentation de Microsoft, afin de mieux les comprendre et de prendre les mesures appropriées.

Connexe : Explication des index SQL, Pt. 1

#4 Réutilisation du plan d'exécution

Même si nous implémentons des index appropriés sur les tables et écrivons du bon code T-SQL, si le plan d'exécution n'est pas réutilisé, nous aurons des problèmes de performances. Après avoir affiné les requêtes, nous devons nous assurer que le plan d'exécution pourra être réutilisé si nécessaire. La majeure partie du temps CPU sera consacrée au calcul du plan d'exécution qui peut être éliminé si nous réutilisons le plan.

Nous pouvons utiliser la requête ci-dessous pour savoir combien de fois le plan d'exécution est réutilisé, où usecounts représente le nombre de fois que le plan est réutilisé :

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

La meilleure façon de réutiliser le plan d'exécution consiste à implémenter des procédures stockées paramétrées. Lorsque nous ne sommes pas en mesure d'implémenter des procédures stockées, nous pouvons utiliser sp_executesql , qui peut être utilisé à la place pour exécuter des instructions T-SQL lorsque les seules modifications apportées aux instructions SQL sont des valeurs de paramètre. SQL Server réutilisera très probablement le plan d'exécution qu'il a généré lors de la première exécution.

Encore une fois, comme pour tout programme informatique complexe, il n'y a pas de solution fixe. Parfois, il est préférable de compiler à nouveau le plan.

Examinons les deux exemples de requêtes suivants :

select name from table where name = 'sri';
select name from table where name = 'pal';

Supposons que nous ayons un index non clusterisé sur la colonne de name et que la moitié de la table ait la valeur sri et que quelques lignes aient pal dans la colonne de name . Pour la première requête, SQL Server utilisera l'analyse de table car la moitié de la table a les mêmes valeurs. Mais pour la deuxième requête, il est préférable d'utiliser le parcours d'index car seules quelques lignes ont la valeur pal .

Même si les requêtes sont similaires, le même plan d'exécution peut ne pas être une bonne solution. La plupart du temps, ce sera un cas différent, nous devons donc tout analyser soigneusement avant de prendre une décision. Si nous ne voulons pas réutiliser le plan d'exécution, nous pouvons toujours utiliser l'option « recompiler » dans les procédures stockées.

Gardez à l'esprit que même après avoir utilisé des procédures stockées ou sp_executesql , il arrive que le plan d'exécution ne soit pas réutilisé. Elles sont:

Lorsque les index utilisés par la requête changent ou sont supprimés
Lorsque les statistiques, la structure ou le schéma d'une table utilisée par la requête changent
Lorsque nous utilisons l'option "recompiler"
Lorsqu'il y a un grand nombre d'insertions, de mises à jour ou de suppressions
Quand on mélange DDL et DML dans une même requête

#5 Suppression des index inutiles

Après avoir affiné les requêtes, nous devons vérifier comment les index sont utilisés. La maintenance de l'index nécessite beaucoup de CPU et d'E/S. Chaque fois que nous insérons des données dans une base de données, SQL Server doit également mettre à jour les index, il est donc préférable de les supprimer s'ils ne sont pas utilisés.

Le serveur SQL nous fournit dm_db_index_usage_stats DMV pour trouver les statistiques d'index. Lorsque nous exécutons le code T-SQL ci-dessous, nous obtenons des statistiques d'utilisation pour différents index. Si nous trouvons des index qui ne sont pas utilisés du tout, ou rarement, nous pouvons les supprimer pour gagner en performance.

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 Installation de SQL Server et configuration de la base de données

Lors de la configuration d'une base de données, nous devons conserver les données et les fichiers journaux séparément. La raison principale en est que l'écriture et l'accès aux fichiers de données ne sont pas séquentiels, alors que l'écriture et l'accès aux fichiers journaux sont séquentiels. Si nous les mettons sur le même disque, nous ne pouvons pas les utiliser de manière optimisée.

Lorsque nous achetons un réseau de stockage (SAN), un fournisseur peut nous donner des recommandations sur la façon de le configurer, mais ces informations ne sont pas toujours utiles. Nous devons avoir une discussion détaillée avec nos gars du matériel et du réseau sur la façon de conserver les données et les fichiers journaux séparément et de manière optimisée.

#7 Ne surchargez pas SQL Server

La tâche principale de tout administrateur de base de données est de s'assurer que le serveur de production fonctionne correctement et sert au mieux les clients. Pour ce faire, nous devons maintenir des bases de données distinctes (si possible, sur des machines distinctes) pour les environnements suivants :

Production
Développement
Essai
Analytique

Pour une base de données de production, nous avons besoin d'une base de données avec un mode de récupération complet, et pour les autres bases de données, un mode de récupération simple suffit.

Le test sur une base de données de production mettra beaucoup de charge sur le journal des transactions, les index, le processeur et les E/S. C'est pourquoi nous devons utiliser des bases de données distinctes pour la production, le développement, les tests et l'analyse. Si possible, utilisez des machines distinctes pour chaque base de données, car cela réduira la charge sur le processeur et les E/S.

#8 Journal des transactions, tempdb et mémoire

Le fichier journal doit disposer de suffisamment d'espace libre pour les opérations normales, car une opération de croissance automatique sur un fichier journal prend du temps et peut forcer d'autres opérations à attendre jusqu'à ce qu'elle soit terminée. Pour connaître la taille du fichier journal de chaque base de données et son utilisation, nous pouvons utiliser DBCC SQLPERF(logspace) .

La meilleure façon de configurer tempdb est de le placer sur un disque séparé. Nous devons garder la taille initiale aussi grande que possible, car lorsqu'elle atteint une situation de croissance automatique, les performances diminuent.

Comme mentionné précédemment, nous devons nous assurer que le serveur SQL s'exécute sur une machine distincte, de préférence sans aucune autre application dessus. Nous devons conserver de la mémoire pour le système d'exploitation, plus un peu plus s'il fait partie d'un cluster, donc dans la plupart des cas, environ 2 Go devraient suffire.

Pour les environnements critiques, un retard d'une milliseconde dans l'obtention d'informations peut être un facteur décisif.

Tweeter

Conclusion:

Les procédures et suggestions décrites ici concernent uniquement le réglage des performances de base. Si nous suivons ces étapes, nous pouvons, en moyenne, obtenir une amélioration des performances d'environ 40 à 50 %. Pour effectuer un réglage avancé des performances de SQL Server, nous aurions besoin d'approfondir chacune des étapes décrites ici.

Lectures complémentaires sur le blog Toptal Engineering :

Résoudre les goulots d'étranglement avec les index SQL et les partitions
Guide de migration Oracle vers SQL Server et SQL Server vers Oracle