Avoid plan cache bloat for same query with different comments

Question

I use SQL Server 2017 with forced parametrization on. My app connects to the database with one login; in the SQL Profiler I do not see the GUI user's name so I defined an interceptor in NHibernate that puts a comment with the user's name at the beginning of each query.

But now SQL Server considers the same query from different users to be different even though it is parameterized and the only difference is the comment. This bloats the cache. How can I make SQL Server consider those commented queries to be the same in regards to the hash for the plan's cache?

The forced parametrization option is active because some legacy app needs it. Putting the comment on the end of the query did not work. Before I experiment with creating a batch and putting the user info and the query in 2 different statements, maybe there is a way to format the comment to avoid the problem.

The batch hack would work if the profiler is already connected but not shown the user name in "active expensive queries" of the activity monitor or similar tools. NHibernate specific tricks that solve the problem would be great too.

Answer 1

From the documentation:

The Transact-SQL statement qualifies as existing if it literally matches a previously executed Transact-SQL statement with a cached plan, character per character.

Comments are part of the query. There is no way to get queries differing only by comments to use the same query plan.

You may achieve your goal by transmitting your additional information through some other way, like putting your user name in the connection Application Name property, like here.

The Application Name can be set programmatically before opening the connection, but this will also reduce connection reuse from the pool. This Application Name property is visible in the SQL Profiler. I have already used it to distinguish applications, which is its intended usage indeed.

As far as I know the application name property is not part of the query plan cache key, so having different application names should not cause query plan cache misses. But I have not actually checked this.