How to log long-running queries (ODBC)

To log long-running queries using ODBC Administrator

1. In Control Panel, double-click 32-bit ODBC.
   
   
2. Click the User DSN, System DSN, or File DSN tab.
   
   
3. Click the data source for which to log long-running queries.
   
   
4. Click Configure.
   
   
5. Navigate the Microsoft SQL Server Configure DSN Wizard to the page with Save long-running queries to the log file.
   
   
6. 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.
   
   
7. Set a query time-out interval, in milliseconds, in the Long query time (milliseconds) box.

To log long-running queries data programmatically

1. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_QUERY_LOG and vParam set to the full path and file name of the long-running query log file. For example:

   C:\\Odbcqry.log
   

2. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_QUERY_INTERVAL and vParam set to the time-out interval, in milliseconds.
   
   
3. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_QUERY and vParam set to SQL_PERF_START to start logging long-running queries.
   
   
4. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_QUERY and vParam set to SQL_PERF_STOP to stop logging long-running queries.

Examples

This example shows the creation of a long-running query log file. Error-checking code is removed to simplify this example.

// Sample showing the SQL Server ODBC driver-specific options
// to log long-running queries. Creates C:\Odbcqry.log, which
// contains a list of queries whose execution
// exceeds an interval set by the application.

#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;

int main() {

   RETCODE retcode;

    // Allocate the ODBC environment and save handle.
   retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
   // Notify ODBC that this is an ODBC 3.0 app.
   retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                     (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
   // Allocate ODBC connection handle and connect.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
   retcode = SQLConnect(hdbc1, "MyDSN", SQL_NTS,
               "sa", SQL_NTS, "MyPassWord", SQL_NTS));

   // Set options to log long-running queries, including the
   // file to use for the log.
   retcode = SQLSetConnectAttr
                     (hdbc1,
                     SQL_COPT_SS_PERF_QUERY_LOG,
                     &"c:\\odbcqry.log",
                     SQL_NTS);
   // 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);
   // Start the long-running query log.
   retcode = SQLSetConnectAttr
                     (hdbc1,
                     SQL_COPT_SS_PERF_QUERY,
                     (SQLPOINTER)SQL_PERF_START,
                     SQL_IS_UINTEGER);

   // Allocate statement handle then execute commands.
   retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);

   retcode = SQLExecDirect(hstmt1,
         "SELECT * FROM pubs.dbo.authors", SQL_NTS);
   // Clear any result sets generated.
   while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA )
      ;

   retcode = SQLExecDirect(hstmt1,
         "SELECT * FROM pubs.dbo.stores", SQL_NTS);
   // Clear any result sets generated.
   while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA )
      ;

   // Generate a long-running query.
   retcode = SQLExecDirect(hstmt1,
         "waitfor delay '00:00:04' ", SQL_NTS);
   // Clear any result sets generated.
   while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA )
      ;

   /* Clean up. */
   SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
   SQLDisconnect(hdbc1);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);
   return(0);
}

See Also

Profiling ODBC Driver Performance

SQLSetConnectAttr

SQLGetConnectAttr