Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

sp_prepexec and nHibernate Expand / Collapse
Author
Message
Posted Thursday, December 8, 2011 6:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:05 PM
Points: 4, Visits: 73
Nice article.

We could not keep up with performance issues caused by using hibernate ("experts" consulted, etc.). We finally convinced our management that hibernate is a development tool and not intended for a production environment. The development staff now works with the DBA staff who develops the SQL code. Performance at the app and db layers is much improved.
Post #1218590
Posted Thursday, December 8, 2011 6:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:31 AM
Points: 2,361, Visits: 6,749
Thx David, Excellent article. I gave a five star rating for it.
We are using nHybernate here. We have noticed blotting of the plan cache. There fore the first thing I did was to set Optimization for Ad-Hoc queries. Then clear the cache. I have not seen much blotting after that.
I also made sure that the developers used the right data types when passing the parameter. I am also thinking of passing in the in parameters as table valued parameters.


-Roy
Post #1218595
Posted Thursday, December 8, 2011 7:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 2,908, Visits: 1,833
CyberWomble (12/8/2011)
I have a couple of questions about this article:

1.) Are the plans cached by sp_prepexec stored in the same place as the plans cached when using Stored Procedures?

2.) Do you think that it is a bug with SQL Server that plans created using sp_prepexec are not re-used across connections?

3.) Does the "per connection" plan re-use behaviour change between versions of sql server 2000/2005/2008?

4.) If you are calling SP's from ADO.net are the plans re-used across connections in a web application?


sp_PrepExec stores its plans in the same place. You can actually store your query in a @variable and run it through sp_PrepExec and sp_ExecuteSQL and see one reuse the others plan. It matters not whether the query is called from separate sessions or applications providing the plan is absolutely identical. Bear in mind the cache is case sensitive.

What little documentation for sp_PrepExec describes the use of its plan as being local but I found that it is the handle that is local and not the plan. If that's a bug then please Microsoft DON'T FIX IT!

I haven't tested the behaviour against SQL2000 or 2005. I probably should but as an architect I'm desparately trying to avoid encouraging the use of 7+ year old technology.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #1218624
Posted Thursday, December 8, 2011 8:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 4,401, Visits: 6,262
I have seen issues at clients where the prep/unprep stuff was a huge perf issue because it was getting called a bajillion times for stuff that was only executed ONCE! It is ridiculous to go through the round trips and extra overhead to prep something and then tear that prep down just to make ONE call to the database!!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1218655
Posted Thursday, December 8, 2011 1:46 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:23 AM
Points: 368, Visits: 1,950
Great article. I also rated it 5.

I tried looking through DMVs, Google, etc trying to find any mention of that handle but NOTHING!!


David.Poole (12/8/2011)
If people want to give an article a low rating that's fine, but it would help if they said why.


As a fellow author I completely agree that it would be nice to get feedback. Given how many really good articles are rated lower than expected with nothing but positive comments in the forum, I sometimes think some people just don't like the topic and aren't necessarily rating the article.


Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #1218938
Posted Monday, December 12, 2011 1:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 6:22 AM
Points: 27, Visits: 376
For amusement when reading another topic (http://michaeljswart.com/2011/12/cxpacket-whats-that-and-whats-next/) I decided to run that against my DB that gets hit by the nHibernate queries.

In short the above post gives an example to show the top 20 worst hit parallel queries.

The result I got was 20 variations on a theme of the same query. I expanded it to 100 and I'd say 90 of them were the same query. Whilst it definitely confirms that it's a pretty poor query, it highlights the problem with diagnosing statements that can and should be refined. I'm sure I get hit with a lot more queries, but to separate these out to something useful ? My top 100 isn't really a top 100 then. It's a top 10 or 11. It is possible to get this same sort of problem whatever you look at.

Something worth looking at when you get this is the execution plan and the sheer number of 'compute scalar' icons in the execution plan. They will be very easy to spot (albeit with 0% cost)
Post #1220036
Posted Tuesday, January 31, 2012 5:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 2,013, Visits: 3,445
Thanks David, great article - 5 stars!

Our Devs here are using nHibernate for a new project so I'll be keeping an eye on this.
I agreed to it with the proviso that I am not responsible for performance issues caused by it but lets see how long that lasts if it all goes south!
Post #1244262
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse