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