SQL Server Profiler

SQL Server Profiler

Rating:

SQL Server Profiler is a tool used to determine which transact SQL statements are submitted to the instance of a server. SQL Server Profiler is run from a Monitoring server to test a Production server. A SQL Server Profiler template is used to capture events and store the results in a file with the .tdf extension or in a table.

Conditions for capturing a trace

SQL Server Profiler can capture the traces in the following conditions:

• When the queries that are to be analyzed are running.


• When queries are executed in the Query Editor.


• When queries are executed through an application.

All the transact SQL statements can be captured by using the following two event classes:

RPC:Completed: The RPC:Completed event class indicates the completion of a remote procedure.

SQL:BatchCompleted: The SQL:BatchCompleted event class captures all the transact-SQL queries other than the stored procedures.

Showplan event classes: The Showplan event classes are used to gather and display a query plan in a trace. The Showplan events are extracted from a trace by taking the following steps:

• Use the Events Extraction Settings tab.


• Extract the SQL Server Events option on the Profiler menu.


• Right-click the specific events and choose Extract Event Data.

Events

SQL Server Profiler is a tool used to trace and capture various events that occur in SQL Server 2005. The information is saved either in a table or in a file for analysis. There are various events that are monitored by SQL Server Profiler. These events are as follows:

• Execution of SQL statements such as SELECT, CREATE, UPDATE, and DELETE


• Remote Procedure Call


• Start or end of a stored procedure


• Start or end of statements within a stored procedure


• An error written to SQL Server error log


• An open cursor


• Security permissions

The capturing of all these events helps resolve performance, connectivity, and security issues.

SQL Server Profiler Columns

SQL Server Profiler can be started from the SQL Server Management Studio or from the Start > All Programs menu. SQL Server Profiler is used to trace an instance of SQL Server. A trace is used to monitor errors occurring within the instance of SQL Server. The information can be gathered and viewed by using the following columns within SQL Server Profiler:

• EventClass: The event class is a type of event that is traced by SQL Server Profiler. It contains all the data related to an event.


• EventCategory: An event category defines the category of events that are present in SQL Server Profiler. The examples of some event categories are as follows:
  
  • Lock events
  
  
  • Broker events
  
  
  • Cursor events
  
  
  • Database events
  
  
  • Errors and Warning events
  
  
  • Full-Text events
  
  
  • Objects events
  
  
  • OLEDB events
  
  
  • Performance events
• DataColumn: A data column is a column in an event class where data related to an event is stored. An event class determines the data that is to be captured by the event. All the data columns in an event class are not needed to be captured.


• Template: A SQL Server Profiler template is used to store a trace event. It is also used to define a default configuration for a trace. It includes all those event classes that can be monitored by SQL Server Profiler. A template is saved as a file with the .tdf extension. A template controls the trace data when a trace is launched on its basis.


• Trace: SQL Trace is used to create traces by using stored procedures. These traces gather events, which are instances of event classes. These events can be filtered out of the trace and stored in a file or in SQL Server Management Objects (SMOs).
  
  The following stored procedures are used to create and manage traces:
  
  

  sp_trace_create	It creates a trace.
  sp_trace_setevent	It adds or removes event classes.
  sp_trace_setfilter	It applies a new or a modified filter to a trace.
  sp_trace_setstatus	It starts, stops, or closes a trace.
  sp_trace_generateevent	It creates a user defined event.
  fn_trace_geteventinfo	It returns information about the events included in a trace.
  fn_trace_getinfo	It returns information about a specified trace or all the existing traces.
  fn_trace_getfilterinfo	It returns information about filters applied to a trace.



• Filter: A filter is a criterion that is used to prevent traces from becoming too large. When traces or templates are created, a filter can be imposed on them. It is used to collect only a subset of event data.

Rating: