Summary

In this chapter, we looked at improving the performance of your SQL Server database.

First, we saw how to pinpoint performance bottlenecks associated with the database such as missing indexes, expensive queries, and locking. We also looked at execution plan reuse and fragmentation. In addition to this, the possible hardware-related bottlenecks, including the database server's CPU, memory, and disk usage were discussed.

We then dug deeply into fixing the bottlenecks which we found. The two types of indexes: clustered and non-clustered, how they work, and when to use them were discussed. We saw the advantages and disadvantages of reducing the size of table records and discussed other ways to speed up queries such as caching aggregation queries. Then, we learned how to gather detailed information about locking-related bottlenecks, and how to reduce blocking and deadlocks.

We also discussed how to improve execution plan reuse dramatically, through the use of stored procedures and sp_executesql...