Pour fidéliser ses utilisateurs, toute application ou site Web doit fonctionner rapidement. Pour les environnements critiques, un délai de quelques millisecondes pour obtenir les informations peut créer de gros problèmes. Au fur et à mesure que la taille des bases de données augmente de jour en jour, nous devons récupérer les données le plus rapidement possible et les réécrire dans la base de données le plus rapidement 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 vais décrire 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 serveur SQL (SQL Server, pour faire court).

# 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 pousser des données aussi rapidement et 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 de matériel 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 correct 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 requête 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.

Tableau comparatif des logiciels de gestion de projet

Les statistiques d'attente peuvent être calculées en utilisant sys.dm_os_wait_stats Dynamic Management View (DMV), qui donne 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 Scénario 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 aller à l'excellent Documentation Microsoft .

Prenons un exemple où nous avons trop de PAGEIOLATCH_XX. Cela signifie qu'un thread attend des lectures de pages de données depuis le 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 une mémoire insuffisante, 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 voir pourquoi tant de données sont lues à partir du disque: Quels types de commandes SQL sont à l'origine de cela? Lisons-nous trop de données au lieu de lire moins de données en utilisant des filtres, tels que where clauses? Y a-t-il trop de lectures de données en raison des analyses de table ou des analyses 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 mal comprises par SQL Optimizer (un autre programme dans 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 minutieusement avant d'entreprendre toute action. 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 Recherche de requêtes problématiques

Comme mentionné ci-dessus, la première chose que nous pouvons faire est de rechercher des 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 faire attention aux 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.

didacticiel de l'API Web de base asp.net

# 3 Requêtes de réglage fin

La mise au point 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 tous 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 des requêtes T-SQL appropriées), l'optimiseur SQL ne pourra pas faire son travail.

SQL Server utilise la recherche avancée et algorithmes de tri . Si nous maîtrisons les algorithmes de recherche et de tri, la plupart du temps, nous pouvons deviner pourquoi SQL Server prend des mesures particulières. Le meilleur livre pour en savoir plus et comprendre ces algorithmes est L'art de la programmation informatique par 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 élémentaire, je peux expliquer les choses dont nous devons tenir compte.

• Nous devons d'abord savoir quels opérateurs prennent le plus de coûts de 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 table, mais comme pour tout programme complexe, il n'y a pas de solution fixe. Par exemple, si la table est petite, les analyses 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 le Documentation Microsoft , afin que nous puissions mieux les comprendre et prendre les mesures appropriées.

En relation: 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 peut ê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é:

qu'est-ce qu'un chercheur ux

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ètres. 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 recompiler 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 name colonne et la moitié de la table ont la valeur sri et quelques lignes ont pal dans le name colonne. 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 l'analyse d'index car seules quelques lignes ont pal valeur.

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 attentivement avant de décider. 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
• Lorsque nous mélangeons DDL et DML dans une seule 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.

utilisations du langage de programmation c

Le serveur SQL nous fournit dm_db_index_usage_stats DMV pour trouver des 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 du tout utilisés, ou rarement utilisés, nous pouvons les supprimer pour gagner en performances.

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 lecteur, 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 spécialistes du matériel et des réseaux 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 les clients aussi bien que possible. Pour ce faire, nous devons maintenir des bases de données séparées (si possible, sur des machines distinctes) pour les environnements suivants:

terminal bloomberg pour les investisseurs individuels

• Production
• Développement
• Essai
• Analytique

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

Test sur un 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 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 nous pouvons nous le permettre, 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. Nous avons besoin de garder 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 faire.

Pour les environnements critiques, un délai d'une milliseconde pour obtenir les informations peut être un facteur décisif. Tweet

Conclusion:

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

En relation: Guide de migration d'Oracle vers SQL Server et SQL Server vers Oracle