Use SQL Server Profiler

19.2.1 understand SQL Server Profiler
SQL Server Profiler is a graphical real-time monitoring tool that helps the system administrator monitor database and server behaviors, such as the number of deadlocks, fatal errors, and track Transact-SQL statements and stored procedures. You can store the monitoring data into tables or files and re-display these events at a later time for further analysis.
We usually use SQL Server Profiler to monitor only some insert events. These events mainly include:

• Login connection failed, successful, or disconnected;
• Delete, insert, and update commands;
• The status of remote Stored Procedure Call (RPC;
• The start or end of the stored procedure and each statement in the stored procedure;
• An error occurred while writing the SQL Server Error Log;
• Open cursor;
• Add or release locks to database objects.

The reason we do not monitor too many events is that the monitoring of events often increases the burden on the system, and the tracking file grows rapidly into a large size file, causing unnecessary trouble.

19.2.2 create a trail
In SQL Server, you can use SQL Server Profiler to create a trail, or use the trail creation wizard or extend the stored procedure. Here we will introduce how to use SQL Server Profiler to create a trail.
(1) start SQL Server Profiler (same as starting SQL Server Enterprise Manager), select new from the drop-down menu of file, and then select trace option. The trace Properties dialog box is displayed, 19-2.
In this dialog box, define the trail name, type, SQL Server for running the trail, and storage mode for the trail output data. The meanings of each option are as follows:

• Shared: All Users Allowed to log on to the SQL Server Profiler server can use this trail;
• PRIVATE: indicates that only the current creator can use the trail;
• Capture to file: stores trace results in files to reduce memory overhead during tracking;
• Capture to table: stores trace results in tables, which can easily cause additional system overhead.

(2) Select the events tab, 19-3. This dialog box is used to determine which events will be tracked. In the available events window, select the event to be tracked. The box at the bottom shows the meaning of the event. Click Add to add the event to the selected events event;

(3) Select the data columns tab, as shown in Figure 19-4. In this dialog box, you can select the data columns of the tracking event, and usually select the columns that we care about.

(4) Select the Filters tab, as shown in Figure 19-5.

(5) Click "OK" to create a trail.

19.2.3 viewing, analyzing, and tracking
You can use SQL Server Profiler to view event data in the trail. Each row in the trail represents an event. The event data is determined by the trail attributes. You can copy SQL server data to other applications, such as SQL Server Query analyzer or index tuning wizard, and then use them for data analysis, however, we usually use SQL Server Profiler for tracking and analysis.
You can use SQL Server Profiler to open trace files with the extension. TRC, log files with the extension. log, and common SQL script files.
Because the trace information is usually stored in files or tables, you can view and analyze the trace by opening the table or file.
To open a trail, follow these steps:
Select open from the File menu, and then select trace files. The 19-6 dialog box is displayed.