X hits on this document

99 views

0 shares

0 downloads

0 comments

6 / 48

640

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:

  • 1.

    The recipe begins by going to Start, ProgramsMicrosoft SQL Server 2005Performance ToolsSQL Server Profiler.

  • 2.

    Once in SQL Server Profiler, go to FileNew 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:

Document info
Document views99
Page views99
Page last viewedMon Dec 05 13:17:28 UTC 2016
Pages48
Paragraphs987
Words15822

Comments