In my previous post on this subject, I showed the test results from a two-processor machine. Naturally, you may wonder: do we expect to see the same dramatic throughput drop when we enable a synchronous audit (i.e. with the queue_delay property set to 0) on a more powerful machine?
The answer is yes.
The following table shows the test results for the same three test cases except that (1) the test database was on an eight-processor machine, and (2) the workload was generated with 50 concurrent users.
|
Test Case |
Throughput (transactions/second) |
Total CPU usage |
|
1 |
14,500 |
~98% |
|
2 |
12,300 |
~98% |
|
3 |
500 |
~4% |
To refresh your memory, the three test cases are:
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 audit was on the SELECT statements applied to the three tables accessed by the test workload. With the queue_delay property set to 0 in the audit object, we observed a dramatic impact on the transaction throughput when we enabled the audit, a whopping 96% drop from 14,500 transactions/second to 500 transactions/second. With the two-processor machine, the throughput drop was about 95% from 3500 transactions/second to 160 transactions/second.
We also observed a much less decrease in the throughput when the audit was configured to use a 1000ms queue delay. With the two-processor machine, the performance drop was about ~11%. With the eight-processor machine, the decrease was about 15% (from 14,500 transactions per second to 12,300 transactions per second).
So the behavior in terms of the performance impact was similar whether it's on a two-processor machine or on an eight-processor machine.
I don’t know any practical requirement that may demand a synchronous audit. But since the feature is there in SQL Server and it is highly unlikely that it’s there by accident, I suspect such a requirement probably exists. I’m curious about it.
In both this post and the previous post, we are exploring the worst case scenario with the system already under heavy processor stress. In a future post, we’ll look at the performance impact of enabling the same audit setups when the processors are not the bottleneck. This would be a more realistic scenario.