CHAPTER 28 ■ PERFORMANCE TUNING
Server instance. There are many potential uses for this tool, but this recipe specifically demonstrates how to use SQL Server Profiler to capture high duration queries.
In this recipe, SQL Server Profiler is launched and configured to capture high duration queries. Then, a query is executed in SQL Server Management Studio that will be captured in SQL Server Profiler:
The recipe begins by going to Start, Programs➤Microsoft SQL Server 2005➤Performance Tools➤SQL Server Profiler.
Once in SQL Server Profiler, go to File➤New Trace. This brings up the Connect to Server dialog box (see Figure 28-1). It is here that you select the name of the SQL Server instance to connect to, and the authentication method (either Windows or SQL). When you’re finished designating these values, click the Connect button.
Figure 28-1. The Connect to Server dialog box
3. This brings up the Trace Properties dialog box (see Figure 28-2). In the Trace name field, type in Queries with a Duration > 5 seconds. SQL Server Profiler comes with a set of various trace templates. These templates contain pre-defined event selections, which allow you to get started with monitoring specific types of SQL Server instance activity. In this recipe, the TSQL_Duration template is selected from the Use the template field: