Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring T-SQL Statements of Database Engine. We can save and reuse the state at a later point of time.
The following activities can be done in Sql 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
Starting SQL Server Profiler in Management Studio
SQL Server Management Studio starts each profiler session in its own instance and continues to run if user shutdown SQL Server Management Studio.
User can start SQL Server Profiler from several locations in SQL Server Management Studio, as illustrated in the following procedures. When SQL Server Profiler starts it loads the connection context, trace template, and filter context of its launch point.
1)To start SQL Server Profiler from the Tools menu
In the SQL Server Management Studio Tools menu, click SQL Server Profiler.
2)To start SQL Server Profiler from the Query Editor
On the SQL Server Management Studio menu bar, click New Query.
In Query Editor, right-click and then select Trace Query in SQL Server Profiler.
the below window will appear as shown below.
It has two selection tabs:
1)General – It is used for general setting for Trace Database Engine.
2)Event – It is used to add or remove some selected event for monitor.
The below explanation is for General section alone. In General Section it is divided into four sections.
Section 1: In this section, you have to just specify the name of your trace, Trace provider name and server name are predefined and based upon your SQL Server.And it is not editable.
Section 2: It is the template section. You can choose different type of Templates based upon your requirements. It is the configuration for trace. By default, it is “Standard (Default)” templates. Others templates are T-SQL, T-SQL Duration, T-SQL Reply, T-SQL SPs, etc. You can create your own custom Templates by selecting different Events and Event Class. It is saved as “.tdf” Extension.
Section 3: This section is related to save your trace. Either as File (.trc) or in a database. as table. While clicking on Save to file check box, File save dialog box should open and you can save that file (with .trc extension).
If you check the “Save to Table”, it will connect with your server and ask you to which database you want to save that trace table information.
Section 4: You can stop your trace on a particular time. Check the “Enable trace stop time” checkbox and give the time at which you want to stop track, SQL Server will automatically stop trace on that time.
After crossing through all the section click on “Run” button to start the tracing. Final result will be saved in the specified location.