Sql-server – Extended Events filtering

I would say you don't need all the start events, just the completed. I don't see that you are capturing data just for the SCOM database either. Extended events can cause an overhead just near to what SQL Server traces will depending on what you are trying to capture.

You are basically capturing every query that comes across the server and telling it to wait so you can first check the text of the query and then grab information through the extended event session, then letting it complete.

As well the constant activity SCOM has, depending on environment configuration, it is likely to have an extreme performance hit either with trace or extended events. I would suggest looking at the plan cache for those types of queries initially. However you might try adding more filters to your session such only grabbing a sample of data from active sessions and then also only capturing data for the particular database. That would look something like this through the GUI:

Or code your WHERE clause would look something like:

WHERE ((([package0].[divides_by_uint64]([sqlserver].[session_id],(5))) 
   AND ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)))) 
   AND ([package0].[equal_boolean]([sqlserver].[is_system],(0))))) 

This would capture I think 20% of the active sessions for that database at any given time. However if you want all events then you would have to suffer the overhead of that action.

With it being a Microsoft product I would not be to concerned with what queries it is producing. If it is experiencing performance issues you should follow Microsoft guidelines for the product or consult Microsoft support.

Of course there will be an increase on CPU, as any other process on the server. But Extended Events are recommended precisely due to the low resource needs of running them. Using Extended Events to capture information takes much fewer resources than using the long old known profiler tool for example. Use it wisely of course, don't setup and run thousands of sessions to capture tons of data, then you can have a problem. We have used Extended Events on our own servers for auditing different processes and from our experience, we almost didn't saw a measurable increase on CPU activity. Yes, it takes some disc space to record all data it gathers, but again, think ahead and plan were to save it in order to affect as less as possible production environment.

As for 2nd question: it depends, as usual. Each system, platform and configuration are different, none is exactly to the other. So, if not possible to tell you how much RAM, CPU, disk space or IO will gonna take. But certainly will not be so much. Again, use common sense, don't start hundreds of sessions capturing gazillion of data, because then obviously you will have a penalty on performance.

For more detailed info check here, here here and here.