THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: synchronous audit can trash your database performance – III

This is the third installment of my posts on the performance impact of enabling SQL Server 2008 R2 Audit. My previous two posts (part I and part II) show that whether it’s on a two-processor machine or an eight-processor machine, enabling an audit with the queue_delay property set to 0 can destroy the database performance. In particular, those two posts report the test results in the worst case scenario when the system was already under severe processor stress.

In this post, I highlight the results of running the tests when the system was under moderate load on the same eight-processor machine. The workload was the same modified version of the TPC-C benchmark with the two read-only transactions: OrderStatus and StockLevel, and with the entire database cached in memory. Note that in the test scenarios of the two previous posts, the simulated users did not wait before submitting next transaction, and that forced the system under test into a severe processor bottleneck. To achieve a moderate load level, I used the same test database and simulated 100 users, but with each of the users waiting for 10ms after the completion of the current transaction and before submitting the next transaction.

This 10ms wait between two consecutive transactions resulted in the following performance behavior on the server:

  • The total CPU usage fluctuated between 2% and 50% with an average of roughly 20%.
  • The transaction throughput in terms of the batch requests per second was steady at about 3200.
  • There was no processor queue, no queue on the processors, and no signal wait on the schedulers.

And when I pushed the system harder either with more simulated users or with a reduced wait time between consecutive transactions, I obtained higher transaction throughput until it saturated at about 14,000 transactions per second. So the load level on the system was very moderate with a lot of capacity to spare.

Again the same three test cases were examined:

1.       Without any audit setup,

2.       With an audit tracking all the SELECT statements on the three tables. The queue delay was set to 1000ms (the default value),

3.       With an audit tracking all the SELECT statements on the three tables. The queue delay was set to 0

The following table summarizes the test results for each of the three test cases:

Test Case

Throughput (transactions/second)

Total CPU usage



2% ~ 50% (avg =~ 20%)



2% ~ 50% (avg =~ 20%)





Bad news is that we still observed severe performance impact when we enabled the audit with the queue_delay property set to 0. The throughput dropped from 3200 transactions per second to 410 transactions per second.

However, good news is that when the queue_delay property was left at the default value of 1000ms, we did not see any visible performance degradation at all when we enabled the audit. And since we do expect the default setting to be used in most, if not all, the real world environments, this observation is most relevant and reassuring. In fact, this should be the key takeaway.

Published Tuesday, December 13, 2011 10:52 PM by Linchi Shea
Filed under: ,


No Comments
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement