Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_prepexec and nHibernate


sp_prepexec and nHibernate

Author
Message
ajsongy
ajsongy
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 116
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.
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 6852
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
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 3120
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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6002 Visits: 8314
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
Solomon Rutzky
Solomon Rutzky
Say Hey Kid
Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)

Group: General Forum Members
Points: 666 Visits: 2937
Great article. I also rated it 5.

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


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/
ryan.offord
ryan.offord
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 406
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)
Gazareth
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2872 Visits: 5351
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search