Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I heard from a podcast that there are no ORMs that have a good solution for execution plan reuse. It will lead to increased execution plan cache which affects the performance.

  • How does NHibernate handle execution plan?
  • Are execution plan reused in NHibernate?
share|improve this question
add comment

3 Answers 3

up vote 3 down vote accepted

To answer your first question, NHibernate does not handle execution plans. SQL Server handles execution plans. If the dynamic SQL produced by NHibernate is parameterized, the plans will be classified as "Prepared" and will be reused assuming the parameters provided in each subsequent execution could produce the same optimized query plan. If the dynamic SQL is not parameterized, the execution plan will be classified as "Adhoc" and could still be reused.

I use this bit of T-SQL to monitor cache sizes for various query plans. I believe I copied this from Paul Randal's site (http://www.sqlskills.com/BLOGS/PAUL/), but it has been so long that I can no longer know for sure.

SELECT 
    objtype AS [CacheType],
    count_big(*) AS [Total Plans],
    sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs],
    avg(usecounts) AS [Avg Use Count],
    sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1],
    sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC;
GO
share|improve this answer
add comment

ORMs aren't usually the problem themselves, it's how they're used. SQL Server generates the execution plan. NHibernate can generate a query that causes SQL to generate a bad execution plan. If you use variables in your queries, then execution plans can be reused. String concatenation usually prevents plan reuse. See this question for more info.

share|improve this answer
add comment

Besides the parametrization issue which has already been mentioned, all ORMs can cause really crappy plans to be generated. This is done most often when there are lots of search options causing a really large WHERE clause in the SQL Server query which is full of OR statements which the SQL Server can't do a whole lot with.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.