SQL Server Profiler and Activity Monitor

SQL Server Profiler - The SQL Server 2005 comes with an event capturing tool, using which the events of an SQL server may be captured and saved in an XML file or an SQL table. This tool captures data from an SQL server. The file where all the event activities are persisted is referred to as a 'trace file'. The intent of creating this file is for the analysis of the events that have occurred on the server, by looking into the history of each event that has happened.

The following steps to create a trace file:

1) The SQL Profiler has to be opened.
2) File Menu -> New Trace
3) Type the name of the SQL Server in the "Connect to Server" dialog box. Click "Connect".
4) In the "Trace Properties" dialog box, type the name for the trace.
5) There are in built trace templates that may be selected, or a blank template may be used in case no template is selected.
6) Click "Save to file" to set a location for the file to be saved. The default file size limit is 5 Megabytes. An option called "Enable File Rollover" may be opted, in case a new file is to be created when the file size increases the allowed limit.
7) The "Save to Table" option may be opted, in case the results are to be persisted in an SQL table.

How does SQL Profiler help? - The SQL Server Profile has a replay feature, which replays all events that occurred on an SQL Server in the exact way they occurred. This ultimately helps any Database Administrator to go through all the events that happened and eventually assists in troubleshooting.

SQL Profiler also allows to track the activities of preferred event class, that may be closely monitored by an administrator.

SQL Server Activity Monitor - SQL Server 2005 comes with an activity monitoring tool called the "Activity Monitor". This tool is a component of the SQL Server Management Studio. It helps in  getting information about users connection, and locks that may happen
because of different reasons.

There are 3 pages in the Activity Monitor:

1 - Process Info Page - contains information about all connections.
2 - Locks by Process Page - contains sorted information by locks on the connections.
3 - Locks by Object Page - Contains information about locks sorted based on the object.

Whenever a lock occurs in a database, the Activity Monitor is the best place to view, in order to figure out the cause of the lock.

Its important to note here that in order to view the Activity Monitor, the user needs to have the VIEW SERVER STATE permission on the SQL Server he/she is working on.

How to open Activity Monitor?

 Open SQL Server Management Studio. Connect to a server. Expand 'Management' and then double click 'Activity Monitor'. Here the 3 tabs for each page will be available.