THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Browse by Tags

All Tags » storage » Best Practices   (RSS)
Showing page 1 of 2 (14 total posts)
  • Performance impact: file fragmentation and SAN – Part IV

    Lies, damned lies, and statistics!   If you have read my three previous posts (1, 2, 3), you may walk away with an impression that on a drive presented from a high-end enterprise class disk array, Windows file fragmentation does not have a significant performance impact. And I’ve given you empirical data—oh yeah, statistics—to support that ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on December 22, 2008
  • Finding Disk Partition Offsets Made Easy

    There were discussions on disk misalignment on this site. See my previous post on “Performance Impact of Disk Misalignment”, and Kevin Kline’s blog on “How to Improve Application and Database Performance up to 40% in One Easy Step”   But thanks to Jimmy May's PASS 2008 presentation on the I/O performance impact of disk partition ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on November 24, 2008
  • 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 recommendations caught my attention: For small servers with less than three disks ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on November 19, 2007
  • 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 for many since we still frequently see people using disk queue length as the key measure in ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on November 12, 2007
  • 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 repeats for up to 16 total pages inclusive of the first page. Do a hash lookup for the ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on November 1, 2007
  • SQL Server and SANs: The QueueDepth Setting of a Host Bus Adapter (HBA)

    Too many DBAs tend to view a drive presented from a Storage Area Network (SAN) as something of a monolithic nature. They look at the drive as if it had some intrinsic performance characteristics. This view doesn't help one appreciate the true performance characteristics of such a drive. A more constructive view is to look at the drive as an I/O ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on September 18, 2007
  • 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 trying to get the most out of the insert script. But if we forget about tinkering the ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on August 27, 2007
  • 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, in the following script, what value should we give to variable @batch_size so that the script ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on August 23, 2007
  • 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 perspective. If you were led to believe that manual checkpoints were always bad, that wasn't ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on August 20, 2007
  • 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 to take advantage of the throughput potential of the underlying storage. But I didn't ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on August 17, 2007
1 2 Next >
Privacy Statement