Éviter le gonflement du cache du plan pour une même requête avec des commentaires différents.

Question

SQL Server 2017 avec paramétrage forcé activé.

Notre appserver se connecte à la base de données avec un seul login, donc dans le SQL Profiler nous ne voyons pas le nom de l'utilisateur réel (utilisateur GUI). Pour voir cette information au niveau du serveur SQL, j'ai défini un intercepteur dans NHibernate qui met un commentaire avec le nom de l'utilisateur au début de chaque requête.

Cela résout le problème mais en crée un autre : SQL Server considère que la même requête provenant de différents utilisateurs est différente même s'il est paramétré correctement et que le la seule différence est le commentaire . Multiplier chaque plan par 150 fait gonfler le cache des plans.

Question : Comment puis-je faire en sorte que SQL Server considère ces requêtes commentées comme étant les mêmes en ce qui concerne le hachage pour le cache de plan ?

L'option de paramétrage forcé est déjà active en raison d'une ancienne application qui en a besoin.

Mettre le commentaire à la fin de la requête n'a pas fonctionné non plus. Avant de commencer à expérimenter d'autres solutions, comme la création d'un lot et l'insertion des informations sur l'utilisateur et de la requête proprement dite dans deux déclarations différentes, vous avez peut-être des suggestions sur la manière de formater le commentaire pour éviter le problème. Le batch hack fonctionnerait si le profiler est déjà connecté mais ne montrerait pas le nom de l'utilisateur dans "active expensive queries" du moniteur d'activité ou d'autres outils similaires.

Les astuces spécifiques à NHibernate qui permettent de résoudre le problème original d'une autre manière seraient également très appréciées.

Merci d'avance pour tout conseil !

Edit 2020-06-15 : Le seul moyen que je vois est d'utiliser le lot avec 2 déclarations. Le premier serait upsert dans une petite table qui fait correspondre sessionid au nom d'utilisateur actuel.

Answer 1

De la documentation :

L'instruction Transact-SQL est qualifiée d'existante si elle correspond littéralement à une instruction Transact-SQL exécutée précédemment avec un plan mis en cache, caractère par caractère.

Les commentaires font partie de la requête. Il n'y a aucun moyen de faire en sorte que des requêtes différant uniquement par des commentaires utilisent le même plan de requête.

Vous pouvez atteindre votre objectif en transmettant vos informations supplémentaires d'une autre manière, par exemple en mettant votre nom d'utilisateur dans la connexion. Application Name propriété, comme aquí .

Le nom de l'application peut être défini de manière programmatique avant d'ouvrir la connexion, mais cela réduira également la réutilisation des connexions du pool. Cette propriété Application Name est visible dans le SQL Profiler. Je l'ai déjà utilisée pour distinguer les applications, ce qui est d'ailleurs son utilisation prévue.

Pour autant que je sache, la propriété du nom de l'application ne fait pas partie de la clé du cache du plan de requête. Par conséquent, le fait d'avoir des noms d'application différents ne devrait pas provoquer de ratés dans le cache du plan de requête. Mais je ne l'ai pas encore vérifié.