Professional SQL Server® 2005 Performance Tuning

Chapter 5. Finding Problem Queries with SQL Profiler

The previous chapters provide the knowledge, tools, and resources for finding Windows and SQL server bottlenecks. Tracing database activities and collecting information are critical steps in troubleshooting, tuning, and optimizing a database. SQL Profiler is a valuable investigative tool for measuring query performance and troubleshooting database applications.

In this chapter, you will see how and when to use SQL Profiler to collect and analyze query performance data. SQL Trace is a server-side component for tracing. SQL Profiler is a graphical user interface for SQL Trace. Profiler is used for monitoring an instance of the Microsoft Database Engine or Analysis Services. This chapter focuses mainly on using Profiler to solve performance issues. SQL Trace provides a set of system-stored procedures that can be used from within your own applications to create traces manually. This allows you to write custom applications specific to the needs of your enterprise.

Performance issues can be very specific or vague. Capturing the right amount of reliable event data at the right time with minimum costs presents a challenge for most database administrators. There are many SQL Profiler features and details, which need to be considered before starting a trace. You will see some important options and concerns. You will use appropriate scenarios to examine the processes involved in setting up a Profiler trace and a server-side trace. Toward ...