Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 93

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 the
end of this chapter, you will see how to correlate SQL Profiler trace data and Windows performance
counter data.
Upon finishing the chapter, with some practice, a database administrator should be able to set up
an optimal Profiler trace based on a given scenario.
Preparations for Setting Up a Trace
Setting up a useful trace requires proper planning.Thepurposeofatraceshouldbeclearlydefined.
Selecting SQL trace events in a trace requires a good understanding of both SQL trace event classes
and terminologies. This section will provide you with what you should know for setting up a trace.