November 2007 - Posts

• A Rose By Any Other Name

  Regardless of the DBMS make or model, the transaction throughput curve of a database system is often shaped like a trapezoid. As the load level goes up initially, so does the transaction throughput. As the load level continues to go up, the transaction Read More... Posted Tuesday, November 20, 2007 4:04 PM by Linchi Shea | 8 Comments Filed under: Best Practices, Performance, SQL Server 2005, Waits Attachment(s): proc_name.gif

• Database Physical Storage Design

  In June 2006, Microsoft published a SQL Server technical paper on Physical Database Storage Design . This paper was updated in February 2007. The paper is generally well written, and the recommendations are reasonable. However, the following two specific Read More... Posted Monday, November 19, 2007 2:15 AM by Linchi Shea | 1 Comments Filed under: Best Practices, Storage

• Nondeterministic UPDATE

  Under the description for UPDATE in SQL Server 2000 and 2005 Books Online, you can find the following statement (thanks to SQL Server MVP Steve Kass for pointing me to this passage): The results of an UPDATE statement are undefined if the statement includes Read More... Posted Tuesday, November 13, 2007 3:15 PM by Linchi Shea | 2 Comments Filed under: Best Practices, SQL Server 2005, T-SQL

• Disk Queue Length: Some Data Points may Help!

  There has been much discussion on the usefulness of disk queue length as an indicator of a disk I/O bottleneck. Bob Dorr, for instance, addressed this issue directly in his excellent blog, SQL Server Urban Legends Discussed . But the issue is not settled Read More... Posted Monday, November 12, 2007 5:38 PM by Linchi Shea | 2 Comments Filed under: Best Practices, Queues, Storage Attachment(s): disk_q.gif

• Best and Worst Checkpoint Performance

  The best documentation on the I/O behavior of SQL Server checkpoints is found in SQL Server 2000 I/O Basics by Bob Dorr. In particular, you should read the following carefully: SQL Server uses the following steps to set up another page for flushing and Read More... Posted Thursday, November 01, 2007 1:05 AM by Linchi Shea | 5 Comments Filed under: Architecture, Best Practices, Performance, Storage Attachment(s): Best_checkpoints.gif