THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 – II

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.

Published Monday, December 12, 2011 10:58 PM by Linchi Shea
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

This is the third installment of my posts on the performance impact of enabling SQL Server 2008 R2 Audit.

December 13, 2011 9:52 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement