Short-Take: SQL Performance NotesSeptember 15, 2010
Here are some Microsoft SQL performance notes from discussions that inevitably crop-up when discussing SQL storage:
- Where do I find technical resources for the current version of MS SQL?
- I’m new to SQL I/O performance, how can I learn the basics?
- The basics talk about SQL 2000, but what about performance considerations due to changes in SQL 2005?
- How does using SQL Server 6.x versus SQL Server 7.0 and change storage I/O performance assumptions?
- How does TEMPDB affect storage (and memory) requirements and architecture?
- How does controller and disk caching affect SQL performance and data integrity?
- How can I use NAS for storage of SQL database in a test/lab environment?
- What additional considerations are necessary to implement database mirroring in SQL Server?
- When do SQL dirty cache pages get flushed to disk?
- 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:
- 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?
- How do I diagnose and detect “unreported” SQL I/O problems?
- How do I diagnose stuck/stalled I/O problems in SQL Server?
- What are Bufwait and Writelog Timeout messages in SQL Server indicating?
- Can I control SQL Server checkpoint behavior to avoid additional I/O during certain operations?
- 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…
Posted in Applications, Operating Systems, Performance, Short Take, Windows Server 2003, Windows Server 2008 | Tagged i/o tuning, microsoft sql, mpt_handle_event_sync: IOCStatus=0x8000, Performance, sql i/o problems, sqlio benchmark, sqliosim, sqliostress, Storage, tempdb |