Author: Kun Cheng Reviewers: Sunil Agarwal, Chuck Heinzelman, Shaun Tinline-Jones, Prem Mehra
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.
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.
Red line - Batch Requests per second
Green line - SQL Server Connection memory
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.
In terms of how to resolve the particular issue, in our scenario, until a fix for the ODBC driver is available, we used a workaround to make direct execution (executedirect()) of the queries instead of preparing them. We forfeited the benefits of prepare/execute model and let SQL Server do parameterization and plan cache for these queries. The throughout had been stable with hours of execution.
Well it's another thing for you developers and DBAs to look out for a mystic performance degradation scenario like this.