Chapter 8: Improving Database Application Performance

Overview

Microsoft SQL Server 2005 is designed to handle databases of all sizes, but unless you are careful about how you design, develop, and maintain your database applications, they might not perform as expected. It is critical that you know what considerations need to be made so your applications perform optimally.

Lesson 1 will cover the topic of scalability and the different technologies you should consider. You must take several factors into consideration when determining what scaling methods to use. Lesson 2 will move on to resolving performance problems. Not only do you need to know how to identify problems, but you should be prepared to suggest resolutions.

Exam objectives in this chapter:

• Scale database applications.

  • Specify a data-partitioning model.

  • Design queries that target multiple servers.

  • Implement scale-out techniques like federated database, service broker, distributed partitioned views.

  • Design applications to distribute data and workload transparently.

  • Identify code or processes that can be moved to a different tier to improve performance.

  • Rewrite algorithms to improve efficiency.

• Resolve performance problems.

  • Analyze application performance across multiple users.

  • Capture workload information.

  • Find out the causes of performance problems.

  • Specify resolutions such as: changing algorithms, scaling up, and scaling out, terminating a session.