How to profile driver performance data (ODBC)

To log driver performance data 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 performance.
4. Click Configure.
5. Navigate the Microsoft SQL Server Configure DSN Wizard to the page with Log ODBC driver statistics to the log file.
6. Select Log ODBC driver statistics to the log file. In the box, place the name of the file where the statistics should be logged. Optionally, click Browse to browse the file system for the statistics log.

To log driver performance data programmatically

1. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_DATA_LOG and vParam set to the full path and file name of the performance data log file. For example:

   "C:\\Odbcperf.log"

     

2. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_DATA and vParam set to SQL_PERF_START to start logging performance data.
3. Optionally, call SQLSetConnectAttr with fOption set to SQL_COPT_SS_LOG_NOW and vParam set to NULL to write a tab-delimited record of performance data to the performance data log file. This can be done multiple times as the application runs.
4. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_DATA and vParam set to SQL_PERF_STOP to stop logging performance data.

To pull driver performance data into an application

1. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_DATA and vParam set to SQL_PERF_START to start profiling performance data.
2. Call SQLGetConnectAttr with fOption set to SQL_COPT_SS_PERF_DATA and pvParam set to the address of a pointer to a SQLPERF structure. The first such call sets the pointer to the address of a valid SQLPERF structure that contains current performance data. The driver does not continually refresh the data in the performance structure. The application must repeat the call to SQLGetConnectAttr anytime it needs to refresh the structure with more current performance data.
3. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_DATA and vParam set to SQL_PERF_STOP to stop logging performance data.

The SQLPERF structure is defined in Odbcss.h as follows:

typedef struct sqlperf

{

    // Application profile statistics

    DWORD TimerResolution;

    DWORD SQLidu;

    DWORD SQLiduRows;

    DWORD SQLSelects;

    DWORD SQLSelectRows;

    DWORD Transactions;

    DWORD SQLPrepares;

    DWORD ExecDirects;

    DWORD SQLExecutes;

    DWORD CursorOpens;

    DWORD CursorSize;

    DWORD CursorUsed;

    LDOUBLE PercentCursorUsed;

    LDOUBLE AvgFetchTime;

    LDOUBLE AvgCursorSize;

    LDOUBLE AvgCursorUsed;

    DWORD SQLFetchTime;

    DWORD SQLFetchCount;

    DWORD CurrentStmtCount;

    DWORD MaxOpenStmt;

    DWORD SumOpenStmt;

    // Connection statistics

    DWORD CurrentConnectionCount;

    DWORD MaxConnectionsOpened;

    DWORD SumConnectionsOpened;

    DWORD SumConnectionTime;

    LDOUBLE AvgTimeOpened;

    // Network statistics

    DWORD ServerRndTrips;

    DWORD BuffersSent;

    DWORD BuffersRec;

    DWORD BytesSent;

    DWORD BytesRec;

    // Time statistics

    DWORD msExecutionTime;

    DWORD msNetworkServerTime;

}     SQLPERF;

Examples

This example shows both the creation of a performance data log file and displaying performance data directly from the SQLPERF data structure. Error-checking code was removed to simplify this example.

// Sample showing the SQL Server ODBC driver-specific options

// to record performance statistics.  The sample creates

// one file:

//

// C:\Odbcperf.log contains performance statistics and

// can be imported to a spreadsheet program such as Microsoft Excel

// as a tab-delimited file for analysis.

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

    // Pointer to the ODBC driver performance structure.

    SQLPERF        *PerfPtr;

    SQLINTEGER    cbPerfPtr;

     // Allocate the ODBC environment and save handle.

    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

    // Let ODBC know 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 performance statistics.

    // Specify file to use for the log.

    retcode = SQLSetConnectAttr

                            (hdbc1,

                            SQL_COPT_SS_PERF_DATA_LOG,

                            &"c:\\odbcperf.log",

                            SQL_NTS);

    // Start the performance statistics log.

    retcode = SQLSetConnectAttr

                            (hdbc1,

                            SQL_COPT_SS_PERF_DATA,

                            (SQLPOINTER)SQL_PERF_START,

                            SQL_IS_UINTEGER);

    // Allocate statement handle, then execute command.

    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 )

        ;

    // Write current statistics to the performance log.

    retcode = SQLSetConnectAttr

                            (hdbc1,

                            SQL_COPT_SS_PERF_DATA_LOG_NOW,

                            (SQLPOINTER)NULL,

                            SQL_IS_UINTEGER);

    // Get pointer to current SQLPerf structure.

    // Print a couple of statistics.

    retcode = SQLGetConnectAttr

                            (hdbc1,

                            SQL_COPT_SS_PERF_DATA,

                            (SQLPOINTER)&PerfPtr,

                            SQL_IS_POINTER,

                            &cbPerfPtr);

    printf("SQLSelects = %d, SQLSelectRows = %d\n",

                PerfPtr->SQLSelects, PerfPtr->SQLSelectRows);

    /* Clean up. */

    SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);

    SQLDisconnect(hdbc1);

    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

    SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return(0);

}

See Also