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 <[email protected]> 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 <[email protected]> > > > > > > > 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' > > > On 30 Okt., 18:49, Naz <[email protected]> wrote: > > > I know ;) > > > > "The ideal situation is alongside the fix you set parameter sizes in > > > your mapping that match your table..." > > > > Naz > > > > On Oct 30, 12:58 pm, zvolkov <[email protected]> wrote: > > > > > Naz, once the fix is in place, you can do it in the mappings by adding > > > > type="AnsiString(LENGTH)" to each string property. > > > > > On Oct 30, 5:08 am, Naz <[email protected]> wrote: > > > > > > 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 > > > > > > On Oct 29, 1:55 pm, zvolkov <[email protected]> wrote: > > > > > > > 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 <[email protected]> wrote: > > > > > > > > 2009/10/29 zvolkov <[email protected]> > > > > > > > > > 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 > > -- > Fabio Maulo --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
