Check Database Consistency
Ensuring database consistency and recoverability comes first. To guarantee that you have this job covered, develop a checklist of steps that brings your database system in line with this goal—and make sure you can check off every step. The first thing on the list is to plan your system's on-disk subsystem and on-disk structures with recoverability in mind. Except perhaps for the smallest databases, you should have overridden SQL Server's installation defaults and put your data and log files on different drives, with log files on mirrored volumes. Next, you need to understand SQL Server's recovery models and how they affect the way your system handles transaction logs. SQL Server's recovery models are part of the database properties. SQL Server 2000's Simple recovery model is suited only to the smallest installations and doesn't allow for point-in-time recovery. The vast majority of applications require Full or Bulk-Logged recovery models.
Make sure your checklist includes a backup plan that accounts for both backup and disaster-recovery scenarios. In other words, you need to be able to restore the SQL Server system itself as well as the data that resides on the system. Last, but certainly not least, you need to test all aspects of your restore and recovery plans. The absolute worst time to test these plans is when you need them. I can think of better ways to spend a Saturday morning than watching a system's backup tapes whir. However, there's no other way to know whether your backup and disaster-recovery plans will work when you need them.
—Michael Otey
Editorial, "The Data Is Job One"
June 2002, InstantDoc ID 24969
7 Hints for Better Performance
SQL Server performance depends on the interaction of many factors, ranging from the hardware that the system runs on to the application coding techniques you can use. Here are seven ways you can improve SQL Server's performance:
7. Use SQL Server Profiler.
6. Use the SQL Performance Monitor.
5. Use the Query Analyzer ShowPlan option.
4. Use the Index Tuning Wizard.
3. Use automated tuning.
2. Plan disk subsystem usage.
1. Get enough memory.
—Michael Otey
SQL Seven, "Performance Tuning Tips"
November 1999, InstantDoc ID 6207