Some servers added to DBHistory.com show a high level of unexpected activity, some reporting thousands of DDL events per day despite no changes being actually deployed on them. After collecting data over a longer period, it had become clear that two DDL events truly stand out in this regard:
- UPDATE STATISTICS events reported when running sp_updatestats.
- CREATE/ALTER/DROP EVENT SESSION events related to Microsoft telemetry XEvents session.
In fact the two categories above accounted for more than 75% of all events reported to DBHistory.com. These events can overwhelm the analysis of what changes actually occurred on a server.
Now you can define filters in your DBHistory.com account. Creating a filter allows you to ignore certain events reported to DBHistory.com. Events that satisfy a filter are ignored and not stored. To add an event filter expand the Show More option on the left hand navigation bar and click on the Event Filters. The following video shows how to add a filter that will ignores all events that occur on tempdb:
Event Filters allow you to specify any combination of event type, object type, object location (server, database, schema) and object name to define what events are filtered. If a field is left blank then the filter will match any value for that field. All field values are case sensitive, even for events originating from case insensitive collation SQL Server instances.
Event Filters allow you to specify also as action Include Event. This is intended to allow specific events to be captured when another, more generic, filter would normally disqualify the event. An example would be a generic filter that causes all UPDATE STATISTICS events to be dropped and a specific Include Event type filter that specifies event type UPDATE STATISTICS and object name ImportantTable. This combination will ignore all update statistics events except for the update statistics events on the ImportantTable, which will be captured.