SQL Server agent causes extensive audit logging activity (4376960)

SQL Server agent causes extensive audit logging activity

The Foglight agent monitors the SQL Server instance by querying its status regularly using various dictionary and management views (for example: sys.dm_exec_requests). The SQL PI "get active requests" collection runs by default once per second by default.

Querying these views sometimes translates to a "VIEW SERVER STATE" action on the part of SQL Server. This is documented in SQL Server Books Online and Foglight documentation as required permission.

In a monitored SQL Server host, there is an active audit which triggers a "VIEW SERVER STATE" event each times this happens. It began occurring much more frequently to several times every few hundred milliseconds in versions of SQL Server above 2008 R2 / Windows 2008 R2.
 

CAUSE 1
Parallel processing


CAUSE 2
Internal Microsoft processing of queries while auditing.

RESOLUTION 1

Check the MAXDOP for their instance in SQL Server Management Studio

SELECT value_in_use
FROM sys.configurations
WHERE name ='max degree of parallelism'; 

By default the value is 0.

The Active Requests query can be executed by engine optimizer in parallel workers (threads) unless the instance “max dop” is 1. Then if they are capturing events for each worker then it can look to them that query executed multiple times in one second when query executed only once.

When the Max DOP is set high (or 0 which is no limit), a single query might be executed across multiple processors, which can lead to multiple execution plans and operations being recorded in the audit logs. This could result in a higher number of audit entries if the operations or resource usage are logged.

 
RESOLUTION 2

This change in agent behavior was due to Microsoft's internal changes Microsoft in the underlying SQL Server server mechanism. Quest R&D recommends customizing the audit to ignore this event when triggered by the Foglight agent program and user.