Extended Events: Filter on Stored Procedure Name

I just received a question about Extended Events: What about filtering on the stored procedure name. You know I love writing and talking about Extended Events.

The answer is, well, sure, we can do that. However, as with all things, there may be wrinkles worth being aware of. Let’s examine this.

Filter on Stored Procedure Name

Let’s create an Extended Event session that captures rpc_starting and rpc_completed:

CREATE EVENT SESSION StoredProcedureName
ON SERVER
    ADD EVENT sqlserver.rpc_completed
    (ACTION
     (
         sqlserver.database_name
     )
     WHERE (object_name = N'AddressByCity')
    ),
    ADD EVENT sqlserver.rpc_starting
    (SET collect_statement = (1)
     ACTION
     (
         sqlserver.database_name
     )
     WHERE (object_name = N'AddressByCity')
    );

I’ve added a WHERE clause to the Extended Event to capture only those procedures that have an object_name equal to ‘AddressByCity’.

If we look at the output from these events, you can see that we’re capturing exactly what we intended to:

That was easy. Done.

Yeah, it was. However, we’re not quite done.

Different Databases

Now, what happens with the above Extended Event session if we had, say, another database and that database had a stored procedure with the same name? Well, it just so happens, I have a second database that has a procedure with the same name. The output from our Extended Event session now looks like this:

These two procs share a name, but that’s all they share. Now we’re capturing two different sets of behaviors instead of just the one we wanted. In this case, I’m going to need to modify the session to include more filtering:

CREATE EVENT SESSION StoredProcedureNameAndDB
ON SERVER
    ADD EVENT sqlserver.rpc_completed
    (ACTION
     (
         sqlserver.database_name
     )
     WHERE (
               object_name = N'AddressByCity'
               AND sqlserver.database_name = N'AdventureWorks'
           )
    ),
    ADD EVENT sqlserver.rpc_starting
    (SET collect_statement = (1)
     ACTION
     (
         sqlserver.database_name
     )
     WHERE (
               object_name = N'AddressByCity'
               AND sqlserver.database_name = N'AdventureWorks'
           )
    );

With this, I can be sure that I’ve got the right procedure.

Now, one additional thought here. Does the filter order matter? Could we put the database name in front of the procedure name? If we did, would it affect the behavior of the session?

Shortest possible answer, I don’t know.

As a general rule, I’d put the most restrictive filter first. The order does matter. Extended Events will filter in the order you provide. So, is filtering on procedure name, then database name, more restrictive than the reverse? Depending on the system and the procedure in question, yes, but it’s hard to be sure. Testing is your friend here.

Conclusion

There are lots of choices and options when it comes to setting up your Extended Events sessions. It’s a good idea to understand what you’re going to be doing within those sessions though. Think through the events you’re capturing and how you’re choosing your filter criteria. It does matter. However, as you can see, a filter on stored procedure name is simple and effective.