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 ...

  A while back, I was asked which of the following two bulk insert options would be faster: Copy/Bulk Insert: First copy the data file from server B to server A, and then bulk insert the data into a table on server A Bulk Insert across Network: Directly bulk insert the data from server B into the table on server A Intuitively, the ...

My reaction was ''Wow!'', when I first saw the test results. I had to triple check the results and repeat the tests several more times in random order to make sure that I didn't make a mistake and that the results weren't a fluke. Good news is that the results are highly reproducible! The results I'm excited about are from the tests I conducted ...

SQL Server 2005 supports placing read-only filegroups or read-only databases on NTFS compression. In other words, you can compress the database files in a read-only filegroup or a read-only database. This can be a very useful feature if saving disk storage is of high priority. But what are the performance implications of using this SQL Server ...

In Windows Server 2003, you can use the Disk Management console to create a striped volume over multiple dynamic disks (well, you can also create a mirrored, a RAID-5 volume, etc). If these disks (or LUNs) are presented from a SAN, most likely you can stripe across the same storage devices--making up these LUNs--inside the SAN to present ...

Whether you are load testing SQL Server or evaluating the I/O performance of a storage subsystem, you need to make sure that you cover the entire spectrum of the load levels--from light and moderate all the way to heavy and saturated--before you draw any conclusion. This is especially important when you are doing performance comparison. Relying on ...