[2008/08/25] This post has been modified significantly to correct some inaccurate statements because I mis-read Joe Chang's post. Joe Chang posted some interesting results using the TPC-H queries with the scale factor set to 10. I happened to have done something similar, and naturally noticed a rather significant difference between his results ...

In my previous post, I looked at how a typical OLTP workload may be affected by various controller cache configurations. And the conclusion was that giving too much cache (say all 512MB) to reads hurt the OLTP performance. The primary reason was that the writes from the OLTP workloads were starved of cache. Now, let's take a look at how the ...

In my previous post on the performance impact of controller cache configurations, I presented some empirical results showing the performance impact of configuring the controller cache to various read/write settings on large sequential I/Os.   Why did I single out large sequential I/Os? That's because large sequential I/Os are heavily used ...

In the next several blog posts, I’ll share with you some empirical results concerning the performance impact of configuring the read/write cache of a disk controller.   In the comments on Joe Chang’s blog at this site on Storage Performance for SQL Server, some statements were made concerning the performance impact of read cache in a disk ...

It is widely quoted that if a SQL Server database is set to Read Only (using ALTER DATABASE ... SET READ_ONLY), your queries may run faster because no locking occurs in a read-only database. This all makes sense conceptually. But I have not seen any empirical data to get a feel for the extent of the impact. And it's always good to have some solid ...

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 perspective. If you were led to believe that manual checkpoints were always bad, that wasn't ...

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 to take advantage of the throughput potential of the underlying storage. But I didn't ...

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 1SET NOCOUNT ON SET IMPLICIT_TRANSACTIONS ON DECLARE @i int SET @i = 1 WHILE @i <= 3145728 -- (1024*1024*3) BEGIN INSERT tbTest(i, ...

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 Working with tempdb in SQL Server 2005. The KB article lists the following as conditions that ...

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: In Microsoft SQL Server 2005, you may notice blocking ...