Post reply

Finding DML statements in stored procedures with SQL Profiler

  • July 24, 2008 at 4:20 pm

    #190203

    Hello,

    I'd like to use SQL Profiler to audit DML statements (UPDATE, INSERT, DELETE), but I am concerned that if someone has enclosed the DML in a stored procedure, I would not find it.

    For example, let's say I filter Profiler to find TextData that contains UPDATE, INSERT, or DELETE.

    Then someone calls this hypothetical procedure:

    mysp_processdata

    But in the procedure is this code:

    delete * from myTable

    I don't think the Profiler filter stated above would catch that procedure call, would it? Then I would not have any trace that the mysp_processdata procedure did the deleting.

    Is it correct that the Profiler filter wouldn't capture this delete? If so, is there any way to accurately audit all DML statements on a given SQL server, whether or not they are within a procedure?

    I want to capture what I need without generating giant trace files that result from not knowing the details of how smart Profiler is at filtering.

    Thanks for any help,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • July 24, 2008 at 10:38 pm

    #848192

    You can profile at the statement level, which I think will catch statements inside stored procedures.

  • July 25, 2008 at 3:16 pm

    #848708

    I would also like to know if this is possible?

  • July 25, 2008 at 4:05 pm

    #848720

    Steve Jones - Editor (7/24/2008)

    You can profile at the statement level, which I think will catch statements inside stored procedures.

    Thanks, Steve, this lead was very helpful! It took me a little while to find information on the web because I didn't see the term "statement level" used a lot in the context of SQL Profiler. But there were enough articles that allowed me to piece something together.

    For example, this link told me about the SQLTransation Event, including the EventSubClass

    http://www.themssforum.com/SQLServer/Transaction-count/

    Thanks again,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • July 25, 2008 at 4:23 pm

    #848722

    Dev (7/25/2008)

    I would also like to know if this is possible?

    Dev,

    Here is a trace template definition I used that you may want to try. (It's for SQL 2000 Profiler, but you can adjust for SQL 2005.)

    In SQL Profiler, create a new trace template.

    General tab

    Name and save the file.

    Events tab

    Add:

    Stored Procedures

    - SP:Starting

    - SP:StmtStarting

    - SP:StmtCompleted

    - SP:Completed

    Transactions

    - SQLTransaction

    TSQL

    - SQL:StmtStarting

    - SQL:StmtCompleted

    Data Columns tab

    Add:

    EventClass

    DatabaseID

    StartTime

    EndTime

    EventSubClass

    HostName

    Reads

    Writes

    SPID

    TextData

    ApplicationName

    LoginName

    Filters tab

    Check: Exclude system IDs (unless you really need them)

    Add any other filters you need to keep the row count appropriate - this kind of trace can generate lots of rows quickly if it is not filtered, so PLEASE try on a test server FIRST.

    Use the template in a new trace - Don't forget to select your new template from the dropdown list in the General tab (labeled Template name).

    I tested the trace template by running a trace that I filtered by the database ID of a database with low activity at the time. Then I ran a test procedure, and the trace file output a nice set of rows showing the SP starting, each statement within it starting and completing (in the TextData column), the begin and commit transaction points, and then the SP completing.

    Perhaps others can offer a more streamlined version, but I think this is a good starting point if you don't want stored procedures turning up in the trace without seeing the statements running inside. It is of course possible to see the code of the procedure by viewing its script to try to piece together what happened in real time, but seeing it all in the trace done for you is a great help.

    Good luck. Let me know if you find it useful, and if anyone has pointers or improvements, let me know. I'm especially interested in any advice on how to turn this kind of statement-level tracing into a server-side trace that can run all the time.

    Best regards,

    webrunner

    P.S. Also, check out these links:

    http://www.themssforum.com/SQLServer/Transaction-count/

    SQLTransaction Event Class (use this together with the SP and TSQL events and EventSubClass mentioned above)

    http://msdn.microsoft.com/en-us/library/ms186239.aspx

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply