Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

SQL Server : Pourquoi ma requête est lente en production, mais pas quand je l’exécute dans Management Studio ?

Derrière cette question se cache un des plus gros problèmes d'optimisation de requêtes. Il faut savoir que le moteur de base de données est adaptif et qu'il sait s'adapter aux ressources disponibles et aux volumes de données présent dans les tables. C'est d'ailleurs bénéfique dans la majorité des cas… Qui souhaiterait utiliser 8 processeurs pour exécuter une requête sur un serveur utilisant déjà 100% des CPU ?

Le problème du phénomène d'écrit plus haut n'est généralement pas là. En effet même l'activité du serveur est calme et que la même requête avec les mêmes paramètres est exécutée avec quelques secondes d'intervalle entre votre application et Management Studio le comportement change. En fait une application ado.net et Management Studio ne partage pas les même paramètres de connexion ce qui explique l'origine de ce problème.

On retrouve ces fameux paramètres de connexion avec :

select * from sys.dm_exec_sessions

Les colonnes (entre autres) :

  • quoted_identifier
  • arithabort
  • ansi_null_dflt_on
  • ansi_defaults
  • ansi_warnings
  • ansi_padding
  • ...

Sont considérées comme telles…

SQL Server va alors mémoriser certains de ces paramètres avec le plan d'exécution de la requête. De fait, le plan ne sera réutilisé que si les paramètres sont les mêmes. La requête suivante sera déclinée en 2 plans d'exécutions différents :

SET ANSI_NULLS ON

 

SELECT * FROM MaTable WHERE MaCol = NULL

 

SET ANSI_NULLS OFF

 

SELECT * FROM MaTable WHERE MaCol = NULL

Ici le problème sera visible si vous avez une grande quantité d'enregistrement ayant NULL stockée dans la colonne MaCol et un index présent sur cette même colonne. Les plans seront alors très différent l'un de l'autre.

La problématique est double. D'une part les paramètres de connexion sont différents entre les 2 applications (Ado.Net et Management Studio) et d'autre part la requête de part sa complexité peut être déclinée en plusieurs plan d'exécution différent.

La solution à ce problème sera supprimer le plan fautif (celui qui est le plus lent des 2), en vidant par exemple le cache de procédure (DBCC FREEPROCACHE) mais cette solution n'est que temporaire, le problème reviendra tôt ou tard.

La solution durable est de signaler à SQL Server que la requête sur lequel nous planchons risque de générer des plans d'exécution différent en fonction du contexte et de tout simplement lui sire de régénérer un nouveau plan à chaque exécution :

SELECT * FROM MaTable WHERE MaCol = NULL

OPTION ( RECOMPILE )

Ou dans le cas d'une procédure stockée on ajoutera WITH RECOMPILE à sa définition.

Bonne résolution...

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié vendredi 3 avril 2009 09:00 par christian

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01