Condividi tramite

Watch out those prepared SQL statements

  • Articolo

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 https://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 .

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.

 

Additional Reference:

Comments

  • Anonymous
    December 27, 2010
    The comment has been removed
  • Anonymous
    January 20, 2011
    The comment has been removed
  • Anonymous
    February 02, 2011
    I inadvertantly deleted something at some point b/c my laptop gives me this error message each time I boot. SQL Writer Program: Sqldumper Library failed to initialize.  Un-Install then re-run setup to correct I've no idea what SQL writers or libraries are but I presume it's required (?)  If yes, how can I fix it?  If it's not required, can I just delete all the SQL programs from the control panel?   Regards, T