August 2007 - Posts

• How to Corrupt a SQL Server 2005 Database

  How can you corrupt an online SQL Server 2005 database? Okay, why would you want to do that? Well, let's say because you want to test out some DBCC commands. If you take a SQL Server 2005 database offline, you can easily corrupt it by opening it with Read More... Posted Wednesday, August 29, 2007 11:35 PM by Linchi Shea | 2 Comments Filed under: Cluster, SAN, Storage

• Performance Impact: the Most Optimal Insert Script can't Beat BulkCopy

  With the insert script and the test configurations in my previous posts , the best data load throughput was 24GB in ~7 minutes when the checkpoint (and/or transaction commit) batch size was set to 100,000 ~ 1,000,000. That was the best result when I was Read More... Posted Monday, August 27, 2007 11:46 AM by Linchi Shea | 6 Comments Filed under: Best Practices, Performance, Storage Attachment(s): bulkcopy.gif

• Performance Impact: Finding the Most Optimal Batch Size

  In my previous posts ( 1 , 2 , 3 ), I focused on the performance behavior of setting the checkpoints and transaction commit sizes to once every 16 inserts and once every 100,000 inserts. A question remains: what is the most optimal size? In other words, Read More... Posted Thursday, August 23, 2007 4:44 PM by Linchi Shea | 1 Comments Filed under: Best Practices, Performance, Storage Attachment(s): checkpoint4.gif

• Performance Impact: Manual Checkpoints are not Necessarily Evil

  In my two previous posts on the performance impact of frequent manual checkpoints and the I/O behavior of frequent manual checkpoints , I demonstrated that frequently issuing manual checkpoints can be bad for performance and why it's bad from the storage Read More... Posted Monday, August 20, 2007 3:27 PM by Linchi Shea | 1 Comments Filed under: Best Practices, Performance, Storage, Testing Attachment(s): checkpoint3.gif

• Performance Impact: Frequent Manual Checkpoints and Their I/O Behavior

  In my previous blog post on the performance impact of frequent manual checkpoints , I highlighted the performance peril of going overboard with manual checkpoints, and I suggested that a major contributing factor was the failure of frequent manual checkpoints Read More... Posted Friday, August 17, 2007 11:57 PM by Linchi Shea | 5 Comments Filed under: Best Practices, Performance, Storage, Testing Attachment(s): checkpoints2.gif

• Performance Impact: Frequent Manual Checkpoints

  Recently, I was asked why the following script took a very long time to insert 24GB of data into a single table in SQL Server 2005 (the database was in the simple recovery mode): -- Name: Script 1 SET NOCOUNT ON SET IMPLICIT_TRANSACTIONS ON DECLARE @i Read More... Posted Monday, August 13, 2007 1:21 AM by Linchi Shea | 7 Comments Filed under: Best Practices, Performance, Testing Attachment(s): checkpoints.gif

• Reduce the Contention on tempdb with Trace Flag 1118: Take 2

  My earlier attempt to see what's going on with trace flag 1118 on SQL Server 2005 SP2 (9.00.3042) didn't take me too far because the results were not conclusive. I was motivated by seemingly conflicting suggestions from KB article Q936185 and whitepaper Read More... Posted Friday, August 10, 2007 9:12 AM by Linchi Shea | 4 Comments Filed under: Best Practices, Performance, Testing Attachment(s): T1118_2.gif

• Reduce the Contention on tempdb with Trace Flag 1118: To Enable, or Not to Enable?

  A recently published KB article Q936185 seems to contradict the recommendation of using trace flag 1118 and multiple tempdb data files with equal sizing to reduce the contention on tempdb object allocation in SQL Server 2005. The article states the following: Read More... Posted Tuesday, August 07, 2007 7:34 AM by Linchi Shea | 5 Comments Filed under: Best Practices, Performance, Testing Attachment(s): Trace1118.gif

• Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

  Adam Machanic asked me whether I had done any tests to compare the performance impact of using SQL Profiler on the client side and that of using SQL Trace on the server side. This is an interesting question because the frequently heard recommendation Read More... Posted Wednesday, August 01, 2007 6:11 PM by Linchi Shea | 13 Comments Filed under: Best Practices, Performance Attachment(s): Profil1.gif