Post reply

Server Side Trace Issues

  • January 5, 2011 at 7:40 am

    #144274

    Hi - I was trying to figure out the usage on one of my tables and created a server side trace with filters of the Database and Object name, and with Events of SP:StmtCompleted and SQL:StmtCompleted.

    After the trace I viewed the trace file and found lots of entries that seemed to have no bearing on the object in question.

    I would have thought that only SQL would have been returned that contained a reference to my object but instead I find calls to SPs that don't reference my object, and also SQL:StmtCompleted lines with no reference to my object.

    I also find many BEGIN TRAN and COMMIT TRAN lines with code in between some of which have references to my object and others that don't.

    I tired to look up the object name from the object ID for some of these transactions but I am getting NULL returned.

    I am a little confused as to what profiler is returning here and any help in further understanding would be greatly appreciated.

    Many thanks

  • January 5, 2011 at 7:50 am

    #1269529

    Can you post the code for the trace script. Did you use profiler to create the trace script or do it manually?

    -

    Jason

  • January 5, 2011 at 7:53 am

    #1269532

    The object name in SP:StmtCompleted is the name of the stored procedure that the statement is part of. It will not be a table name. (what do you put as an object name for a query that joins 4 tables)

    If the objectname is blank, then it will show no matter what filters you have, the opposite of null handling in SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 5, 2011 at 8:23 am

    #1269547

    So that explains why I get lots of rows that seem irrelevant because the trace pulls back things it can't identify as having an object name.

    In fact in my trace no row has an object name so would you agree that in the window of data I captured that no reference to my table was made? Or is my filter incorrect as I am trying to filter on a table name where as I should be filtering on object IDs of procs, functions, views that contain references to my table?

    Thanks

  • January 5, 2011 at 9:36 am

    #1269596

    Kwisatz78 (1/5/2011)

    In fact in my trace no row has an object name so would you agree that in the window of data I captured that no reference to my table was made? Or is my filter incorrect as I am trying to filter on a table name where as I should be filtering on object IDs of procs, functions, views that contain references to my table?

    Both.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 6, 2011 at 2:44 am

    #1269943

    Hi again Gail

    Thinking about this then maybe I should only profile the table name adding the filter to the TextData column, seeing as Object name and ID relate to the containing SP and not the executed statement.

    Thanks

  • January 6, 2011 at 2:55 am

    #1269952

    Have just found this article which explains how to use the Audit Schema Object Access event class. This is a far better way of doing this as it shows failed calls also.

    http://www.databasejournal.com/features/mssql/article.php/3887996/Determining-Object-Access-Using-SQL-Server-Profiler.htm

Viewing 7 posts - 1 through 6 (of 6 total)

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