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 --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
