- Know your data: I often see queries where a developer has taken a crazy route to get to some data where a much simpler path could have been used. This is often the result of bad schema design choices.
- Understand real-world use patterns: Understand how will the data actually be queried. Does your schema support these queries properly and easily?
- Fix your database schema: A better schema will allow developers to write better queries and better queries will be faster queries. Simple as that. And speaking of tricks, avoid creating tricky schemas. Entity-Attribute-Value (EAV) is a good example; is it really that difficult to create an additional column when you need a new attribute?
- Fix the queries: If you’ve done all you can with the schema, start looking at the queries. Are they unnecessarily using cursors? Extensive use of temporary tables? Nonsargable predicates?
- Index Effectively: Notice that this is step #5. If at all possible, fix the schema and the data before you get to this step. Indexing is a great tool, but we often tend to use it as a crutch to fix problems that were created at other stages of the design process.
- Know the Hardware: It’s difficult to specify hardware without understanding something about data volumes, use patterns, and the like. How could you possibly know how many bytes/second your disk system needs to be able to serve up without knowing how many bytes/second your queries will be consuming? The vast majority of systems I see were designed based on guesswork at best, and oftentimes no information at all. Databases are not all created equal, and neither are servers.
- Be Specific: Don’t guess. Don’t try to be tricky. Collect metrics, apply them to your knowledge of the system, and create solutions.
See Also: