X hits on this document

175 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 views175
Page views175
Page last viewedTue Jan 24 05:31:23 UTC 2017
Pages48
Paragraphs987
Words15822

Comments