Symptoms
While doing load testing of my C++ app that continually(and heavily) interfaces with a SQL Server instance using and ODBC connection I started noticing handle leaks in the Windows Task Manager. These were not there before (please curb your skepticism and read on) and I suspect they developed while doing said load testing.
Running the exact same binary on an alternate machine does not show the same symptoms, but instead correlates with the expected behavior that was standard and commonplace before the leak started. I.e. no leak with the same binary on a different machine.
I traced this handle leak to the SQL connect/disconnect process and was able to recreate using a console application that only opens and closes a connection to a SQL Server instance, modeled on the SQLDriverConnect() MSDN example. Code should be shown below.
- Not all handles allocated seem to be released.
- Return values show all SQL operations in code execute without error.
Technical details of components used
- Dev machine (leak). Windows 7 Pro SP1.
- ODBC drivers tried:
- SQL Server 6.01.7601.17514
- ODBC Driver 13 for SQL Server 2015.130.1601.05.
- ODBC Driver 13 for SQL Server 2015.131.4413.46.
- Dev machine with updated OS (leak). Windows 10 Pro
- ODBC drivers tried:
- SQL Server 10.00.15063.00
- ODBC Driver 13 for SQL Server 2017.140.500.272.
- Alternate machine (no leak). Windows Server 2012 R2.
- ODBC driver: Sql Server 6.03.9600.17415
- Visual Studio 2013 (v120) Console application.
- Standard windows libs
- Character set not set.
- No CLR support.
- No whole program or C++ optimization.
- SQL Server connections tried from Dev machine.
- Express 9.0.5.
- 13.0.1728.2.
What has been tried without success
Connecting to another SQL server instance.
Using a different ODBC driver.
Repair Visual Studio 2013 Pro and rebuild binary.
Re-install visual Studio 2013 Pro and rebuild binary.
Re-install SSMS (in attempt to refresh local built-in drivers).
Uninstall all components that contain "SQL" from PC and install the latest SSMS (in attempt to reduce component conflicts).
Extract only suspected components into its own Console application
Attempt SQLConnect() using default system DSN instead of SQLDriverConnect() with all params explicitly specified.
Updated SQL Server ODBC driver 13 to 2015.131.4413.46.
Console application source
// ConsoleTest.cpp : Defines the entry point for the console application.
//
#include <windows.h>
#include <stdio.h>
#include <tchar.h>
#include <string>
#include <sqlext.h>
#include <sqltypes.h>
#include <iostream>
#include "MyTypes.h"
#pragma comment(lib, "ODBC32.lib")
static s32 PrintSqlDiagRecords(SQLRETURN _sqlRet, SQLSMALLINT _sqlHandleType, SQLHANDLE _sqlHandle);
int _tmain(int argc, _TCHAR* argv[])
{
std::cout << "Starting test" << std::endl;
SQLHANDLE m_sqlHndlEnvironment = NULL;
SQLHANDLE m_sqlHndlConnection = NULL;
for (int k = 0; k < 500; ++k)
{
//Allocate environment handle
SQLRETURN ssiResult = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_sqlHndlEnvironment);
if ((ssiResult != SQL_SUCCESS) && (ssiResult != SQL_SUCCESS_WITH_INFO))
{
std::cout << "Error allocating ENV handle" << std::endl;
return -1;
}
//Set environment attribute
ssiResult = SQLSetEnvAttr(m_sqlHndlEnvironment, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
if ((ssiResult != SQL_SUCCESS) && (ssiResult != SQL_SUCCESS_WITH_INFO))
{
std::cout << "Error setting ODBC version" << std::endl;
return -1;
}
//Allocate connection handle
ssiResult = SQLAllocHandle(SQL_HANDLE_DBC, m_sqlHndlEnvironment, &m_sqlHndlConnection);
if ((ssiResult != SQL_SUCCESS) && (ssiResult != SQL_SUCCESS_WITH_INFO))
{
std::cout << "Error allocating DBC handle" << std::endl;
return -1;
}
(void)SQLSetConnectAttr(m_sqlHndlConnection, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
//Connect SQL
SQLCHAR acOutmsg[1024] = { 0 };
ssiResult = SQLDriverConnect(
m_sqlHndlConnection,
NULL,
(SQLCHAR*)"My Verified Connection String",
SQL_NTSL,
acOutmsg,
sizeof(acOutmsg),
NULL,
SQL_DRIVER_NOPROMPT);
if (ssiResult != SQL_SUCCESS)
{
(void) PrintSqlDiagRecords(ssiResult, SQL_HANDLE_DBC, m_sqlHndlConnection);
//If success with info, just dump diagnostic info
if (ssiResult == SQL_SUCCESS_WITH_INFO) {/*Do nothing for now*/ }
//Else error
else
{
std::cout << "Error connecting to DB" << std::endl;
return -1;
}
}
//Leave out actual DB operation to simplify execution path, but spin here for a while
Sleep(10);
//Free handles
SQLRETURN sqlRet = SQL_SUCCESS;
if (m_sqlHndlConnection != NULL)
{
sqlRet = SQLDisconnect(m_sqlHndlConnection);
if (sqlRet != SQL_SUCCESS)
{
(void)PrintSqlDiagRecords(ssiResult, SQL_HANDLE_DBC, m_sqlHndlConnection);
}
sqlRet = SQLFreeHandle(SQL_HANDLE_DBC, m_sqlHndlConnection);
if (sqlRet != SQL_SUCCESS)
{
std::cout << "Error freeing DBC handle" << std::endl;
return -1;
}
m_sqlHndlConnection = NULL;
}
//Free environment handle
if (m_sqlHndlEnvironment != NULL)
{
sqlRet = SQLFreeHandle(SQL_HANDLE_ENV, m_sqlHndlEnvironment);
if (sqlRet != SQL_SUCCESS)
{
std::cout << "Error freeing ENV handle" << std::endl;
return -1;
}
m_sqlHndlEnvironment = NULL;
}
}
std::cout << "Test complete" << std::endl;
Sleep(5000);
return 0;
}
s32 PrintSqlDiagRecords(SQLRETURN _sqlRet, SQLSMALLINT _sqlHandleType, SQLHANDLE _sqlHandle)
{
SQLRETURN sqlRetDiag = SQL_SUCCESS;
SQLINTEGER sqliNativeError = SQL_SUCCESS;
SQLSMALLINT sqlsiMsgLen = 0;
SQLCHAR acOutmsg[1024] = { 0 };
SQLCHAR acSqlState[1024] = { 0 };
int i = 1;
sqlRetDiag = SQLGetDiagRec(_sqlHandleType, _sqlHandle, i, acSqlState, &sqliNativeError, acOutmsg, sizeof(acOutmsg), &sqlsiMsgLen);
while ((sqlRetDiag != SQL_NO_DATA) && (i < 100))
{
//std::cout << "Msg[" << i <<"]: " << acOutmsg << "State: " << acSqlState << std::endl;
++i;
memset(acOutmsg, 0, sizeof(acOutmsg));
memset(acSqlState, 0, sizeof(acSqlState));
sqlRetDiag = SQLGetDiagRec(_sqlHandleType, _sqlHandle, i, acSqlState, &sqliNativeError, acOutmsg, sizeof(acOutmsg), &sqlsiMsgLen);
}
return 0;
}
Observations
- Same binary on a different machine not showing these leaks indicates an issue localized to the dev PC.
- Recreating with different versions of SQL Server drivers reduces the probability of it being ODBC driver related.
- Recreating after update of ODBC driver supports 2., as it stands to reason that out-of-date/broken driver files would have been updated with new install.
- Repeating the connect/disconnect process 500 times, leaves ~580 handles just before the app exits. 4.1 Breaking execution after each iteration shows 1 handle leak / iteration.
- Test console app execution on alternate machine shows handle count stable at ~135 during execution and after all iterations are complete.
Any idea what is causing the leak?
Any insight would be greatly appreciated.
Kind regards.
EDIT 6/7/2017: Update and change goal of forum question to be fix oriented.
- The original question context was meant to point out the cause, so I can fix it. The emphasis has now shifted to finding a solution and inferring the cause.
- Following input from @JeroenMostert, I did a cursory examination to try and peg down the cause to the operation that might be causing the issue.
UPDATE 6/7/2017:
- Neglected to mention that I recently restored my Win 7 Pro OS onto an SSD shortly before symptoms started. Handle leak was not present for the first couple of days after the OS move (more info in presentation of symptoms above).
- Suspected a possible link to Win 7 - SSD combination as the console application above did not show any leaks when tried on a Windows 8.1 or Windows 10 machine (same behavior as the Windows Server 2012 R2 mentioned above).
- Clean install of Windows 10 Pro done on dev machine. Preliminary tests with the console app and pristine original application binary shortly after OS install were successful.
- Installed VS2017 and kept original VC++ lib and target platform settings intact.
- Installed miscellaneous components needed for day-to-day operations.
- Resuming development, the handle leak was present again with same pattern of symptoms where binary on local machine reacted differently than the same binary run on other trusted machines.
- Pristine console app built before OS update and verified on other test machines show same leaky behavior only on my machine.
- Console app using SQL Server ODBC v10.00.15063.00 and ODBC Driver 13 for SQL Server 2017.140.500.272 showed leaky behavior.
- An interim solution at the moment is to assume I cannot trust my dev machine's handle consumption (at least as far as SQL connections go), and verify handle usage on our staging server.
Debugged above application in attempt to try and find which part of the process is not releasing all its handles.
(A) Win 10 Pro, VS2013, VC++ v120, SQL Server ODBC v10.00.15063.00 - 4 iterations, average 1 handle leak per iteration
- Connect: +5; +6; +6; +6
- Disconnect: -1; -1; -1; -1
- DBC free: -1; -2; -2; -2
- ENV free: -1; -2; -2; -1
(B) Win Server 2012 R2, VS2013, VC++ v120, SQL Server ODBC 6.03.9600.17415 - 4 iterations - net handle usage constant
- Connect: +10; +11; +10; +10
- Disconnect: -1; -1; -1; -1
- DBC free: -2; -2; -3; -2
- ENV free: -7; -7; -7; -7
Granted a fairly small sample size:
- Suspect ENV handle free op is the culprit. Usage is constant in working case (B) and more variable in (A).
- Disconnect op seems constant across both cases.
- DBC free seems fairly constant across both cases (1 change in 4 iterations)