Database Reference
In-Depth Information
Chapter 26
SQL Server Optimization Checklist
If you have read through the previous 25 chapters of this topic, then you understand the major aspects involved in
performance optimization. You also understand that it is a challenging and ongoing activity.
What I hope to do in this chapter is to provide a performance-monitoring checklist that can serve as a quick
reference for database developers and DBAs when in the field. The idea is similar to the notion of tear-off cards of
best practices. This chapter does not cover everything, but it does summarize, in one place, some of the major tuning
activities that can have a quick and demonstrable impact on the performance of your SQL Server systems. I have
categorized these checklist items into the following sections:
•
Database design
•
Configuration settings
•
Database administration
•
Database backup
•
Query design
Each section contains a number of optimization recommendations and techniques. Where appropriate, each
section also cross-references specific chapters in this topic that provide more detailed information.
Database Design
Database design is a broad topic, and it can't be given due justice in a small section in this query tuning book;
nevertheless, I advise you to keep an eye on the following design aspects to ensure that you pay attention to database
performance from an early stage:
•
Balancing under- and overnormalization
•
Benefiting from using entity-integrity constraints
•
Benefiting from using domain and referential integrity constraints
•
Adopting index-design best practices
sp_
prefix for stored procedure names
•
Avoiding the use of the
•
Minimizing the use of triggers
•
Considering putting tables into in-memory storage
