Extended Event doesn't capture every rpc_completed

Question

I have a long running Extended Event session that captures rpc_completed events filtered for a few procs. It has found some that have run. But others it doesn't capture them when I know they have been executed. The reason I know this is because I look in the cache and find them called there.

I'm using SQL Server 2016. I'm filtering on object_name as the field name, equal_i_sql_unicode_string as the operator and the value minus the schema name. I've also modified the session to add procs that I know are used regularly and it captures those fine.

Why does it overlook and not capture some?

Answer 1

Things may not be in Extended Events data when you look for a few reasons

1. Something in your XE session filtering excluded the data. This would be hard to diagnose without your XE session code, and the code that didn't make it in there. I'm not asking for those details, mind you -- that's a tip of the iceberg thing that isn't a good fit for Q&A here.

2. When you set up XE sessions, you have the choice to lose single, multiple or no events. The GUI even warns you against choosing to not lose any.

3. Events may not have been dispatched to the file in the time specified. For instance, if you run a query to see if it goes in there, you may have to wait at least 30 seconds from it finishing until it's in the XE session data.

4. Depending on the target, and how much data is allowed to go into the target, the data you're looking for may have expired from the XE data.

5. For rpc_completed, it's possible that the rpc did not complete-d. To tackle that, you'd need to get rpc_starting, and then track causality in the XE session settings.

I'd probably want to cover sp_statement_starting and sql_batch_starting as well. As David Browne mentioned, catching things in the right place can be tricky:

An RPC is a query sent from the client, not embedded in a TSQL batch. A stored procedure can be run from a TSQL batch, or from another stored procedure and not appear as an RPC.

This isn't stuff that's easy to troubleshoot without looking at a server directly, but it should be enough for you to start your own investigation.