Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 24, 2005

Working with the SQL Profiler - Page 3

By Steven Warren

Another example would be a trace that looks at third-party applications or people who use excessive resources. To trap this information, use Sessions:ExistingConnection and TSQL-SQL:BatchCompleted and then group by CPU, Reads, and Writes, as shown in Figures J and K.

In addition to Tracing SQL Server events, you can use the SQL Profiler for system monitoring. When using Profiler as a system monitor, trace the following:

  • Sessions: Existing Connection
  • Errors and Warnings: Error Log
  • Errors and Warnings: Event Log
  • Security Audit: Audit Login Failed

Once you have the output, you can save the results to a file or a table for later viewing.
Another key feature of Profiler is its ability to debug SQL Server transactions and stored procedures by trapping and replaying SQL statements. Let's explore this a bit further.
To capture a SQL statement for replay, open Profiler and choose the SQL Profiler TSQL_Replay template. After you run the trace, save it to either a file or a table. Your next step is to open the saved trace by choosing File | Open | Trace File or Trace Table and enter your connection information and your replay SQL Server options (Figure L).

Figure L.

Next, choose Step from the Replay menu. You can also choose the following:

  • Step-This steps you through each TSQL statement one at a time.
  • Start-This replays the entire trace.
  • Run To Cursor-This runs the trace to the where the cursor is currently highlighted.
  • Pause-This pauses the trace.
  • Stop-This stops the trace.
  • Toggle Break-Point-This allows you to specify a break point in your trace.

To specify a break point:

  • Open Profiler from the Start menu.
  • Open the Trace that you saved to a file or a table.
  • Highlight a SQL statement, as shown in Figure M, and choose Toggle Break-Point from the Replay menu. A red circle will appear to the left of the SQL statement you selected.

Figure M.

Specifying break points throughout your trace will let you stop at the predefined spots along the way, so you do not have to single-step through the entire trace or file.

You should now have some common knowledge on how to work with the SQL Profiler. I recommend that you experiment with the different templates and create a few of your own. By tracing events and playing back the results, you will be able to quickly identify application and query problems.

» See All Articles by Columnist Steven S. Warren

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM