Short-Take: SQL Performance Notes

Here are some Microsoft SQL performance notes from discussions that inevitably crop-up when discussing SQL storage:

1. Where do I find technical resources for the current version of MS SQL?


2. I'm new to SQL I/O performance, how can I learn the basics?


3. The basics talk about SQL 2000, but what about performance considerations due to changes in SQL 2005?


4. How does using SQL Server 6.x versus SQL Server 7.0 and change storage I/O performance assumptions?


5. How does TEMPDB affect storage (and memory) requirements and architecture?


6. How does controller and disk caching affect SQL performance and data integrity?


7. How can I use NAS for storage of SQL database in a test/lab environment?


8. What additional considerations are necessary to implement database mirroring in SQL Server?


9. When do SQL dirty cache pages get flushed to disk?


10. Where can I find Microsoft's general reference sheet on SQL I/O requirements for more information?

From performance tuning to performance testing and diagnostics:

1. I've heard that SQLIOStress has been replaced by SQLIOSim: where can I find out about SQLIOSim to evaluate my storage I/O system before application testing?


2. How do I diagnose and detect "unreported" SQL I/O problems?


3. How do I diagnose stuck/stalled I/O problems in SQL Server?


4. What are Bufwait and Writelog Timeout messages in SQL Server indicating?


5. Can I control SQL Server checkpoint behavior to avoid additional I/O during certain operations?


6. Where can I get the SQLIO benchmark tool to assess the potential of my current configuration?

That should provide a good half-day's reading for any storage/db admin...