Search results matching tags 'performance' and 'Audit'

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

Linchi Shea — Wed, 14 Dec 2011 02:52:00 GMT

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
1	3200	2% ~ 50% (avg =~ 20%)
2	3200	2% ~ 50% (avg =~ 20%)
3	410	~3%

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.

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

Linchi Shea — Tue, 13 Dec 2011 02:58:00 GMT

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.

Performance impact: synchronous audit can trash your database performance

Linchi Shea — Sun, 11 Dec 2011 22:26:00 GMT

The SQL Server Audit feature introduced in SQL Server2008 is useful in many scenarios. Although it still has much room for improvement, it has made SQL Server auditing easier to manage, and in particular, it has reduced performance impact as compared with a similar audit setup using SQL Trace.

However, this does not mean that you should ignore performance considerations when configuring SQL Server Audit. In this post, I give you some empirical evidence to show that if you configure it incorrectly, you can still trash your database performance. In particular, I’ll focus on the impact of configuring the audit property called “queue delay”.

When you configure an audit object using SSMS Object Explorer, you can set the property in the textbox labeled as “Queue delay (in milliseconds)”. If you use the CREATE SERVER AUDIT statement, the option in the WITH clause is called Queue_Delay. By default, it is set to 1000ms. Generally speaking, the default setting is good enough, and you should not change it. At least, I have not found a case where a different value offers visible advantage.

But you can do serious damage if you set the queue delay property to 0, i.e. setting the audit to log data synchronously without any wait.

To demonstrate the damage, I ran a series of tests on a small server with two processors running SQL Server 2008 R2. The workload was OLTP, primarily on three user tables: customer, orders, and stock. I’ll skip the details about the workload as they do not change the general message of this post. If you are interested, I can give you all the details over email.

I tested three cases, all with 10 simulated users submitting transactions non-stop (i.e. without any wait between two consecutive transaction submissions for each user):

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 0ms

The following table summarizes the test results:

Test Case	Throughput (transactions/second)	Total CPU usage
1	3500	~97%
2	3100	~98%
3	160	~8%

First thing to note is that when the audit was configured and enabled with no queue delay at all, the negative performance impact was dramatic, causing the throughput to drop from ~3500 transactions/second (when no audit was enabled) to a mere ~160 transactions/second. In fact, when I first had the test going with no audit and the throughput was staying at 3500 transactions/second, and when I enabled the no-delay audit, I saw the throughput drop like a stone on the chart.

When there was no audit, the system was completely CPU bound with the total CPU usage pegged at ~97% and a processor queue was observed. When the no-queue-delay audit was enabled, the waits were observed to be primarily on the wait type XE_SERVICES_RWLOCK, and the total CPU usage dropped to ~8%. The system spent most of its time waiting on some extended event locks.

By the way, when I checked the exposed SQL Server performance counters under SQLServer:Wait Statistics, I saw all waits to be 0. In other words, there was no wait as far as the perfmon counters were concerned. Apparently, the XE waits were not reported under SQLServer:Wait Statistics in SQL Server 2008 R2.

From the above result table, it’s clear that when an audit with the queue_delay set to 1000ms was enabled, the transaction throughput dropped from 3500 to 3100, about 11% decrease in performance. This can be considered significant. However, it’s worth noting that I was testing a rather extreme performance scenario in which the total CPU usage was already driven to almost 100% and there was a clear and sustained processor bottleneck.

In real world scenarios, you probably should not turn on any audit to track busy activities when the system is already terribly bottlenecked on the processors. Good news is that even when the system is under extreme CPU load, enabling an audit (with the queue_delay set to 1000ms) that does not actually track a lot of data appears to have very little adverse performance impact. For instance, I re-configured the database audit specification to track all the DELETE statements (instead of SELECT) on the same three tables. Since the test workload did not perform any DELETE, the audit did not actually record any audit data. The throughout did seem to drop slightly to around 3450 transactions/second when this audit was enabled, a rather insignificant drop within the margin of observation error.

I’ll follow up with the test results that show the performance impact when the system under test is not bottlenecked on the processors.

I hope the message is loud and clear: do not configure an audit with the queue-delay property set to 0.