SQL Server Profiler

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. An SQL server profiler is a tool for tracing, recreating, and troubleshooting problems in MS SQL Server, Microsoft’s Relational Database Management System (RDBMS). We can save and reuse the state at a later point of time. It’s like a dashboard that shows the health of an instance of MS SQL Server. 

Users create traces to capture data and monitor errors and other problems. They then use the profiler to store, retrieve, and view the results of many traces graphically for purposes of troubleshooting and repair. This function all happens on the client-side, meaning it uses resources on the same machine it’s monitoring.

We can do the following using SQL Server Profiler

- Create a trace

- 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

Event

An event is an action generated within an instance of SQL Server Database Engine. Examples of these are:

- Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.

- User login and logout

- Execution of Stored procedures

- Operation with cursor

Event Class

Event class is a type of event that can be traced. Some examples are:

- SQL: BatchCompleted

- SQL: Batch Starting

- Audit Login

- Audit Logout

- Lock: Acquired

- Lock: Released

Reply in SQL Server Profiler

SQL Server profiler has a Reply facility which has the ability to save a trace and replay it later.

Replay is useful to troubleshoot an application. Trace replay supports debugging by using Toggle Breakpoint and the Run to Cursor options on the SQL Server Profiler Replay menu.

Anything changed in SQL Server Management Studio will be traced by the SQL Profiler. So it can basically be used for database performance check. We also have "SQL Server Performance Monitor" to monitor the System and Server performance too.