Microsoft Dynamics NAV - C/SIDE Solution Development -

Microsoft Dynamics NAV – C/SIDE Solution Development

Locus IT ServicesMicrosoft Dynamics NAV – C/SIDE Solution Development

Lesson 11: SQL Server Profiler

SQL Server Profiler

SQL Server Profiler : Microsoft SQL Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which queries are affecting performance by executing too slowly.

SQL Server Profiler

SQL Server Profiler can be used to monitor events that occur on SQL Server. It can be used to do the following tasks:

  • Create a trace that is based on a reusable template.
  • Watch the trace results as the trace runs.
  • Store the trace results in a table.
  • Start, stop, pause, and modify the trace results as necessary.
  • Replay the trace results.

SQL Profiler then can analyze or use the trace file to troubleshoot logic or performance problems. You can use this utility to monitor several areas of server activity, such as the following:

  • Analyzing and debugging SQL statements and stored procedures
  • Monitoring slow performance
  • Stress analysis
  • General debugging and troubleshooting
  • Fine-tuning indexes
  • Auditing and reviewing security activity

To summarize, you create a template or use an existing template that defines the data that you want to collect. Then you collect the data by running a trace on the events that you defined in your template. During the run, Profiler displays the event classes and data columns that describe the event data that is being collected.           

SQL Server Profiler Terminology

Template

A template defines the default configuration for a trace. Templates can be saved, imported, and exported between SQL Server instances. Templates from one SQL Server version cannot be imported to a different SQL Server version. SQL Server includes the following ten predefined templates:

  • Event – An event is an action that is generated by the SQL Server engine, such as a logon connection or the execution of a TransactSQL statement. Events are grouped by event categories. All the data that is generated by an event is displayed in the trace. This contains columns of data that describe the event in detail.
  • Trace – The trace does the actual data capture, based on the events that you defined in the template.
  • Event Class – An event class can be defined as a type of event that can be traced. Examples of event classes are SP:Starting and RPC:Completed.
  • Event Category – Groups of events are called an event category. Examples of event categories are Stored Procedure and Locks. There can be multiple event categories that can be selected for a single trace.
  • Data Column – Data column is an attribute of an event class that is captured in the trace. A data column contains values of an event class.

Filter – Filters are used to create selectivity in data that are collected in trace. By default, SQL Profiler monitors all events on SQL Server.

Locus IT Project Management Office
What’s it?