Enabling Advanced Driver Tracing for the SQL Native Client ODBC Drivers

  • [アーティクル]

 

If you are using the SQL Native Client ODBC driver, there is built-in high performance very detailed driver tracing, here is how you use it.

#1. Create batch file to start tracing (this traces for all three versions of SQL Native Client ODBC Driver) named StartSNACTrace.cmd:

@echo off echo Starting SNAC tracing... echo {BA798F36-2325-EC5B-ECF8-76958A2AF9B5} 0xFFFFFFFF 128 SQLNCLI > providers.txt echo {A9377239-477A-DD22-6E21-75912A95FD08} 0xFFFFFFFF 128 SQLNCLI10 >> providers.txt echo {2DA81B52-908E-7DB6-EF81-76856BB47C4F} 0xFFFFFFFF 128 SQLNCLI11 >> providers.txt reg add HKLM\SOFTWARE\Microsoft\BidInterface\Loader /v :Path /t REG_SZ /d "%systemroot%\system32\msdadiag.dll" /f del /Q *.etl logman create trace -n SnacTrace -ct perf -pf providers.txt -bs 10000 -nb 10000 50000 -o SnacTrace.etl -max 100 -cnf 00 logman start -n SnacTrace echo Run StopSnacTrace.cmd to stop tracing.

Note, replace 0xFFFFFFFF with 0x00000000 to do minimum tracing.  The number 128 indicates save trace data as ANSI, which reduces by about 1/2 the space for the ETL files.

Note you MUST start the tracing before the process that uses the SQL Native Client ODBC driver starts (this is VERY IMPORTANT).   

Start the tracing then start the process that is using the SQL Native Client ODBC driver.   You can stop tracing at any time, even before the process exits.

#2. Create this batch file named StopSnacTrace.cmd to stop the tracing:

@echo off echo Stopping SNAC tracing... logman stop -n SnacTrace logman delete -n SnacTrace reg delete HKLM\SOFTWARE\Microsoft\BidInterface\Loader /v :Path /f echo SNAC tracing stopped

The above settings will create a new trace file of maximum size 100MB numbered SnacTrace_000001.etl, SnacTrace_000002.etl, etc…

#3. To process the trace files, you need to register a special mof file on the trace processing machine (you do not have to do this on the trace gathering machine)

#4. Download and register the all.mof file from "Data Access Tracing in SQL Server 2012" MSDN article (last time I checked the URL is here https://technet.microsoft.com/en-us/library/hh880086)

There is a section in this article that says "Download Sample and MOF Files". This file is named Setup.zip.

Download this file to some temporary folder and extract the files.  You will see a folder named MOF_Files, go into this folder and locate the snac*.mof files , then on the client machine where you want to do tracing,

run the following from an elevated command prompt:

C:\>mofcomp all.mof

Microsoft (R) MOF Compiler Version 6.1.7600.16385Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.Parsing MOF file: all.mofMOF file has been successfully parsedStoring data in the repository...Done!

This will register all the providers so you can now see them with logman, to verify they are registered, run:

C:\>logman query providers | findstr SQL
SQLNCLI.1                                {BA798F36-2325-EC5B-ECF8-76958A2AF9B5}
SQLNCLI10.1                              {A9377239-477A-DD22-6E21-75912A95FD08}
SQLNCLI11.1                              {2DA81B52-908E-7DB6-EF81-76856BB47C4F}

You should see all 3 SNAC driver versions in the output.  You are now ready to process the traces (the *.etl files generated)

#5. To process the traces to extract the data to CSV file, use tracerpt tool (comes with Windows) or LogParser (you need to download this tool).

Run following from a command prompt:

tracerpt SnacTrace_000001.etl -of CSV -en ANSI -gmt -o SnacTrace_000001.csv

Note I find the LogParser tool (you can download from Microsoft) provides a nicer, more compact log file, try this command:

LogParser.exe "select eventnumber, eventname, timestamp, userdata into SnacTrace_000001.csv from SnacTrace_000001.etl"
-i:ETW -o:CSV -oTsFormat:"yyyy-MM-dd hh:mm:ss.n" -headers:off

This will give you a really nice looking trace file output, and LogParser is also very fast to process large traces.

Comments

  • Anonymous
    June 12, 2014
    Hello Sir, Its an awesome article and very helpful. We were able to trace the client calls for MSFT native SQL server client. I have one query :- How can we check what username and connection string is being used by driver while connecting. I could not find that. Is there any option to do so? Thanks and Regards

  • Anonymous
    July 13, 2015
    I tried following this directions and I don't get any events in the ETL file if I trace the sample app, SSMS 2016, or logparser.exe My providers look like so: C:UsersjdearingDocumentsdeletemeSQL 2012 TracingMOF_Files>logman query providers | findstr SQL Microsoft-SQLServerDataTools             {79F618AD-4B02-4D46-A525-F5A93C551DDD} Microsoft-SQLServerDataToolsVS           {77142E1C-50FE-42CC-8A75-00C27AF955C0} MSSQLSERVER Trace                        {17531BCD-C627-46A2-9F1E-7CC920E0E94A} SQLBROWSER.1                             {FC9F92E6-D521-9C9A-1D8C-D8980B9978A9} SQLJDBC_XA.1                             {172E580D-9BEF-D154-EABB-83429A6F3718} SQLNCLI.1                                {BA798F36-2325-EC5B-ECF8-76958A2AF9B5} SQLSERVER.09                             {AB6D5EEB-0132-74AB-C5F5-B23E1644DADA} SQLSERVER.10                             {48D59D84-105B-00FA-6B49-03462F696737} SQLSERVER.11                             {B2A28C42-A7C2-1563-97CC-3BE49FDA19F9} SQLSRV32.1                               {4B647745-F438-0A42-F870-5DBD29949C99} My LogParser command looks like: "c:Program Files (x86)Log Parser 2.2LogParser.exe" "SELECT SUBSTR(Field1, 0, 2) AS CountryCode, SUBSTR(TO_STRING(Field2), 0, 5) AS Zip INTO ZipCodes  FROM 'c:UsersjdearingDownloadsus.txt'" -i:TSV -headerrow:OFF -o:SQL -oConnString:"Driver={SQL Server Native Client 11.0};Server=.;  Database=LogParserTest;Trusted_Connection=yes;" -cleartable -transactionRowCount:0  -ignoreMinWarns:OFF -new_console:c In addition, that sample C# code requires the 2005 version of the adventure works database.