Watch out those prepared SQL statements

Although not documented very well, the system stored procedures listed below are known to many SQL Server developers and DBAs. For more details, please check out http://msdn.microsoft.com/en-us/library/ms176007.aspx. But rarely have any developers called these stored procedures directly from application code. They are usually executed on behalf of ODBC, OLE DB, or other APIs, which developers are familiar with.

sp_prepare

sp_execute

sp_prepexec

sp_unprepare

In a recent engagement with a partner, we were testing an intensive OLTP workload. The application uses a 3rd-party ODBC driver to interact with SQL Server 2008 R2. As the client application is running from Linux machines, we can't use Microsoft's ODBC driver in this scenario. During the test, we observed a strange behavior that the throughput gradually dropped over time. See screenshot of the perfmon below. The batch requests per second dropped from 3k to 2.7k in less than an hour.

After extensive investigation ruling out common things like resource contention, fragmentation, etc, we found that the application issued tons and tons of queries via sp_prepexec, but we couldn't find matching sp_unprepare statements. When we monitored SQL Server memory clerks (sys.dm_os_memory_clerks), we saw ever increasing MEMORYCLERK_SQLCONNECTIONPOOL while the number of connections stayed stable. So it's clear that SQL Server kept preparing the statement handles consuming more and more of the connection memory pool (see perfmon graph above with connection memory usage). And these handles were not cleaned up properly. The application code, as expected, is following the 3rd-party ODBC driver vendor standard API procedures to run the queries. The root cause is actually in the ODBC driver, which wrapped queries in sp_prepexec but failed to issue sp_unprepare afterwards causing the statement handle leak.

Read more: Microsoft SQL Server Development Customer Advisory

Posted via email from .NET Info