Performance impact of Extended Events sessions looking at the exact same event type

Posted on

Question :

I have created an extended events session that watches the module_start event type and filters down based on the object_name: equal_i_sql_unicode_string]([object_name])

The purpose of this session is to simply record basic information whenever a proc in the filter list is called so that I can answer developers questions with 99.9% guarantee whether or not a proc is still called in production. The idea is to run this for ~1 month 24/7 (yes it doesn’t account for things that are ran annually, but it is what it is).

The issue I am running into is that the list of procs the developer gave me is about 90 or so long and the filter list of an EE session is limited to 3,000 characters. The only idea I have come up with in order to increase the rate at which we can track the procs is to have 2 separate EE sessions that are identical except the filter predicates are different.

I am not asking “how much of a CPU impact will this be”, but more or less is their any concern with running 2 of the same EE sessions with different filter predicates? It is odd to me that Microsoft would limit the filter list to 3,000 characters when ‘more filtering == better performance’ because the way EE is built into the engine it is very optimized unlike a trace that acts more like a proxy than a “trigger based off an event”.

Is it safe to assume that whatever the performance impact of running 1 session is I can multiply it by 2 or is their further concerns I am not considering?

Answer :

I’m not an expert on Extended Events by any means but I do know there is potential for performance implications if not done carefully. You’ll only really be able to find out by testing and monitoring throughout the day if there’s any meaningful impact on your current server / database’s workload. I would think by filtering it down to specific entities which aren’t necessarily even being used might be ok though.

An alternative idea is to modify each stored procedure such that it inserts into a table the name of the procedure and the current datetime.

For some other alternative solutions you can see this StackOverflow post’s answers.
I would be careful using sys.dm_exec_procedure_stats if you go that route though, as it isn’t 100% guaranteed to give you the results you’re looking for, if an event has cleared out the plan cache.

Leave a Reply

Your email address will not be published. Required fields are marked *