Like most DBAs I keep a close eye on what gets thrown at my databases. I'm particularly nervous about ORM tools. It is not because they are not any good; on the contrary, one or two have left me quietly impressed.

If I had to state why they make me nervous then the top of my list would be their potential to emasculate me as a DBA. The worst case scenario is that they are used to execute something legitimate in a horrible way and I find myself in a situation where I am responsible for a production DB under stress: -

• The DB stressed by one application is under pressure to the extent that performance and stability of the other applications is threatened and may actually end in downtime
• I have no power to tune or maintain the queries being thrown at the stressed database
• I cannot get developer resources assigned to correct the root problem
• I cannot get additional physical resources (memory, disk space, CPU) to buffer me against the stress
• I am being held responsible for a situation for something totally out of my control or sphere of influence

nHibernate

Now it is worth stating that tools such as nHibernate can be set up and used in a way that does not cause the DBAs to mainline Valium. There is nothing special about such set up and use merely to say that it has been done "Properly".

The first time I saw a profile run against an nHibernate application I saw that it was using three particular system stored procedures

• sp_prepexec
• sp_execute
• sp_unprepare

At the time these stored procedures were undocumented and even now the formal documentation is not in the installed Books Online and the web edition is sparse. These three stored procs are listed in the MSDN article "System Stored Procedures (Transact-SQL)" as calls made by ADO, OLEDB and ODBC APIs with warnings that being called from within T-SQL or explicitly as part of an application is not supported as their operation may be deprecated or amended at any time.

As nHibernate was using these procs, albeit by legitimate means, I thought I had better investigate what they do and how they work.

sp_prepexec

My first step was to build some test code using the Adventureworks database

USE AdventureWorks
GO
DECLARE @handle INT,
 @EmployeeID INT
EXEC sp_prepexec
    @handle OUTPUT, -- Handle to be used by sp_execute and sp_unprepare statements.
    N'@EmployeeID INT', -- Parameter Definition
    N'SELECT * FROM HumanResources.Employee WHERE EmployeeID=@EmployeeID', -- Command
    N'1' -- Parameter value
SELECT @handle
EXEC sp_execute @handle,2 -- Reuses the prepared statement for EmployeeID 2
EXEC sp_execute @handle,3 -- Reuses the prepared statement for EmployeeID 3
EXEC sp_unprepare @handle -- Releases the handle

All this does is return separate recordsets for EmployeeID values 1,2 and 3 and also a scalar value recordset with the @handle parameter generated.

The stored procedures are as follows

There is a 4^th stored proc in this family called sp_prepare which performs the same function as sp_prepexec but returns an empty recordset to reveal the column metadata.

The metadata recordset from sp_prepare is exactly the same as if we had run the following

SET FMTONLY ON
SELECT * FROM HumanResources.Employee
SET FMTONLY OFF

Both statements simply return the column headings and definitions without running an actual query.

SQL Profiler and sp_prepexec

I decided to use SQL Profiler to test what happened when my test code ran.

For a starting point I ran the following commands in their own window to make sure I was starting from a clean procedure cache.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Back in my test code window my initial sp_prepexec generated the following events

• sp:cachemiss
• sp:cacheinsert
• Prepare SQL
• Sp:cachehit
• Exec PreparedSQL

Each of my sp_execute commands generated the following events

• Sp:cachehit
• Exec PreparedSQL

Finally my sp_unprepare produced

• Unprepare SQL

We can see that sp_prepexec and sp_execute are using the procedure cache to gain advantage from parameterised SQL.

sp_prepexec and separate sessions

As a separate experiment I commented out the sp_unprepare statement.

After executing my new code I copied the exact code into a second SSMS window and ran it attempting to use the @handle from my first window and got the following error for my troubles

Msg 8179, Level 16, State 2, Procedure sp_unprepare, Line 1
Could not find prepared statement with handle 2.
    

When I ran my full code in my 2^nd window I got the following events

• sp:cachehit
• Prepare SQL
• Exec PreparedSQL

From these experiments I have learnt two things

• I cannot share prepared statements between windows or sessions
• The execution plan is cached globally and is reused for both sessions. This contradicts Books Online/MSDN!

MSDN documentation for sp_unprepare says that the execution plan is discarded. This is not true, it actually means the handle is deallocated. The execution plan remains in the proc cache until it ages out of the cache.

You can verify this by running the following SQL statement

SELECT
   ST.dbid,
   st.text,
   qp.query_plan,
   cp.cacheobjtype,
   cp.objtype,
   cp.plan_handle,
   qp.number
 FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
    

Sp_PrepExec handles

Neither myself or my coalesce of DBAs have yet to find a way of listing open handles generated by sp_prepare and sp_prepexec statements except by recording them when they are generated.

If you know of a way of identifying handles that have not gone through an sp_unprepare then please post the answer.

Every sp_prepare or sp_prepexec should have a corresponding sp_unprepare statement to release the handle. In theory whenever an sp_reset_connection is issued it should free up the handles but without the means to list those handles in the first place this cannot be demonstrated.

Summary, concluding thoughts and observations on sp_prepexec

• nHibernates' use of sp_PrepExec is legitimate. It is as a result of using ODBC, ADO and OLE DB drivers.
• sp_prepexec does at least use the proc cache but each session has to prepare and unprepare statements.
• If you have a few windows apps hitting your database this probably isn't a big deal.
• If you have a busy web site where connections are being taken and returned to the connection pool continuously you are going to see a huge amount of sp_prepexec and sp_unprepare statements.
• Again, most of the queries nHibernate throws at a database are fairly simple so again this still may not be an issue.
• If a lot of complex queries are thrown at the database then the cost of constantly preparing statements is likely to make itself felt.
• The nature of web programming is such that connections are used sparingly and with sp_reset_connection being used frequently so the benefits of reuse via sp_execute doesn't seem to materialise.

My DBA career began when keeping database calls to the database to a minimum was one of the BIG rules so having loads of sp_prepexec/sp_unprepare feels wrong.

That said, there was also the rule about grabbing only what data you need when it is needed. With ORM tools and caching technologies the intepretation of this is somewhat different. In web programming the rules seems to have become

• Grab everything you will need (note the future tense)
• Grab it early
• Cache it and don't go back to the database for it unless you absolutely have to

There are a few posts out on the web concerning sp_prepexec and locking and blocking. I have personally seen sp_prepexec result in an unkillable SPID. The root cause was a deadlock but as sp_prepexec is classified as a cursor stored procedure I suspect that it is this that compounded the issue. Again, the lack of documentation prevents further research.

If you are really uncomfortable with nHibernate using sp_prepexec then it is possible to switch to using sp_executesql by making sure that the nHibernate config file has the following entry in it.

    <property name='prepare_sql'>false</property>