Profiling Linked Server

Question

I'm trying to optimize a set of stored procedures. These stored procedures are on Server A and do the following actions:

• Delete data on Server B based on a parameterized query on Server A

  DELETE FROM B (via linked server)
  DO SOME JOIN ON A
  WHERE A.x IN (@ID1, @ID2, @ID3, @ID4)

• Insert data on Server B based on a parameterized query on Server A

  INSERT INTO B (via linked server)
  DO SOME JOIN ON A
  WHERE A.x IN (@ID1, @ID2, @ID3, @ID4)

When I try to profile the stored procedures, I don't get a useful trace file. When I trace Server A, I just see the execution of the stored procedure, but none of the internal queries of the stored procedure, because they are refering to Server B through a linked server. When I trace Server B, I see no queries related to the stored procedure on Server A, I think that's because the stored procedure was actually executed on Server A.

In short: How can I trace these queries as part of the stored procedure on Server A, to optimize the queries exectuted on Server A and B?

Answer 1

you have to trace the Stored Procedure: SP:StmtStarting and SP:StmtCompleted events on server A and RPC:Completed on server B