Let’s say that I am starting to design a SQL Server-based application. Even before I start coding, what can I do now (during the design process) to help ensure that my SQL Server-based application will perform well after it is written?
Performance need to be taken into consideration at the design stage. Some things to keep in mind include:
- Design your database using proper normalization techniques.
- Use IDENTITY primary keys wherever possible.
- Based on your understanding of the data, create indexes on any columns or sets of columns you think will help performance.
- Carefully choose each table’s clustered index (range queries are the best candidates).
- Use stored procedures exclusively.
- Make sure programmers understand to fetch all result set data ASAP.
- Don’t underestimate how inefficient some auto-generated SQL statements are when using tools that generate SQL for you. Some tools fetch entire data sets and sort in the client app, a potentially devastating design.
- Document each query carefully, noting the columns used in join conditions and SQL WHERE clauses. These columns will likely make good index candidates.
- Make sure your server has sufficient memory. Memory is one of the best and cheapest server upgrades possible.
- Don’t confuse server processing power with efficient queries.
- Make sure developers work closely with DBAs.
- Code, test, and tune. Repeat this process until satisfied.
- Design an index rebuilding schedule.
- Design standard test practices when rolling out new releases.
- Document the performance of each new iteration of an application/database. Coefficient can really help here.
Besides what we have covered, what other tips or advice can you offer to help DBAs or developers get the best performance out of SQL Server?
I think I can cover this in just a few items:
- Never assume your SQL is executing efficiently. Test to be sure.
- Never assume a query will run the same on one server as another (actual distribution and size of data makes a big difference).
- Test and re-test every query until satisfied.
- Use tools like the Profiler or Coefficient to watch your server activity.
- Document your queries as you write them, rather than when you realize you have a problem.
As you can see, a lot goes into database tuning. But as with anything, the more you practice and study a subject, the easier tuning becomes. When I tune a database, I like to start with a picture of overall database activity that includes the relationships between queries and detailed query statistics. That way, I know where to begin. This is why we designed Coefficient.
]]>