Export (0) Print
Expand All

How to profile driver performance data (ODBC)

SQL Server 2000

  New Information - SQL Server 2000 SP3.

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

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

The complete sample code is in this file: CreateAndDisplayPerformanceLog.cpp. You can download an archive containing the sample from the Microsoft® SQL Server™ Downloads page at this Microsoft Web site.

This sample was developed for ODBC version 3.0 or later. It was developed with Microsoft® Visual C++® version 6.0, and may expose properties of the Microsoft Foundation Classes.

Security Note  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 cryptoAPI.

See Also

Profiling ODBC Driver Performance

SQLSetConnectAttr

SQLGetConnectAttr

Show:
© 2014 Microsoft