1

I use a SQL Server Profiler trace template called "Procs completed by me", that include this text filter: %@UserID=123%.

enter image description here

Almost all the stored procedures run by our website include a @UserID parameter, so I can click away and see the effect on the database. And it ignores all the stored procedures run by other website users. Incredibly useful!

How do I create the same thing in extended events?

The answer (from David below) is

CREATE EVENT SESSION XE_ProcsRunByMe
ON SERVER
ADD EVENT sqlserver.rpc_completed (WHERE sqlserver.like_i_sql_unicode_string(statement,N'%@UserID=123%'))
Craig
  • 4,111
  • 9
  • 39
  • 49
  • See [Humanevents](https://www.erikdarlingdata.com/sp_humanevents) – Stu Mar 22 '21 at 14:27
  • @DavidBrowne-Microsoft like_i_sql_unicode_string seems to work. I just need to include %. Username will not work because ever website user (1000s of them) connect to the database with the same db user. – Craig Mar 22 '21 at 14:55

1 Answers1

3

In the session window:
In the list on the left go to Events, and under Event library: there are the rpc_ events.
For the user, in the same window go to Configure on the right, then select an actual event under Selected events: on the left (if you have more than one listed, as each must be set up), then on the right choose Filter (predicate), and under Field check for the appropriate value (I can't check it now, I used to filter on my machine with sqlserver.client_hostname and to reduce footprint also on sqlserver.database_name). If the user is not the same according to SQL server user mapping, than filter on the statement instead (there should be something like statement in the list, but might depend on SQL server version).

Dávid Laczkó
  • 1,091
  • 2
  • 6
  • 25
  • 1
    Yes, that worked. Thanks. This is the SQL I used: CREATE EVENT SESSION XE_ProcsRunByMe ON SERVER ADD EVENT sqlserver.rpc_completed (WHERE sqlserver.like_i_sql_unicode_string(statement,N'%@UserID=123%')) – Craig Mar 22 '21 at 14:47