Once again, I attended 3 sessions today (Friday): Kevin Kline's "Performance Baselining, Benchmarking, and Monitoring", Tom Davidson's "SQL Server 2005: Dynamic Management Views", and Ross LoForte's "Optimization Considerations for Designing Very Large SQL Server Database Systems".
Kevin is a great speaker. He went into detail on how to do proactive monitoring. You start with a baseline, which is what is normal for the server. It includes number of concurrent users, Performance Monitor counter averages, system processes, and much more. He also showed us some exception monitoring, which is exceptions to the baseline. It uses limited performance monitoring, more alerts, and heavy use of the error logs.
I was in the presence of a SQL god during Kevin's session. Ken Henderson sat one seat in front and one seat to the right of me. I also was in the presence of someone with bad manners. The guy sitting two seats to the left of Ken put his nasty, bare feet on the seat in front of him. This is a professional conference, not a day at the beach. Show a little respect!
Tom showed us dynamic management views, also known as DMVs. Catalog views expose metadata persisted on disk, whereas DMVs expose changing server state information. They are used for diagnostics, memory and process tuning, and monitoring potentially across all sessions in that server. He mentioned that parallelism is good for OLAP systems but not for OLTP systems.
Ross noted that on systems with greater than 25% writes, RAID 10 is best and RAID 1 is next best. For systems that are primarily used for reads, such as data warehouses and reporting, RAID 5 is acceptable. He stated that RAID 10 should be used for the transaction log and for tempdb; the data drive can be either RAID 1, 5, or 10. He does not recommend using the autogrow feature on your databases if you have large databases. Instead, you should setup an alert to notify you when you are running out of space; then during a maintenance window, expand the file. I had to leave this session early so that I could catch my flight. I wish that I had been able to stay later as he was just getting into partitioning.