|
|
|
|
Checking out SQL Server via empirical data points
Browse by Tags
All Tags » Best Practices » Storage (RSS)
-
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 Read More...
|
-
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 Read More...
|
-
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...
|
-
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...
|
-
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...
|
-
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 Read More...
|
-
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 Read More...
|
-
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, Read More...
|
-
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...
|
-
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...
|
-
In SQL Server related storage literature, it is almost univerally recommended that disk partitions be aligned either on the 32K or the 64K boundary. On this subject, I posted some test results a while back, regarding the performance impact of disk partition Read More...
|
-
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...
|
-
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 Read More...
|
-
Just google for Windows disk alignment best practice , and you would find thousands of articles, whitepapers, and posts, all preaching the practice of aligning disk partitions on the 64K boundary. For instance, one of the EMC recommendations prescribes Read More...
|
|
|
|
|
|