What is SQL Profiler, How to use SQL Profiler on a database in SQL Server

SQL Profiler is a tool used to capture SQL Statements fired at the database.

It’s basically a listener tool. The tool is included in the SQL Server and used for monitoring purpose.

The output of the SQL Listener is fed to the Tuning advisor to get the optimized statements.

SQL Profiler tool is used by all Developers, Administrators and Testers.

Event – An event is an action generated within the SQL Server Database engine.

 The following are Events in SQL Server-

a) The login connections, failures, and disconnections.

b) SQL Statement SELECT, INSERT, UPDATE, and DELETE execution.

c) The start or end of a stored procedure.

d) The start or end of statements within stored procedures.

Event class -A type of event that can be traced.

We can also say that Events are grouped under different Event class.

Event classes are grouped into event categories.

For example, all lock event classes are grouped within the Locks event category
Refer the image at the end of the post for all types of events

Filters - Filters are used to select only the required values.

There is too much noise (too much information) thats get generated when we see the profiler for entire database hence we use Filters

Tuning Template should be selected in the main Profile page

 The template “Tuning” should be selected in the Template type instead of Standard Template. As this will help to reduce the noise.

Next we need to apply Filters so that we capture statements fired only on a particular database.

Here we will apply filter to capture SQL Statment’s fired only on Database “Test Database”

Now we will insert a Record to the Table_Table1 which is in Test Database hence our profiler will capture the insert statements

Note – Data displayed in the SQL Profiler follows the below convention

Old are at the TOP

New are at the BOTTOM

 i.e. New will be updated at the bottom
 

Event Classes (Official Image from microsoft )