2

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.

Gigiwig
  • 31
  • 5

1 Answers1

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.

Frédéric
  • 9,364
  • 3
  • 62
  • 112
  • Thank you for that "character by character" doc reference. At least now I know comments are a dead end. Adding a suffix to the connection property Application name is a good idea. I'll try to put that in the NHibernate interceptor and see if it works without side effects. – Gigiwig Jun 08 '20 at 16:57
  • Using the Application Name will not work because the connectnio stirng is fixed and the same for all users. They share one connection pool. I found no way to add something different to the app name for each select. The session context cannot be accessed from outside, i.e. another session. So if I see an expensive query in the activity monitor and have that session id I have no way to read it's context value from SSMS with another session. – Gigiwig Jun 15 '20 at 11:29