Chapter 6: ASE's Optimizer
This chapter doesn't attempt to provide every detail and nuance of the optimizer. Rather, it provides an overview of the steps the optimizer takes to make its decisions. You'll agree that understanding the basic processes of this important piece of ASE will help to better tune any queries. It starts describing the optimizer's two phases to find the cheapest query plan, then it describes the estimate column and estimate index selectivity and the cost of joins. An interesting chapter, but don't expect too much from it.

Chapter 7: Cache Strategies
The efficient deployment of appropriate amounts of memory is key to achieving optimal performance. Having available memory will reduce, or even eliminate, the need for physical I/O. This, in turn, can enable a quantum improvement in response time, since memory access is always much faster than disk access. However, it's not just a matter of bringing up a server with tons of memory; it's also how you deploy and allocate the memory resources you have. This chapter describes the memory allocation of the server (including changes from prior versions), the different types of cache available (Procedure Cache, Data Cache, Named Cache), and the two major strategies for efficient data cache performance: LRU (Least Recently Used) replacement strategy and MRU (Most Recently Used), or fetch-and-discard, replacement strategy. Overall it's a complex and interesting chapter.

Chapter 8: Parallel Query Processing
This chapter provides details on how to use parallel queries for performance and to pinpoint any issues that may occur when using it. It's very detailed and you won't have any questions about parallel query processing after reading it. Everything is there. It starts by describing why you should use it and how the parallel processing model is implemented internally and how the objects are accessed by the server engine (hash and partition processing). The configuration parameters are described in detail and it is also shows how to test and monitor these queries.

Chapter 9: Application Design
Any successful application is a combination of many well-integrated components, including good client design, a reliable network with sufficient bandwidth, efficient database design, fast and reliable server hardware, and minimal resource contention with other interacting applications. Often, a project fails due to omitting the steps necessary to assess the system impact of other applications or different functions within the same application. This chapter describes the common problems that occur when it comes to application development. Every experienced database developer will run into the mentioned items, which are indeed worth considering. It's good to have a place where these things are mentioned and described.

Chapter 10: T-SQL Programming
SQL is a set-based language that is very different from traditional procedural-based languages. SQL is much more efficient in executing set-based statements than it is in executing single row updates. The cursors provided in the SQL standard allow row-based processing but should only be used when each row requires specific processing depending on the attribute values of that row. Any tuning or performance tricks are usually DBMSspecific and are therefore not usually portable via the SQL standard syntax, though this chapter also describes some general rules that should be thought of (i.e., stored procedures generate less network traffic than a complex query would). It also mentions that triggers are important because they can be used to enforce referential integrity and business rules. Since they use the inserted and deleted special tables, which are not indexed and built from the transaction log, they can seriously affect performance if coded poorly.

Chapter 11: Optimizing Stored Procedures
How to write good procedures? The authors describe it quite well at the beginning of this chapter:

• Establish a proper testing environment.
• Plan your stored procedures correctly.
• Review testing objectives.
• Debug the stored procedure.
• Measure the performance of the stored procedure.
• Make changes to the stored procedure and resume testing.
• Make changes to the database if necessary.

This chapter is the most useful for database developers. The authors' experience with tuning a stored procedure from seven minutes to 20 seconds was exactly what I saw at different customers as a consultant. Sometimes it would be pretty easy to get a fast performing system just by looking at a few things. This chapter should be given to every developer within your company.

Chapter 12: Locking
The role of the DBMS is to manage data. This includes, among other things, security and data integrity. What would be the effect of writing two rows to the same place? What would be the effect of allowing two processes to update the same row at the same time? How about allowing one process to read a row when the change is not yet permanent (committed) in the database? This chapter discusses all aspects of locking such as:

• Consistency levels
• Lock isolation levels
• Lock granularity
• Types of page locks
• Data locking mechanisms
  -Allpages
  -Datapages
  -Datarows