Chapter 7. Inside Tuning and Optimization Tools

This chapter will cover various SQL Server tools that can be used for tuning and optimization of database queries. It should give you a detailed overview of what is available and help provide you with some guidance on what tool to use in your particular situation.

The chapter starts with discussion of execution plan capture and analysis features of SQL Server Management Studio. Then it looks at the internals of SQL Server Profiler and highlights many new features related to advanced tracing and data analysis that were added to the tool in the SQL Server 2005 release of the product. Following a discussion of tracing, you get a brief look at the new tracing application programming interface and see several related scenarios along with corresponding code samples. A somewhat underutilized feature of SQL Profiler is covered separately: Replay. This chapter also covers the Replay application programming interface exposed by the tool. Finally, it takes an in-depth look at Database Tuning Advisor, including its internal architecture and multiple usage scenarios.