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 of Enabling Page Checksum and Default Trace

"Should I keep the database page checksum and the default trace enabled on SQL Server 2005?" That is a frequently-asked best practice question. Out of box, these two options are enabled. Although you can argue about the pros and cons of enabling or turning off these options, it is mostly an empirical question that should be answered with solid test data.

My instinct is to let these two options stay because of the potential benefits they provide. But when I was conducting some load tests recently, I noticed that for some load levels, SQLTrace_Buffer_Flush accounted for a significant percentage of the total resource waits. Since the SQL Server 2005 default trace was the only trace running, so it was responsible for the observed waits. Naturally, that got me a bit worried! So I decided to find out its performance impact through systematic tests. And while I was at it, I threw the database page checksum into the mix to provide more data points.

So, I wanted to test the following four configuration settings with everything else being equal and with identical workloads:

  1. Neither the database page checksum and the default trace was enabled,
  2. Only the database page checksum was enabled,
  3. Only the default trace was enabled, and
  4. Both the database page checksum and the default trace were enabled

For the test workloads, I used a TPC-C benchmark kit (no surprise if you read some of my earlier blog entries), but calibrated it to create the following two test scenarios:

  1. I/O bound workloads at various load levels where the load level was controlled by the number of simulated users,
  2. Balanced workloads for 100 simulated users. These workloads were balanced (or more balanced) in terms of their pressure on CPU, I/O, and memory, and were created by adding a separate 30-user CPU-bound workload to the 100-user I/O bound workload. The CPU-bound workload was introduced by running the read-only transactions to target a subset of the database that was smaller than the amount of physical memory given to the SQL Server 2005 instance.

The second test scenario was included because I didn't expect the two options to have much performance impact with an I/O bound workload.

The chart on the left summarizes the results from the first test scenario:

As you can see, with the I/O workloads, I didn't see any significant performance difference in the measured transaction throughput among the four configurations. And this was the case regardless of the load level.

Okay, how about a more balanced workload? The chart on the right shows the results of the second workload scenario where CPU, memory, and I/O were all stressed, though the load level was carefully controlled so that none was overly stressed (e.g. the four processors were kept at about 80% utilization).

Again, there was no significant performance difference in any of the four configurations.

This is very good news. If these results didn't lie and you have no specific counter reason, I'd suggest you leave the two options turned on.

I should mention that, in addition to the charts shown here, many more tests were actually performed. The results were similar. I tried hard to find a workload that might be sensitive to these two configuration options, and I have so far failed. That is not to say that you can't find one either.

So what's the story with the SQLTrace_Buffer_Flush waits I was worried about? It turns out that when SQL Server 2005 isn't stressed, there aren't many other resource waits, and the SQLTrace_Buffer_Flush waits stand out. It is reassuring to know that as the load level increases, the percentage of resource waits that SQLTrace_Buffer_Flush accounts for decreases.

 

Published Tuesday, January 16, 2007 11:56 PM by Linchi Shea

Attachment(s): Perfor2.gif

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

 

Andy said:

This is a very useful benchmark and one that should answer a lot of questions for people new to SQL2005.

January 17, 2007 9:36 PM
 

Glenn Berry said:

Very nice effort to answer the question based on empirical tests rather than just guessing

January 19, 2007 3:25 PM
 

Enterprise Software Development said:

Thank you for the benchmarks, I was looking at the latest MSDN Magizine (January 2008). They have an execelent article titled 'Uncover Hidden Data To Optimize Application Performance' ... When I ran there example query to 'query records causes of wait times' againist my dotNetNuke database, I noticed SQLTRACE_BUFFER_FLUSH was at the top.

Again thanks for the info about this metric.

December 10, 2007 11:33 AM
 

granit said:

Thank You...

March 14, 2009 2:27 PM
 

TRsohbet said:

tHANKs

May 3, 2009 6:07 AM
 

Sohbet Chat Sevgi Arkadaşlık said:

Thank You...

May 8, 2009 6:48 PM
 

Ask Sohbet Chat Sevgi said:

Thank You...

May 8, 2009 6:49 PM
 

Sohbet said:

hallo i wish you succes on your job thanks a lot

May 10, 2009 12:58 PM
 

Sohbet said:

hallo i wish you verry  succes operator

May 12, 2009 7:11 PM
 

Edencity Chat said:

hallo i wish you verry succes operator

May 22, 2009 8:35 PM
 

Sohbet said:

hallo i wish you verry  succes operator

June 2, 2009 6:44 PM
 

kaderimsin said:

hallo i wish you verry succes operator

November 22, 2009 11:22 AM
 

Brett Hawton said:

As CHECKSUM really affects processor usage your test (where processor usage was kept under roughly 80%) doesn't really show any negative impact CHECKSUM may have had as if it used say a few % more of CPU your transaction rate would be unaffected as your system had the spare processor "headroom" to handle this. It would have been a far better test to get your system to be CPU stressed with the IO subsystem totally relaxed. Get the CPU's maxed out at 100% with the IO subsystem idling without CHECKSUM turned on. NOW turn on CHECKSUM and you must see a reduction in the OLTP rate. This test will surely tell you the precise impact of turning on CHECKSUM.

July 20, 2010 11:32 AM
 

Setting Your Page Verify Database Option to CHECKSUM - Glenn Berry said:

October 16, 2014 11:16 AM

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