Sql Server Executions Plans Still Not Working with prepare_sql = true?

[Daniel Auger]

This came across my twitter stream today:
http://www.objectreference.net/post/NHibernate-and-Execution-Plans.aspx

The blog entry claims that prepare_sql = true really only solves the
problem on a per connetion basis.

Quoting the blog:
"This seemed to fix the parameter size issue but now the queries were
being sent using exec sp_prepexec which even had the DBA’s scratching
their heads over as it’s documented as a system procedure used to
facilitate cursors within SQL Server. The DBAs later informed us that
running queries like this causes the execution plans to only be re-
used per open connection so for every connection it would still have
to create new execution plans."

Here is the old thread on the issue:
http://groups.google.com/group/nhusers/browse_thread/thread/7a0a341184796836/db3cb45cfc8f9a2b?lnk=gst&q=prepare_sql#db3cb45cfc8f9a2b

I'm wondering what's the real deal with it. It seems like this "bug"
is either not confirmed, in a state of limbo, or FUD/not real.

Anyone know? It seems like it sould be a pretty big issue for a high
volume app.

[Fabio Maulo]

NH version?

2009/10/26 Daniel Auger <daniel...@gmail.com>

--
Fabio Maulo

[Craig van Nieuwkerk]

2.0 it says in the article. Could be 2.01, it is not specific.

[Fabio Maulo]

Use 2.1.0 and then let me know...

I don't remember exactly where I have fixed all parameters-size stuff but should be 2.1.0 or in the actual branch (2.1.1 that will be released on Sundays).

2009/10/26 Craig van Nieuwkerk <cra...@gmail.com>

--
Fabio Maulo

[Fabio Maulo]

and... btw...

if MsSQL server has a silly way to define the execution plan, please, create a issue in the MsSQL server issue tracker and not in NH.

Thanks.

2009/10/26 Fabio Maulo <fabio...@gmail.com>

--
Fabio Maulo

[Daniel Auger]

I've sent messages to both the blog poster and the initial reporter to
validate the fix and report back here.

On Oct 26, 6:13 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> Use 2.1.0 and then let me know...
> I don't remember exactly where I have fixed all parameters-size stuff but
> should be 2.1.0 or in the actual branch (2.1.1 that will be released
> on Sundays).
>

> 2009/10/26 Craig van Nieuwkerk <crai...@gmail.com>

>
>
>
>
>
> > 2.0 it says in the article. Could be 2.01, it is not specific.
>

> > On Tue, Oct 27, 2009 at 9:39 AM, Fabio Maulo <fabioma...@gmail.com> wrote:
> > > NH version?
>

> > > 2009/10/26 Daniel Auger <daniel.au...@gmail.com>

[Daniel Auger]

The blog author posted a comment on the blog indicating that the team
was using NHibernate 2.1.0 with MSSQL Server 2005. I will try to test
2.1.1 when time allows.

Fabio, do the fixes you made negate the need for setting prepare_sql =
true? The reason why I ask is that if I'm understanding the blog
correctly, using prepare_sql is the wrong way to solve the problem as
SQL server is going to cause the execution plan per connection
behavior.

[Fabio Maulo]

if so... remember that the Driver, in NHibernate, is an injectable component.

You can copy&paste the default impl. and then change it to do what you want; after that you only need to use the NH's configuration to inject your drive.

2009/10/27 Daniel Auger <daniel...@gmail.com>

--
Fabio Maulo

[Fabio Maulo]

To be more clear

http://twitter.com/fabiomaulo/status/5186045199

2009/10/27 Fabio Maulo <fabio...@gmail.com>

--
Fabio Maulo

[Daniel Auger]

Understood Fabio :)

On Oct 27, 9:55 am, Fabio Maulo <fabioma...@gmail.com> wrote:
> To be more clearhttp://twitter.com/fabiomaulo/status/5186045199
>
> 2009/10/27 Fabio Maulo <fabioma...@gmail.com>

>
>
>
> > if so... remember that the Driver, in NHibernate, is an injectable
> > component.
> > You can copy&paste the default impl. and then change it to do what you
> > want; after that you only need to use the NH's configuration to inject your
> > drive.
>

> > 2009/10/27 Daniel Auger <daniel.au...@gmail.com>

[Rob]

Although I agree with your sentiment from an architectural perspective
(i.e., "ORM is to fill the gap between O and RDMS and not to fix RDBMS
issues"),
as a practical matter (if NH adoption is your goal), providing the
user with a(n easy) way to overcome "issues" is often required by the
thing filling the gap. May not be what you'd like to do, but it's
often necessary.

Rob

On Oct 27, 10:55 am, Fabio Maulo <fabioma...@gmail.com> wrote:
> To be more clearhttp://twitter.com/fabiomaulo/status/5186045199
>
> 2009/10/27 Fabio Maulo <fabioma...@gmail.com>
>
>
>
>
>

> > if so... remember that the Driver, in NHibernate, is an injectable
> > component.
> > You can copy&paste the default impl. and then change it to do what you
> > want; after that you only need to use the NH's configuration to inject your
> > drive.
>

> > 2009/10/27 Daniel Auger <daniel.au...@gmail.com>

> Fabio Maulo- Hide quoted text -
>
> - Show quoted text -

[Fabio Maulo]

for that reason we are giving you the opportunity to define what NH should do to work around a MsSQL issue.

1. Create you own IDriver inheriting from SqlClientDriver

2. then override GenerateCommand with this implementation

{

IDbCommand command = base.GenerateCommand(type, sqlString, parameterTypes);

SetParameterSizes(command.Parameters, parameterTypes);

return command;

}

3. then use the NHibernate configuration

<property name="connection.driver_class">YourApp.Driver.WorkaroundSqlClientDriver</property>

That's all... only 3 steps to go.

2009/10/27 Rob <robsc...@gmail.com>

--
Fabio Maulo

[Fabio Maulo]

and Rob...

my goal is do the right thing in the right place.

We all should learn how send issues to MsSQL team and not only looking for a workaround in NHibernate.

Do you have a link to the issue regarding the query-execution plan in MsSQL ?

do you know why MsSQL need the parameter-size to choose the execution-plan ?

why the others RDBMS does not need it ?

why I can write something so simple as

in FireBird: SELECT FIRST x [SKIP y] rest-of-sql-statement 

in Postgre :  SELECT rest-of-sql-statement LIMIT x [OFFSET y]

in MySQL :  SELECT rest-of-sql-statement LIMIT x [, y]

and in MsSQL I must make me crazy for a simple pagination in the 21th century ?

why I can't write something like this ?

SELECT rest-of-sql-statement WHERE CONTAINS(…, …) = :pShouldContain

Which is the type of return-value of the two functions CONTAINS and FREETEXT ?

Perhaps you can tolerate all these stuff the MsSQL-team are giving us but I'm starting to be a little bit not tolerant.

2009/10/27 Rob <robsc...@gmail.com>

--
Fabio Maulo

[Fabio Maulo]

sorry for the relief... sorry

2009/10/27 Fabio Maulo <fabio...@gmail.com>

--
Fabio Maulo

[Daniel Auger]

Fabio,

I totally understand your frustration with this quirk of SQL Server.
However I tend to agree with Rob. If NHibernate can work around it
without compromising the framework (i.e. the workaround is isolated to
the driver), the workaround should probably be implemented if only to
help adoption. This issue is a glaring problem that a developer or DBA
can point to as a reason to avoid NH. Even though it's easy to inject
a custom driver, it's a hard sell to potential adopters because I
think most people are going to feel queasy about doing so. People just
want things to "work". If you go look around on some of the SQL
forums / blogs, this is an issue used as anti-NH ammo. What's more
practical: implementing the work around, or telling everyone using SQL
Server who cares about performance to create a custom driver? Where
the blame lies is not important to 99% NH users. Also I'd bet money MS
is going to make sure EF won't have this issue.

With all my respect,

- Dan

On Oct 27, 3:22 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> sorry for the relief... sorry
>

> 2009/10/27 Fabio Maulo <fabioma...@gmail.com>

>
>
>
> > and Rob...
> > my goal is do the right thing in the right place.
> > We all should learn how send issues to MsSQL team and not only looking for
> > a workaround in NHibernate.
> > Do you have a link to the issue regarding the query-execution plan in MsSQL
> > ?
>
> > do you know why MsSQL need the parameter-size to choose the execution-plan
> > ?
> > why the others RDBMS does not need it ?
>
> > why I can write something so simple as
> > in FireBird: SELECT FIRST x [SKIP y] rest-of-sql-statement
> > in Postgre :  SELECT rest-of-sql-statement LIMIT x [OFFSET y]
> > in MySQL :  SELECT rest-of-sql-statement LIMIT x [, y]
> > and in MsSQL I must make me crazy for a simple pagination in the 21th
> > century ?
>
> > why I can't write something like this ?
> > SELECT rest-of-sql-statement WHERE CONTAINS(…, …) = :pShouldContain
> > Which is the type of return-value of the two functions CONTAINS and
> > FREETEXT ?
>
> > Perhaps you can tolerate all these stuff the MsSQL-team are giving us but
> > I'm starting to be a little bit not tolerant.
>

> > 2009/10/27 Rob <robscot...@gmail.com>

[Fabio Maulo]

Have you a link to pros and con of set all parameters size always for any DbType ?

I don't remind why we have that "if" there... probably was for the bug I have entirely analysed and fixed (about the management of size/precision,scale)  but perhaps is because somebody have analysed some other situation.

If you can provide enough info., to justify such change, you know which is the procedure (JIRA with explication, test and links).

I'm not worried by NH adoption... I'm 42 and you can imagine how much technology I saw born and die. NH has its lifecycle as any other tech.

2009/10/27 Daniel Auger <daniel...@gmail.com>

--
Fabio Maulo

[zvolkov]

Blogged: NHibernate parameter sizes controversy, history of the issue
http://zvolkov.com/blog/post/2009/10/28/NHibernate-parameter-sizes-controversy.aspx

[Rob]

No need to apologize, I've been where you are many times before -- It
can be horribly frustrating.

Thanks for your efforts, they are appreciated.

Rob

On Oct 27, 4:22 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> sorry for the relief... sorry
>

> 2009/10/27 Fabio Maulo <fabioma...@gmail.com>

>
>
>
>
>
> > and Rob...
> > my goal is do the right thing in the right place.
> > We all should learn how send issues to MsSQL team and not only looking for
> > a workaround in NHibernate.
> > Do you have a link to the issue regarding the query-execution plan in MsSQL
> > ?
>
> > do you know why MsSQL need the parameter-size to choose the execution-plan
> > ?
> > why the others RDBMS does not need it ?
>
> > why I can write something so simple as
> > in FireBird: SELECT FIRST x [SKIP y] rest-of-sql-statement
> > in Postgre :  SELECT rest-of-sql-statement LIMIT x [OFFSET y]
> > in MySQL :  SELECT rest-of-sql-statement LIMIT x [, y]
> > and in MsSQL I must make me crazy for a simple pagination in the 21th
> > century ?
>
> > why I can't write something like this ?
> > SELECT rest-of-sql-statement WHERE CONTAINS(…, …) = :pShouldContain
> > Which is the type of return-value of the two functions CONTAINS and
> > FREETEXT ?
>
> > Perhaps you can tolerate all these stuff the MsSQL-team are giving us but
> > I'm starting to be a little bit not tolerant.
>

> > 2009/10/27 Rob <robscot...@gmail.com>

[Fabio Maulo]

I don't know where you read or saw part of your story.

Try to implement that Drive and le me knoe what you must do when you are setting the parameters of each query (NHQ and Criteria).

After that let me know who are doing it in each query.

2009/10/28 zvolkov <zvo...@gmail.com>

Blogged: NHibernate parameter sizes controversy, history of the issue
http://zvolkov.com/blog/post/2009/10/28/NHibernate-parameter-sizes-controversy.aspx

--
Fabio Maulo

[zvolkov]

Fabio, this was my interpretation of your comment on NH-1713: "NH-1707
was reverted because, more than our work, it mean additional attention
by the user when he create a query (each parameter may need additional
info)... is better if the user set prepare_sql explicitly". My
apologies if what you meant was different from how I understood it.

On Oct 28, 6:02 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> I don't know where you read or saw part of your story.
> Try to implement that Drive and le me knoe what you must do when you are
> setting the parameters of each query (NHQ and Criteria).
> After that let me know who are doing it in each query.
>

> 2009/10/28 zvolkov <zvol...@gmail.com>

>
>
>
> > Blogged: NHibernate parameter sizes controversy, history of the issue
>

> >http://zvolkov.com/blog/post/2009/10/28/NHibernate-parameter-sizes-co...
>
> --
> Fabio Maulo

[Fabio Maulo]

No problem but write in your post that what you are saying is YOUR interpretation of the story.

We can't force, by default, each user to set the parameter dimension in each query.

Who set prepare-sql=true must know that, at the same time, he must change his way to write a query and he must set the right size in each query.

select p from Person where p.Name || p.Surname = :pFullName

which is the size of the parameter ?

Now think about what each user should do with his existing project if we will make that behaviour the default only because a MsSQL issue.

Who serve who ?

The RDBMS serve us or we should serve de RDBMS ?

If your choice is "serve MsSQL" we are giving you the way to do that (and its very easy, as I showed above). 

2009/10/28 zvolkov <zvo...@gmail.com>

--
Fabio Maulo

[zvolkov]

In your example below, what happens if I don't specify size in
SetParameter? Will it use vnvarchar(4000) by default?

On Oct 28, 6:25 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> No problem but write in your post that what you are saying is YOUR
> interpretation of the story.
> We can't force, by default, each user to set the parameter dimension in each
> query.
> Who set prepare-sql=true must know that, at the same time, he must change
> his way to write a query and he must set the right size in each query.
> select p from Person where p.Name || p.Surname = :pFullName
> which is the size of the parameter ?
>
> Now think about what each user should do with his existing project if we
> will make that behaviour the default only because a MsSQL issue.
> Who serve who ?
> The RDBMS serve us or we should serve de RDBMS ?
>
> If your choice is "serve MsSQL" we are giving you the way to do that (and
> its very easy, as I showed above).
>

> 2009/10/28 zvolkov <zvol...@gmail.com>

[Naz]

Hi
I don't now how the other databases work but MS SQL generates
different execution plans for each query individual query including
the parameter sizes so the Execution Plans can be different for a
query with a parameter of a different size it is a feature/behaviour
of SQL Server. Because of this known behaviour it has been documented
by nhforge and ayende who claim a so called fix for the issue.

http://ayende.com/Blog/archive/2009/05/02/nhibernate-the-database-query-cache-and-parameter-sizes.aspx
http://nhforge.org/wikis/howtonh/tuning-queries-with-ms-sqlserver.aspx
http://testdrivendevelopment.wordpress.com/2009/03/10/nhibernate-queries-sql-server-execution-plans/

The issue is this fix doesn't fix the problem rather it just changes
behaviour in which NHibernate connects to the database which requires
it to call SetParameterSizes() which at first glance seem to fix the
problem but actually only doing it for the current connection.

In my opinion the default behaviour of the NHibernate SQLServer driver
should be to SetParameterSizes() on every query it passes to the
Database.

Thanks Fabio for showing us how to implement our own driver.

Regards,

Naz

On Oct 28, 3:54 am, Fabio Maulo <fabioma...@gmail.com> wrote:
> Have you a link to pros and con of set all parameters size always for any
> DbType ?
> I don't remind why we have that "if" there... probably was for the bug I
> have entirely analysed and fixed (about the management of
> size/precision,scale)  but perhaps is because somebody have analysed some
> other situation.
>
> If you can provide enough info., to justify such change, you know which is
> the procedure (JIRA with explication, test and links).
>
> I'm not worried by NH adoption... I'm 42 and you can imagine how much
> technology I saw born and die. NH has its lifecycle as any other tech.
>

> 2009/10/27 Daniel Auger <daniel.au...@gmail.com>

[Fabio Maulo]

no if prepare_sql=false

have a look to the query by your self.

2009/10/28 zvolkov <zvo...@gmail.com>

--
Fabio Maulo

[Fabio Maulo]

My pleasure.

2009/10/28 Naz <nazm...@gmail.com>

--
Fabio Maulo

[zvolkov]

Fabio, my point is: if SQLServer is changed to always call
SetParameterSizes and SetParameter does not specify the size, NH will
simply default to nvarchar(4000), so what's the problem?

On Oct 28, 10:27 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> no if prepare_sql=false
> have a look to the query by your self.
>

> 2009/10/28 zvolkov <zvol...@gmail.com>

[Fabio Maulo]

2009/10/29 zvolkov <zvo...@gmail.com>

Fabio, my point is: if SQLServer is changed to always call
SetParameterSizes and SetParameter does not specify the size, NH will
simply default to nvarchar(4000), so what's the problem?

Try it with a app in prod. never set parameter size in your queries and pray. 

If all is working as you expect and your DBA is happy let me know (opening a JIRA with needed info).

--
Fabio Maulo

[zvolkov]

Ok, implemented these changes in the next version of my app, goes to
PROD around New Year.

On Oct 29, 12:09 am, Fabio Maulo <fabioma...@gmail.com> wrote:
> 2009/10/29 zvolkov <zvol...@gmail.com>

[Naz]

Hi
Although passing large default parameter sizes is still not a good
idea with MS SQL since the database potentially might start holding
large nvarchar(4000) parameters in memory. SQL Server is fairly smart
and will just make sure it has the memory available to hold it but
only use the memory required and not allocate the full amount.

This is still a lot better situation than not re-using execution plans
and/or filling up your server full of them to the point it starts
dropping plans so although the fix isn't perfect it's so much better
than the current behaviour.

The ideal situation is alongside the fix you set parameter sizes in
your mapping that match your table or if your to lazy to do that then
at least if you know most of your nvarchar fields are say 255 length
make that a default convention and only specify larger if required.

Regards,

Naz

[zvolkov]

Naz, once the fix is in place, you can do it in the mappings by adding
type="AnsiString(LENGTH)" to each string property.

[Naz]

I know ;)

"The ideal situation is alongside the fix you set parameter sizes in

your mapping that match your table..."

Naz

[Carsten Hess]

Being a part of this discussion in the beginning (and being quoted in
some of the referenced material) I'll give my latest 10 pence to the
discussion. I'm not working on a project with NHibernate right now,
thats why I've been silent.

1) prepare_sql: I wrote that setting prepare_sql was causing the
underlying ADO.NET layer to generate "prepared" statements instead of
"parameterized" statements. Prepared statements are not wery well
documented by MS, but gives back a handle to the application layer
which can be used to execute the same statement again without
resending the statement sql text (only actual parametervalues). What I
also wrote was that these prepared statements are only connection
local. That it still true when it comes to the handle (representing
the statement), but I later found out that the executionplan that was
generated at the same time is GLOBAL - as we want it to be... So
setting "prepare_sql = true", sqlprofiler shows us a lot of sp_prepare
statements which gives back new handles for the same statement for new
connections - but the underlying executionplan IS reused (as long as
the size of the parameterdefinitions are unchanged).

Just before version 2.1, Fabio implemented changes so the
typedefinition for strings (which caused the biggest problems) were
sent using the standard size of 4000. This is a OK solution for the
problem sketched above, which requires the typedefinition to be
constant in order to use an existing executionplan. In ver. 2.1 some
bugs concerning size of binaryblobs and decimals not being set
correctly were also fixed.

All in all my conclusion is that setting prepare_sql = true for
NHibernate later than ver. 2.1 IS a viable solution in order to reuse
executionplans (and avoid sending sql text more than once per
connection). You dont need to override the database driver code.

Kind regards
Carsten Hess

You can use this query to check whether your executionplans are reused
(usecounts column):

select top 100 st.text, cp.cacheobjtype, cp.objtype, cp.refcounts,
cp.usecounts, cp.size_in_bytes, cp.bucketid, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.cacheobjtype = 'Compiled Plan'
and cp.objtype = 'Prepared'

[Fabio Maulo]

at the end somebody with a way to confirm or deny an issue.

@Carsten Thanks.

@Andrei now you can add an episode to your story. Thanks

Btw the point, IMO, is the same... I never saw the issue for MsSQL team, only some external workaround.

--
Fabio Maulo

[Naz]

Thanks Carsten Hess for explaining how execution plans are re-used in
prepared statements.

However we still believe the default behaviour of NHibernate is that
all queries should be parameterized with the correct parameter lengths
ideally based on the table mapping and not dynamically as this causes
huge performance problems in executions plans not bieng re-used. What
surprised us is that is the default behaviour of the NHibernate's MS
SQL Driver which if we did not look into would have caused us major
problems had we gone live with NHibernate with the default setting.

So unless you know to set prepare_sql your application is not re-using
execution plans and even when you know to set it the queries are sent
using a not very well documented MS SQL procedure.

I still belive the fix is valid and we will continue to use our own
driver until NHibernate sorts out it's MS SQL driver.

Naz

On Oct 30, 11:43 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> at the end somebody with a way to confirm or deny an issue.
>
> @Carsten Thanks.
>
> @Andrei now you can add an episode to your story. Thanks
>
> Btw the point, IMO, is the same... I never saw the issue for MsSQL team,
> only some external workaround.
>

> 2009/10/30 Carsten Hess <carsten.hess...@gmail.com>

[Fabio Maulo]

Welcome to the nice world of NH's choices!!

As I said few weeks ago:

Words as “intelligent”/”smart” or “silly” are subjective opinions. In my opinion what is really important is not if a framework is silly or not but if the framework give me the opportunity to define what is intelligent for me.

2009/10/31 Naz <nazm...@gmail.com>

--
Fabio Maulo

[zvolkov]

Naz, I thought prepare_sql is set to true by default in NH 2.1 (when
using SqlClientDriver)...

[Naz]

Hi
I'm pretty sure the setting doesn't change based on your driver you
use we had to explicitly turn it on in our NHibernate config.

Naz

[zvolkov]

Yeah I just verified it and you're right, it is NOT set by default.
Looks like NH-1707 was indeed reverted despite having status of Fixed.
Mental note: never trust anybody.