Monitoring SQL Database Activity-CodePudding

I deal with a 3rd party software with a client. That application has a SQL Server database I have access to. I need to figure out any cascading actions that occur to the database when I save an invoice within this software (would essentially insert into 1 table).

I have my own SQL Server that is linked to theirs, so I just need to determine any actions that occur if I were to insert into this table manually. I have looked into SQL Trace, but was curious if anyone has any input on the easiest way to find out all the actions associated with an action in this software within SQL.

CodePudding user response：

You can't know all the implications. They may have data-driven decisions made by applications that monitor the system. That's going to be beyond the purview of any kind of internal monitoring within SQL Server.

That said, if you want to know what happens when you run your query, probably the best bet would be to capture through Extended Events. I say that over Trace because you're going to want to capture events, and track causality, something Trace doesn't do. I also have a blog post describing what this is and how it works.

I'd suggest you capture sql_batch_completed, rpc_completed, sp_statement_completed, sql_statement_completed. That should let you know all the queries & statements associated with your INSERT. The causality tracking will tell you the order in which they occurred and group them all together.

CodePudding user response：

If you have access to SQL Server Management Studio to run a query on the system, you can turn on Include Actual Execution Plan on the toolbar, run the query, and examine the result.

Using code, you can do this by:

set statistics xml on

begin tran

insert into MyTable... -- your query goes here

rollback

set statistics xml off

This technique works well if the cascading actions are in some kind of trigger. The result will include the actual execution plan used when performing all cascading queries.

Unfortunately, if there is any asynchronous processing that happens after this record exists, the actual query plan will not include these events. For example, the query plan would not include the processing of SQL Broker events that were created when this row was inserted. It would also not help understand a polling processes that detects the new row and does something with it. Those processes would need to be traced using extended events or the (deprecated) SQL Profiler.