Profiling ODBC Driver Performance Data - Log Long-Running Queries
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
This sample shows the SQL Server ODBC driver-specific options to log long-running queries. When run, this sample creates Odbcqry.log, which contains a list of queries whose execution exceeds an interval set by the application. This sample is not supported on IA64. This sample was developed for ODBC version 3.0 or later.
Important
When possible, use Windows Authentication. If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32 crypto API.
To log long-running queries using ODBC Administrator
In Control Panel, double-click Administrative Tools and then double-click Data Sources (ODBC). (Alternatively, you can run odbcad32.exe from the command prompt.)
Click the User DSN, System DSN, or File DSN tab.
Click the data source for which to log long-running queries.
Click Configure.
In the Microsoft SQL Server Configure DSN Wizard, navigate to the page with Save long-running queries to the log file.
Select Save long-running queries to the log file. In the box, place the name of the file where the long-running queries should be logged. Optionally, click Browse to browse the file system for the query log.
Set a query time-out interval, in milliseconds, in the Long query time (milliseconds) box.
To log long-running queries data programmatically
Call SQLSetConnectAttr with SQL_COPT_SS_PERF_QUERY_LOG and the full path and file name of the long-running query log file. For example:
C:\\Odbcqry.logCall SQLSetConnectAttr with SQL_COPT_SS_PERF_QUERY_INTERVAL and set to the time-out interval, in milliseconds.
Call SQLSetConnectAttr with SQL_COPT_SS_PERF_QUERY and SQL_PERF_START to start logging long-running queries.
Call SQLSetConnectAttr with SQL_COPT_SS_PERF_QUERY and SQL_PERF_STOP to stop logging long-running queries.
Example
You will need an ODBC data source called AdventureWorks, whose default database is the AdventureWorks sample database. (You can download the AdventureWorks sample database from the Microsoft SQL Server Samples and Community Projects home page.) This data source must be based on the ODBC driver that is supplied by the operating system (the driver name is "SQL Server"). If you will build and run this sample as a 32-bit application on a 64-bit operating system, you must create the ODBC data source with the ODBC Administrator in %windir%\SysWOW64\odbcad32.exe.
This sample connects to your computer's default SQL Server instance. To connect to a named instance, change the definition of the ODBC data source to specify the instance using the following format: server\namedinstance. By default, SQL Server Express installs to a named instance.
Compile with odbc32.lib.
// compile with: odbc32.lib
#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc1 = SQL_NULL_HDBC;
SQLHSTMT hstmt1 = SQL_NULL_HSTMT;
void Cleanup() {
if (hstmt1 != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
if (hdbc1 != SQL_NULL_HDBC) {
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
int main() {
RETCODE retcode;
// Allocate the ODBC environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(Env) Failed\n\n");
Cleanup();
return(9);
}
// Notify ODBC that this is an ODBC 3.0 app.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
Cleanup();
return(9);
}
// Allocate ODBC connection handle and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(hdbc1) Failed\n\n");
Cleanup();
return(9);
}
// sample uses Integrated Security, create SQL Server DSN using the Windows NT authentication.
retcode = SQLConnect(hdbc1, (UCHAR*)"AdventureWorks", SQL_NTS, (UCHAR*)"",SQL_NTS, (UCHAR*)"", SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLConnect() Failed\n\n");
Cleanup();
return(9);
}
// Set options to log long-running queries, including the file to use for the log.
retcode = SQLSetConnectAttr( hdbc1, SQL_COPT_SS_PERF_QUERY_LOG, &"odbcqry.log", SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLSetConnectAttr Failed\n\n");
Cleanup();
return(9);
}
// Set the long-running query interval (in milliseconds). Note that for version 2.50 and 2.65
// drivers, this value is specified in seconds, not milliseconds.
retcode =
SQLSetConnectAttr( hdbc1, SQL_COPT_SS_PERF_QUERY_INTERVAL, (SQLPOINTER)3000, SQL_IS_UINTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLSetConnectAttr Failed\n\n");
Cleanup();
return(9);
}
// Start the long-running query log.
retcode =
SQLSetConnectAttr( hdbc1, SQL_COPT_SS_PERF_QUERY, (SQLPOINTER)SQL_PERF_START, SQL_IS_UINTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLSetConnectAttr Failed\n\n");
Cleanup();
return(9);
}
// Allocate statement handle then execute commands.
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLAllocHandle(hstmt1) Failed\n\n");
Cleanup();
return(9);
}
retcode = SQLExecDirect(hstmt1, (UCHAR*)"SELECT * FROM Purchasing.Vendor", SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLExecDirect Failed\n\n");
Cleanup();
return(9);
}
// Clear any result sets generated.
while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA ) {
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLMoreResults Failed\n\n");
Cleanup();
return(9);
}
}
retcode = SQLExecDirect(hstmt1, (UCHAR*)"SELECT * FROM Sales.Store", SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLExecDirect Failed\n\n");
Cleanup();
return(9);
}
// Clear any result sets generated.
while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA ) {
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLMoreResults Failed\n\n");
Cleanup();
return(9);
}
}
// Generate a long-running query.
retcode = SQLExecDirect(hstmt1, (UCHAR*)"waitfor delay '00:00:04' ", SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLExecDirect Failed\n\n");
Cleanup();
return(9);
}
// Clear any result sets generated.
while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA ) {
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLMoreResults Failed\n\n");
Cleanup();
return(9);
}
}
// Cleanup
SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}