SQL Profiler

What is the SQL Profiler?

     SQL Profiler is a graphical tool that allows the DBAs to monitor events in an instance of Microsoft SQL Server. Once those events and data are captured, you can save the results to a file or a table for later analysis

The next few videos I will demonstrate the following capabilities of the Profiler:

  • How to monitor the performance of an instance of SQL Server via trace
  • Review and debug T-SQL statements and stored procedures
  • Identify what is causing slow-executing queries
  • Audit activity against the SQL Server by tracing logins and users (security)
  • Use the tuning template to analyze Indexes
  • Add a user profiler template
  • Best practice in using the SQL Profiler

Does and don’ts of SQL Profiler

  • Since the Profiler adds a lot of overhead to production server, filter trace
  • Save the trace to a file rather than a table first
  • Use only when needed for short period of time, as it consumes CPU
  • Configured the trace only relevant events and columns

Terminology

Event – An event is an action within an instance of SQL Server Database Engine

Trace – A trace captures data based on the columns and events

Template – A template defines the default configuration or one that you set for the trace

Under Windows 10, you can write SQL Server Profiler from the Start menu, and the application will show in the results:

running SQL Server profiler from the start menu in Windows 10

Or you can simply find a shortcut of this tool under the Tools menu inside the SQL Server Management Studio as shown in the image below:

running SQL Server Profiler from the Management Studio

When you open the Profiler, the authentication form is shown. You have to select whether you need to connect to an Analysis Service instance or a Database Engine. Then you should enter the instance name, the authentication type, and the credentials:

connecting to an instance in SQL Server Profiler

In this first tab, you have to enter the trace name. Then you can select whether you need to save the trace results into a table or external file and also if you need to pause the trace at a specific date-time.

Also, you can select a trace template from a drop-down list based on your needs. Note that a template is a set of tuning events that are caught by the profiler trace. There are different predefine templates such as T-SQL, Tuning or you can select the standard (default) template or start from scratch by selecting the Blank template:

General tab of the trace properties form

In the Events Selection tab, you can select all events that you need to include in the trace, not that you can add some filters. As an example, if you need only to trace the operations executed in a specific database, then you can add a filter on the database name property. Note that even if you select a template in the General tab, you can edit the event’s selection:

Events selection tab of the Trace properties from