How does NHibernate handle execution plan?

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?

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

StackExchange Database Administrators Q#1866, answer score: 7