0

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?

Improve this question
0

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.

Improve this answer
5
  • The issue with inserting into the table is that the account calling the proc would need write permissions on the table, which might be difficult to track down and if I mess up could cause big issues in production. Perhaps wrapping what you suggested in a TRY/CATCH block and calling sp_send_db_mail using one of the mail profiles in the CATCH would solve that issue though. EDIT: I should also specify that this is assuming the account that calls the proc has no permissions on the table; if they have EXECUTE on the proc itself though I believe that would be covered. – Dylan Janszen Dec 21 '20 at 23:12
  • @DylanJanszen Yes there's a multitude of things you could do in the procedure to track it. In regards to permissioning on the table, not sure if you're using Active Directory in which case you can just GRANT INSERT ON TableName TO [DOMAIN USERS]. Otherwise you can do the same for any / all of the SQL Server Logins used, or to the db_datareader role (or similar basic role all users would be a part of), or to a custom schema the Table lives in. – J.D. Dec 21 '20 at 23:21
  • 1
    Ahh yes domain users would work. I guess that doesn't cover SQL accounts though right? So perhaps TRY/CATCH is still a good idea for the insert? – Dylan Janszen Dec 21 '20 at 23:23
  • @DylanJanszen I prematurely submitted my comment, sorry about that. I just updated it with a number of ways to account for all Logins (Windows or SQL Server based). If you miss an account then they'll receive an error when the exception is thrown as is. If this isn't sufficient (e.g. you can't rely on the user to let you know when they receive an error) then I'm not sure a TRY/CATCH will help either unfortunately since that would be internal to the stored procedure which is unable to execute due to permissions. – J.D. Dec 21 '20 at 23:28
  • @DylanJanszen Upon further research, you can just grant the permission on the table to all users who have login access via this line of code: GRANT INSERT ON TableName TO PUBLIC. This is probably the most simple solution which leverages the default server level "PUBLIC" role. – J.D. Dec 21 '20 at 23:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.