June 15, 2020 at 8:38 pm
I want to see what Stored Procedures are run when users enter data in a 3rd party product we use. I would also like to see INSERTS & UPDATES to tables.
I set up a trace that I have used elsewhere with success, but in this case, it's not capturing any of the events I would expect in the Prod database. I see my own commands in the trace EXEC SP_WHO2, and all the normal activity in master & msdb
Do 3rd party products have a way to prevent their activity from being captured by a trace ?
June 16, 2020 at 1:20 pm
To my knowledge, no.
However, it's all about what events you're capturing and how they're making their calls. If you capture sql_batch_completed and they're using all stored procedures and/or prepared statements, you'll never see their calls. Same thing if you're capturing rpc_completed but they're doing everything through batches.
Also, filtering, if any, in your trace could play a factor.
To validate, run a query against sys.dm_exec_query_stats and sys.dm_exec_procedure_stats. You should see activity there from your 3rd party system. If not, something else might be up.
On a side note:
Strong recommendation, stop using trace and start using Extended Events. Zero new functionality has been added to trace since 2008. All new functionality is going into Extended Events. I get it. If all you want is queries, trace works. However, it puts more of a load on the system than Extended Events (substantially more), and, you're going to have to use Extended Events if you want to capture any of the modern functionality of SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2020 at 2:46 pm
An example is an INSERT TRIGGER that runs this statement.
execute @nReturn = SPName N'INS', @dDATE, @nREADING
But, If I run this, I see it in my trace:
DECLARE @nReturn varchar(100) execute @nReturn = sp_who2
Otherwise, I get practically no results searching the trace for SPs executed by the application's triggers, or the table name being Inserted/Updated.
I will have to look into extended events and catch up.
June 16, 2020 at 3:33 pm
Ah, triggers aren't technically batches or procedures. You'll have to go to statement level monitoring to capture that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2020 at 3:48 pm
Ah, triggers aren't technically batches or procedures. You'll have to go to statement level monitoring to capture that.
Can you elaborate ?
Shouldn't I see the commands that the triggers execute in the trace ? For example, if the trigger has an EXECUTE, INSERT or UPDATE ?
June 16, 2020 at 4:50 pm
That's the absolute fun thing about triggers. They're not part of the rpc_completed call from an app. They're also not part of the sql_batch_completed call from an app. They're run internally. So, capturing sp_statement_completed or sql_statement_completed would, in fact, capture the trigger. However, the outer wrapper, what you're capturing with the first two events, won't show the trigger.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2020 at 6:45 pm
How can I capture the events within the trigger ?
June 16, 2020 at 8:48 pm
Grant just told you:
So, capturing sp_statement_completed or sql_statement_completed would, in fact, capture the trigger.
June 17, 2020 at 12:44 am
I misunderstood. Any suggestions for other events I should capture at the same time ?
June 17, 2020 at 11:15 am
No. That should be all you need. I should point out, statement level captures can be quite large. So, you should make sure you filter the session to eliminate as much as possible. Minimum would be to only capture for a given database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 17, 2020 at 12:35 pm
Thanks. I started a new thread specific to my new questions about extended events.
https://www.sqlservercentral.com/forums/topic/reading-extended-events-file-suggestions
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply