none
Get Activity ID with System.Data.1 in ETW? RRS feed

  • Question

  • Hi,

    I'm trying to trace all SQL queries via ETW & the System.Data.1 event provider. I can capture all the queries, but I can't get their activity IDs. I wanted to grab all the queries per activity, so I could correlate queries and http requests together. Right now I have no context for where the queries were executed.

    Thanks.

    Oh, and here's what I DO have:

    <event name="TextW" description="System.Data.1 formatted output (W)" timestamp="129806163839609903" threadId="6876" type="18" level="0" guid="914abde3-171e-c600-3348-c514171de148" flags="None">
          <properties>
            <ModID>4</ModID>
            <msgStr>&lt;sc.SqlCommand.set_CommandText|API&gt; 16705#, '</msgStr>
          </properties>
        </event>
        <event name="TextW" description="System.Data.1 formatted output (W)" timestamp="129806163839609959" threadId="6876" type="18" level="0" guid="914abde3-171e-c600-3348-c514171de148" flags="None">
          <properties>
            <ModID>4</ModID>
            <msgStr>declare @BatchID uniqueidentifier
    
                                                set @BatchID = NEWID()
    
                                                UPDATE [Event] WITH (TABLOCKX)
                                                    SET [BatchID] = @BatchID,
                                                    [ProcessStart] = GETUTCDATE(),
                                                    [ProcessHeartbeat] = GETUTCDATE()
                                                FROM (
                                                    SELECT TOP 4 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
                                                    ) AS t1
                                                WHERE [Event].[EventID] = t1.[EventID]
    
                                                select top 4
    	                                            E.[EventID],
    	                                            E.[EventType],
    	                                            E.[EventData]
                                                from
    	                                            [Event] E WITH (TABLOCKX)
                                                where
    	                                            [BatchID] = @BatchID
                                                ORDER BY [TimeEntered]</msgStr>
          </properties>
        </event>
        <event name="TextW" description="System.Data.1 formatted output (W)" timestamp="129806163839609973" threadId="6876" type="18" level="0" guid="914abde3-171e-c600-3348-c514171de148" flags="None">
          <properties>
            <ModID>4</ModID>
            <msgStr>'</msgStr>
          </properties>
        </event>

    • Moved by Karel ZikmundMicrosoft employee Monday, May 7, 2012 5:55 AM Either general ETW question, or specific ETW provider System.Data question? (From:Building Development and Diagnostic Tools for .Net)
    • Moved by Mike Dos Zhang Friday, June 15, 2012 7:40 AM ADO.NET provider question (From:General Windows Desktop Development Issues)
    Friday, May 4, 2012 3:16 PM

Answers

  • Can you find out what version of System.data.dll you are using and post back?

    I tested this with .NET 4.0.3 (downloaded latest 4.0.3 update) and I see following traces:

    <sc.SqlConnection.Open|API|Correlation> ObjectID1#, ActivityID 61039d83-93ad-449a-9c31-007ee560626a:1

    <sc.SqlCommand.ExecuteScalar|API|Correlation> ObjectID2#, ActivityID 61039d83-93ad-449a-9c31-007ee560626a:4

    Here is how I started/stopped tracing:

    Copy below to notepad and save as StartAdoTrace.cmd =>

    @echo off
    echo Starting ADO tracing...
    echo {914ABDE2-171E-C600-3348-C514171DE148}  0xFFFFFFFF  128   System.Data.1 > providers.txt
    echo {C9996FA5-C06F-F20C-8A20-69B3BA392315}  0xFFFFFFFF  128   System.Data.SNI.1 >> providers.txt
    reg add HKLM\Software\Microsoft\BidInterface\Loader /v :Path /t REG_SZ /d "%systemroot%\system32\msdaDiag.dll" /f
    reg add HKLM\Software\Wow6432Node\Microsoft\BidInterface\Loader /v :Path /t REG_SZ /d "%systemroot%\syswow64\msdadiag.dll" /f
    del /Q *.etl
    logman create trace -n AdoTrace -ct perf -pf providers.txt -bs 10000 -nb 10000 50000 -o AdoTrace.etl -max 10 -cnf 00
    logman start -n AdoTrace
    echo Run StopAdoTrace.cmd to stop tracing.

    Copy below to notepad and save as StopAdoTrace.cmd =>

    @echo off
    echo Stopping ADO tracing...
    logman stop -n AdoTrace
    logman delete -n AdoTrace
    reg delete HKLM\Software\Microsoft\BidInterface\Loader /v :Path /f
    reg delete HKLM\Software\Microsoft\Wow6432Node\Microsoft\BidInterface\Loader /v :Path /f
    echo ADO tracing stopped, collect and zip all AdoTrace_NNNNN.etl files


    Matt

    Wednesday, June 20, 2012 7:21 PM
    Moderator

All replies

  • I'm not very familiar with the ETW, I will try to involve others senior engineer to help you.

    Best wishes,


    Mike Zhang[MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, May 8, 2012 7:49 AM
  • Thank you so much!
    Tuesday, May 8, 2012 12:46 PM
  • You're welcome!

    Have a nice day!


    Mike Zhang[MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, May 9, 2012 3:26 AM
  • Hi Mike / System.Data team,

    Any thoughts on this issue? We are really stumped and need to figure out the solution ASAP. Appreciate your help here.

    Best,

    Mike


    Mike Volodarsky

    Wednesday, May 16, 2012 6:06 AM
  • I really want to work this question out, but I'm really not familiar with this technical aspect, and I involved others senior engineers to research this question, they maybe have not got an idea so there's no update.

    Best wishes,


    Mike Zhang[MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, May 16, 2012 6:32 AM
  • Hi,

    One senior engineer who is research this thread told me that, the thread seems hard, and let me move it to the provider forum since it is this aspect question.

    The senior engineer will reply to you once an idea is gotten which can help you.

    Best wishes,


    Mike Zhang[MSFT]
    MSDN Community Support | Feedback to us

    Friday, June 15, 2012 7:43 AM
  • Can you find out what version of System.data.dll you are using and post back?

    I tested this with .NET 4.0.3 (downloaded latest 4.0.3 update) and I see following traces:

    <sc.SqlConnection.Open|API|Correlation> ObjectID1#, ActivityID 61039d83-93ad-449a-9c31-007ee560626a:1

    <sc.SqlCommand.ExecuteScalar|API|Correlation> ObjectID2#, ActivityID 61039d83-93ad-449a-9c31-007ee560626a:4

    Here is how I started/stopped tracing:

    Copy below to notepad and save as StartAdoTrace.cmd =>

    @echo off
    echo Starting ADO tracing...
    echo {914ABDE2-171E-C600-3348-C514171DE148}  0xFFFFFFFF  128   System.Data.1 > providers.txt
    echo {C9996FA5-C06F-F20C-8A20-69B3BA392315}  0xFFFFFFFF  128   System.Data.SNI.1 >> providers.txt
    reg add HKLM\Software\Microsoft\BidInterface\Loader /v :Path /t REG_SZ /d "%systemroot%\system32\msdaDiag.dll" /f
    reg add HKLM\Software\Wow6432Node\Microsoft\BidInterface\Loader /v :Path /t REG_SZ /d "%systemroot%\syswow64\msdadiag.dll" /f
    del /Q *.etl
    logman create trace -n AdoTrace -ct perf -pf providers.txt -bs 10000 -nb 10000 50000 -o AdoTrace.etl -max 10 -cnf 00
    logman start -n AdoTrace
    echo Run StopAdoTrace.cmd to stop tracing.

    Copy below to notepad and save as StopAdoTrace.cmd =>

    @echo off
    echo Stopping ADO tracing...
    logman stop -n AdoTrace
    logman delete -n AdoTrace
    reg delete HKLM\Software\Microsoft\BidInterface\Loader /v :Path /f
    reg delete HKLM\Software\Microsoft\Wow6432Node\Microsoft\BidInterface\Loader /v :Path /f
    echo ADO tracing stopped, collect and zip all AdoTrace_NNNNN.etl files


    Matt

    Wednesday, June 20, 2012 7:21 PM
    Moderator
  • Hi Matt, thanks for the reply! I'm still getting an empty GUID for activity id, even with your script. It SHOULD be using version 4.0.30319.237 of System.Data.dll. That's what is in the GAC. If you're getting activity IDs though, maybe I should just try this on a fresh machine.. how strange.

    Thursday, June 21, 2012 2:21 PM
  • What version of SQL Server are you targeting as well?   Also note for your scenario, this activity id might not work.  The ADO.NET provider will not flow through the activity id from ASP.NET or IIS or SharePoint.  Instead, what it does is it will create it's own activity id on the current thread and then will flow this through to SQL Server, where you can trace it using xevents.   But the only SQL Server version that supports this flow through of activity id is SQL 2012.


    Matt

    Thursday, June 21, 2012 3:54 PM
    Moderator
  • We're targetting SQL Server 2008. Do you mind if we start an email chain? I'd like to dial down to some more detail :) nate@leanserver.com. Thanks!
    Thursday, June 21, 2012 4:44 PM