February 21, 2007 at 7:37 am
I'm a bit flabbergasted. I have set up traces in the past using profiler in SQL 2000, so I think I know how to use this tool. Now, for a first time, I wish to track for specific objects, 2 tables, if and how many times they are called. And I can't get this working...
What I did first is setting up a trace where I select TSQL: STMT Completed. In the filter I set the ObjectID Equals the ID of the object I wish to trace. I do this in SQL Management Studio. As soon as I start the trace I see an enormous amount of activity, which I don't expect: the object to be traced is presumed not to be used anymore. On checking the output I notice, that this specific object is NOT, repeat NOT, used in the lines shown in my output. To be sure, when I do a SELECT * FROM table on this specific table I can see it show up in the output, correctly so. But how are the other hits generated, when they do not conform to the filter of a specific ObjectID? Notice, that NO ObjectID is shown in the output, not even from my SELECT statement...
Next I change the events I wish to so, I choose from OBJECTS: ObjectOpend. I use the same filter on ObjectID. Now I see not a beep in my output, even if I do a SELECT * FROM table on this specific object...
I have tried the same with Profiler in SQL 2000, same results...
What am I missing here?
Greetz,
Hans Brouwer
February 21, 2007 at 4:56 pm
Object_id is not applicable to SQL:stmtStarting or ending. You can accomplish this by putting a filter on the TextData column. So something like "SELECT%FROM%TableName%" would probably do the trick. Minus the quotes, of course...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 23, 2007 at 12:45 am
Right, I will try that then. Out of curiosity: what does ObjectID here refer to? Funny thing is, when I did a Select * from Table it showed up in my trace. That's why I was focused on ObjectID.
Greetz,
Hans Brouwer
February 23, 2007 at 8:32 am
I believe it applies only to RPC and will return the objectId of the stored procedure that was called.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply