UberProf new feature: Query Plan Cache Misuse

time to read 2 min | 344 words

image This is a new feature available for NHibernate Profiler*, Linq to SQL Profiler and Entity Profiler. Basically, it detects when the same query is executed with different parameter sizes, which generate different query plan in the query cache.

Let us say that we issue two queries, to find users by name. (Note that I am using a syntax that will show you the size of the parameters, to demonstrate the problem).

We can do this using the following queries.

exec sp_executesql 
      N'SELECT * FROM Users WHERE Username = @username',
      N'@username nvarchar(3)',
exec sp_executesql 
      N'SELECT * FROM Users WHERE Username = @username',
      N'@username nvarchar(4)',

This sort of code result in two query plans stored in the database query cache, because of the different parameter sizes. In fact, if we assume that the Username column has a length of 16, this single query may take up 16 places in the query cache.

Worse, if you have two parameters whose size change, such as username (length 16) and password (length 16), you may take up to 256 places in the query cache. Obviously, if you use more parameters, or if their length is higher, the number of places that a single query can take in the query cache goes up rapidly.

This can cause performance problems as the database need to keep track of more query plans (uses more memory) may need evict query plans from the cache, which would result in having to rebuild the query plan (increase server load and query time).

* Please note that detecting this in NHibernate requires the trunk version of NHibernate. And it is pretty useless there, since on the trunk, NHibernate will never generate this issue.