• Articles
• admin

Récemment, nous avons travaillé sur les problèmes de performances de SQL server pour certains de nos grands clients qui exécutent SQL server comme base de données. Dans cet article, je vais résumer ce que j’ai appris du processus en décrivant certaines étapes que nous pouvons suivre pour résoudre les problèmes de performance.

Vérifiez la configuration de SQL Server

Assurez-vous que le serveur de base de données est configuré avec suffisamment de ressources, telles que le nombre de cœurs de processeur et la quantité de mémoire.

Pour vérifier la configuration du serveur, vous pouvez ouvrir la vue « Informations système »:

Pour vérifier les paramètres de mémoire SQL Server,

1. Démarrez SQL Server Management Studio.
2. Faites un clic droit sur votre instance de base de données et sélectionnez « Propriétés ».
3. Cliquez sur le tableau « Mémoire » dans la fenêtre contextuelle « Propriétés du serveur ».
4. Vérifiez les paramètres de mémoire.

Assurez-vous Que le mode Instantané Est Activé

Assurez-vous que le mode instantané est activé pour la base de données. Afin d’empêcher le verrouillage de SQL Server, l’indicateur de mode instantané doit être activé. Exécutez la requête suivante pour vérifier si l’indicateur est activé:

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<database_name>'

Si la requête renvoie ‘1’, le mode instantané est déjà activé. Sinon, exécutez la requête suivante pour l’activer.

ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

LIÉS: Définition et implémentation des exigences Lignes de base

Vérifiez les index de base de données

Vérifiez les index de base de données pour vous assurer qu’il n’y a pas d’index manquants. Exécutez la requête suivante pour répertorier tous les index de la base de données (crédit : tsql-List of all index & colonnes d’index dans SQL Server DB–Stack Overflow):

SELECT t.name TableName, col.name ColumnName, ind.name IndexNameFROM sys.indexes indINNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_idINNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_idINNER JOIN sys.tables t ON ind.object_id = t.object_idWHERE (ind.is_primary_key = 0 AND t.is_ms_shipped = 0)ORDER BY t.name, col.name, ind.name

Enregistrez les résultats de la requête dans un fichier texte avec des colonnes délimitées par des onglets afin qu’ils puissent être importés ultérieurement dans une feuille de calcul.

Évitez la fragmentation

Assurez-vous que les index de la base de données ne sont pas fragmentés. Les index de la base de données aident à accélérer la requête de base de données. Mais lorsqu’elles sont fragmentées, les requêtes peuvent fonctionner très lentement. Dans le cadre de la maintenance de la base de données, les index doivent être réorganisés ou reconstruits régulièrement.

Exécutez la requête suivante pour vérifier le pourcentage de fragmentation de tous les index de la base de données (crédit : Comment vérifier la fragmentation des index sur les index d’une base de données):

SELECT dbschemas.Jama Software AS 'Schema', dbtables.Jama Software AS 'Table', dbindexes.Jama Software AS 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, dbindexes.fill_factorFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstatsINNER JOIN sys.tables dbtables ON dbtables. = indexstats.INNER JOIN sys.schemas dbschemas ON dbtables. = dbschemas.INNER JOIN sys.indexes AS dbindexes ON dbindexes. = indexstats. AND indexstats.index_id = dbindexes.index_idWHERE indexstats.database_id = DB_ID()ORDER BY indexstats.avg_fragmentation_in_percent DESC

Voici un exemple de résultat de requête pour une base de données avec des index très fragmentés:

Pour les index avec un nombre de pages supérieur à 1000, le « avg_framentation_in_percent » doit être maintenu sous 10%. Cela n’a pas beaucoup d’importance pour les petits index. En règle générale, les indices avec des pourcentages de fragmentation compris entre 5% et 30% pourraient être réorganisés, et les indices avec des pourcentages de fragmentation supérieurs à 30% devraient être reconstruits.

Pour réorganiser un index unique:

ALTER INDEX REORGANIZE

Pour reconstruire un seul index:

ALTER INDEX REBUILD WITH (ONLINE = ON)

Vous pouvez également réorganiser ou reconstruire un index à l’aide de SQL Server Management Studio en cliquant avec le bouton droit de la souris sur l’index que vous souhaitez reconstruire ou réorganiser.

S’il y a beaucoup d’index défragmentés dans la base de données, vous pouvez exécuter la requête suivante pour les reconstruire tous (crédit: SQL SERVER –2008 -2005 – Reconstruire Tous les Index de Toutes les tables de la base de données – Reconstruire l’index avec FillFactor). Veuillez noter que la requête peut s’exécuter pendant un certain temps pour une grande base de données. Il est recommandé d’exécuter la requête pendant que la base de données est inactive ou hors ligne.

DECLARE @TableName VARCHAR(255)DECLARE @sql NVARCHAR(500)DECLARE TableCursor CURSOR FORSELECT OBJECT_SCHEMA_NAME()+'.'+name AS TableNameFROM sys.tablesOPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGINSET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'PRINT @sqlEXEC (@sql)FETCH NEXT FROM TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursorGO

Envisagez de configurer la tâche de maintenance dans SQL Server studio pour exécuter régulièrement la réorganisation de l’index de la base de données. Bien que la reconstruction des index dans SQL Server devrait probablement se faire hors ligne ou lorsque le système est inactif, la réorganisation des index pourrait se faire en ligne. Voici un bon article sur ce sujet: Reconstruire ou réorganiser: Maintenance de l’index SQL Server.

Pour configurer un plan de réorganisation d’index dans SQL Server, cliquez avec le bouton droit sur « Gestion », puis sur « Plans de maintenance » et sélectionnez « Nouveau Plan de maintenance » ou « Assistant de Plan de maintenance ». Suivez les instructions pour créer un plan.

CONNEXES : Options de gestion des versions dans Jama Connect

Exécuter le rapport d’index manquant

SQL Server fournit un outil qui analyse les activités de la base de données et recommande des index supplémentaires pouvant aider aux performances des requêtes. Le rapport pourrait nous donner quelques idées sur la lenteur de certaines requêtes.

Pour générer le rapport, exécutez la requête suivante après que l’instance de base de données a été utilisée pendant un certain temps (crédit: Ne créez pas aveuglément ces index « manquants »!):

SELECT d., s = OBJECT_SCHEMA_NAME(d.), o = OBJECT_NAME(d.), d.equality_columns, d.inequality_columns, d.included_columns, s.unique_compiles, s.user_seeks, s.last_user_seek, s.user_scans, s.last_user_scan, s.avg_total_user_cost, s.avg_user_impactFROM sys.dm_db_missing_index_details AS dINNER JOIN sys.dm_db_missing_index_groups AS gON d.index_handle = g.index_handleINNER JOIN sys.dm_db_missing_index_group_stats AS sON g.index_group_handle = s.group_handleWHERE d.database_id = DB_ID() AND OBJECTPROPERTY(d., 'IsMsShipped') = 0

Lisez l’article Trouver des index manquants pour comprendre la sortie de cette requête.

Surveiller les sessions de base de données

Utilisez le Moniteur d’activité pour surveiller les sessions de base de données. Microsoft SQL Server Management Studio est livré avec un moniteur d’activité, qui peut être utilisé pour surveiller les sessions de base de données.

Recherchez les sessions bloquées par d’autres sessions pendant une longue période. Faites un clic droit sur la session et sélectionnez « Détails » pour afficher les détails de la requête associée au processus.

Utilisez Windows Resource Monitor

Windows Resource Monitor peut être utilisé pour surveiller l’utilisation de la mémoire et du processeur du processus SQL Server afin de s’assurer qu’il y a suffisamment de mémoire dans le système et que le processeur n’est pas saturé. Veuillez noter qu’il existe un problème connu pour SQL Server 2008 où la figure de mémoire affichée pour le processus SQL Server dans Resource Monitor n’est pas correcte.

Identifier les requêtes lentes

Si nous avons déterminé que les performances ne sont pas liées aux ressources, l’étape suivante consiste à identifier les requêtes lentes qui conduisent à de mauvaises performances.

La requête suivante renvoie les 100 requêtes les plus lentes qui s’exécutent pendant plus de 300 ms (crédit : Requêtes de longue durée):

SELECT st.text, qp.query_plan, qs.*FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC) AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qpWHERE qs.max_worker_time > 300 OR qs.max_elapsed_time > 300

Voici une capture d’écran de quelques exemples de résultats:

Une fois que nous avons rassemblé une liste de requêtes lentes, nous pouvons les exécuter directement sur la base de données et examiner les plans d’exécution pour comprendre ces requêtes.

Autres outils

Voici une liste d’autres outils pouvant être utilisés pour dépanner les applications SQL Server et Java:

• Profileur SQL Server : peut être utilisé pour identifier les requêtes lentes. Vous pouvez le démarrer à partir de SQL Server Management Studio
• VisualVM: peut être utilisé pour surveiller l’utilisation des ressources Java et effectuer des vidages de threads
• Contrôle de mission Java / Enregistrement de vol: surveiller et enregistrer les événements système
• JProfiler: identifier les appels de service ou de base de données lents (développement, non pris en charge par 8.x déploiement)
• New Relic : peut surveiller les performances de la base de données et enregistrer des requêtes lentes

Listes de contrôle

Voici une liste d’informations pouvant être collectées pour résoudre les problèmes de SQL Server:

1. Configuration du serveur de base de données (nombre de cœurs de processeur, mémoire physique, espace disque, version Windows Server, version SQL Server, Paramètres de mémoire SQL Server)
2. Configuration du serveur d’applications (nombre de cœurs de PROCESSEUR, mémoire physique, espace disque, paramètres de mémoire Java core)
3. Statistiques d’index de base de données
4. Statistiques de fragmentation de base de données
5. Rapport d’index manquant SQL server
6. Statistiques de la mémoire et du processeur du serveur de base de données lorsque le serveur est lent
7. Opérations qui ralentissent le système
8. Rapport de requête lente SQL server
9. Statistiques de table de base de données
10. Quelques vidages de threads d’applications Java effectués pendant les opérations lentes, le cas échéant

Conclusion

Le débogage des problèmes de performances de SQL Server n’est pas toujours simple. Mais avec les bons outils et la patience, nous devrions être en mesure de comprendre la cause de ces problèmes. J’espère que les informations contenues dans cet article aident à cela.