Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 6,124 | Comments: 45,475

filter by tags archive

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)',
      @username=N'bob'
exec sp_executesql 
      N'SELECT * FROM Users WHERE Username = @username',
      N'@username nvarchar(4)',
      @username=N'john'

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.


Comments

rumburak

Ok, so how to avoid variable length with varchars? Pad them with spaces or what?

Frans Bouma

Isn't this due to a bug in EF and L2S which are solved in 4.0 and L2S for .net 4?

Any sane o/r mapper would simply pass the length of the field to filter in the parameter (IMHO)

(btw, the font in this textbox is really small in FF3.5)

Dmitry

I guess NH has finally been modified to set parameter length even when prepare_sql is false. I modified the source locally to do that a while ago.

Ayende Rahien

Rumburak,

You set the parameter length (which is separate than the value length.

Frans,

Yes, it is refering to this bug.

Dmitry,

Yes.

Dmitry

@Frans,

EF never had that bug.

It has a different issue though. Constant query parameters are not parameterized. This is true in EF4 as well. Does anyone know if it can cause the same execution plan problem?

Ayende Rahien

Dmitry,

The following EF 3.5 code is going to result in this alert:

using (var db = new Entities(conStr))

{

 for (int i = 0; i < 5; i++)

 {

     db.AddToUsers(new Users

                       {

                           Password = new byte[i] 

                       });

 }

 db.SaveChanges();

}

Ayende Rahien

Dmitry,

In other words, EF certainly have this issue.

Dmitry

I did not realize it has this issue with binary data. I was talking about strings/decimals.

Ayende Rahien

Dmitry,

This result in the same alert as well:

using (var db = new Entities(conStr))

{

for (int i = 0; i < 5; i++)

{

    db.AddToUsers(new Users

    {

        Username = new string('a', i)

    });

}

db.SaveChanges();

}

Jarod

In EF, isnt this solved by compiling the query?

Ayende Rahien

Jarod,

Note that I am doing INSERTs here, not queries, I don't think that you can compile that.

Jarod

Ya, not for inserts.

I was referring to this part of the post:

=========================

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)',

      @username=N'bob'

exec sp_executesql 

      N'SELECT * FROM Users WHERE Username = @username',

      N'@username nvarchar(4)',

      @username=N'john'

This sort of code result in two query plans stored in the database query cache, because of the different parameter sizes.

Ayende Rahien

Jarod,

That is just an example, to show the problem, not talking about those specific queries

Dmitry

Oren,

Does your profiler actually checks execution plans? I doubt a query like

INSERT INTO (field1, ... , @fieldN) VALUES (@value1, ... , @valueN)

would have the execution plan cached.

Ayende Rahien

Dmitry,

Why wouldn't it be cached?

tobi

I wonder if you can make NHibernate not parameterize the value of constant parameters. This should be a performance improvement as the optimizer has more information. It also does not pollute the cache to much because at most as many slots are filled than you have distinct queries in your code.

Ayende Rahien

tobi,

NHibernate already does that when you put the constant as part of the query, yes.

gunteman

I think the wording is a bit confusing..

"Different parameter sizes result in higher query plan cache usage".

If anything, the cache usage is reduced. The cache size increases, though.

Maybe "Different parameter sizes result in reduced use of cached query plans"?

Ayende Rahien

Gunteman,

It is an issue of semantics, since we have more cache entries, for what is basically the same query.

gunteman

Yeah, I know. It just sounded a bit wrong to me. High cache usage is something good, in my book.

Maybe "higher " can be substituted for "inefficient", to remove any doubts

Ayende Rahien

Thanks, I updated the documentations

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

  1. RavenDB 3.5 whirl wind tour: You want all the data, you can’t handle all the data - one day from now
  2. The design of RavenDB 4.0: Making Lucene reliable - about one day from now
  3. RavenDB 3.5 whirl wind tour: I’ll find who is taking my I/O bandwidth and they SHALL pay - 3 days from now
  4. The design of RavenDB 4.0: Physically segregating collections - 4 days from now
  5. RavenDB 3.5 Whirlwind tour: I need to be free to explore my data - 5 days from now

And 14 more posts are pending...

There are posts all the way to May 30, 2016

RECENT SERIES

  1. RavenDB 3.5 whirl wind tour (14):
    29 Apr 2016 - A large cluster goes into a bar and order N^2 drinks
  2. The design of RavenDB 4.0 (13):
    28 Apr 2016 - The implications of the blittable format
  3. Tasks for the new comer (2):
    15 Apr 2016 - Quartz.NET with RavenDB
  4. Code through the looking glass (5):
    18 Mar 2016 - And a linear search to rule them
  5. Find the bug (8):
    29 Feb 2016 - When you can't rely on your own identity
View all series

RECENT COMMENTS

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats