Browse by Tags

• Architecture
• Benchmark
• Best Practices
• Queues
• SAN
• SQL Server 2005
• Storage
• Waits

• Large Query Performance and Query Parallelism

  [ 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 Read More... Posted Sunday, August 24, 2008 2:32 PM by Linchi Shea | 10 Comments Filed under: Performance, Testing, SQL Server 2005, Benchmark

• Performance impact of controller cache: SQL Server read only workloads

  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 Read More... Posted Tuesday, April 01, 2008 12:45 AM by Linchi Shea | 3 Comments Filed under: Performance, Testing, Storage Attachment(s): sql_reads.gif

• Performance impact of controller cache: SQL Server large operations

  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 Read More... Posted Thursday, March 27, 2008 12:23 AM by Linchi Shea | 5 Comments Filed under: Performance, Testing, Storage Attachment(s): sql_queries.gif

• Performance impact of controller read cache: large sequential I/Os

  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 Read More... Posted Monday, March 24, 2008 1:14 PM by Linchi Shea | 8 Comments Filed under: Performance, Testing, Storage Attachment(s): large_sequential.gif

• Performance Impact: Setting a Database to Read Only

  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 Read More... Posted Monday, October 01, 2007 2:50 PM by Linchi Shea | 12 Comments Filed under: Performance, Testing Attachment(s): ReadOnly.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 | 2 Comments Filed under: Performance, Testing, Storage, Best Practices 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 | 6 Comments Filed under: Performance, Testing, Storage, Best Practices 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 | 8 Comments Filed under: Performance, Testing, Best Practices 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 | 2 Comments Filed under: Performance, Testing, Best Practices 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 | 3 Comments Filed under: Performance, Testing, Best Practices Attachment(s): Trace1118.gif

• Which is Faster -- (1) Copy and Local Bulk Insert or (2) Bulk Insert across Network?

  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 Read More... Posted Tuesday, July 24, 2007 12:29 AM by Linchi Shea | 5 Comments Filed under: Performance, Testing, Best Practices Attachment(s): BULK_tests.zip

• Performance Impact of Procedure Calls without Owner Qualification -- SQL Server 2000

  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 Read More... Posted Thursday, July 05, 2007 1:25 AM by Linchi Shea | 19 Comments Filed under: Performance, Testing, Best Practices Attachment(s): sql2000.gif

• Performance Impact of Using NTFS Compression with Read-Only Databases

  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 Read More... Posted Friday, May 04, 2007 3:23 AM by Linchi Shea | 3 Comments Filed under: Performance, Testing, Storage, Best Practices

• How did Random I/Os Outperform Sequential I/Os?

  Recently, when I was doing some I/O performance tests on an I/O path, I found that 8K random reads (and writes) significantly and consistently outperformed 8K sequential reads (and writes) in terms of I/O throughput (megabytes per second). I was puzzled. Read More... Posted Wednesday, April 04, 2007 4:14 PM by Linchi Shea | 5 Comments Filed under: Performance, Testing, SAN, Storage

• SQL Server Backup I/O Performance

  I had always thought that: SQL Server backup reads/writes sequentially, and SQL Server backup could fully utilize the throughput of the I/O path But I'm no longer so sure. Recently, I was doing some benchmark work on two I/O paths, and had the following Read More... Posted Friday, March 30, 2007 11:13 PM by Linchi Shea | 3 Comments Filed under: Performance, Testing, Storage Attachment(s): Backup1.gif