Avoid plan cache bloat for same query with different comments

user_13695234 0
Viewed 193

SQL Server 2017 with forced parametrization on.

Our appserver connects to the db with one login, so in the SQL Profiler we do not see the real user's (GUI user) name. To see that information on the SQL server level I defined an interceptor in NHibernate that puts a comment with the user's name at the beginning of each query.

That solves the problem but creates another one: SQL Server considers the same query from different users to be different even though it is parameterized properly and the only difference is the comment. Multiplying every plan times 150 bloats the plan cache.

Question: How can I make SQL Server consider those commented queries to be the same in regards to the hash for plan cache?

The forced parametrization option is already active because of some legacy app that needs it.

Putting the comment on the end of the query did not work either. Before I start experimenting with other solutions like creating a batch an putting the user info and the actual query in 2 different statements, maybe you have some suggestion how to format the comment to avoid the problem. The batch hack would work if the profiler is already connected but not show the user name in "active expensive queries" of the activity monitor or similar tools.

NHibernate specific tricks that solve the original problem some other way would be great, too.

Thanks in advance for any hints!

Edit 2020-06-15: The only way I see is to use the batch with 2 statements. The first would upsert in a small table that maps sessionid to current user name.

nhibernate sql-server
1 Answers

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.

user_11783140
Related