MS SQL Basics Tutorial: Using SQL Server Profiler

19.2.1 Understanding SQL Server Profiler

SQL Server Profiler is a graphical real-time monitoring tool that helps system administrators monitor the behavior of databases and servers, such as the number of deadlocks, fatal errors, and the tracking of Transact-SQL statements and stored procedures. You can store these monitoring data in a table or file, and then display the events again at a later time to analyze them step-by-step.

Typically, we use SQL Server Profiler to monitor only certain insert events, such as failure of a logon connection, success or disconnection, DELETE, INSERT, UPDATE command, the status of a remote stored procedure call (RPC), the start or end of a stored procedure, and 　　Every statement in a stored procedure, an error writing to the SQL Server error log, an open cursor, or a lock or a release to a database object. The reason we don't monitor too many events is that monitoring events often increases the burden on the system and causes the trace files to grow quickly into large-capacity files, causing unnecessary hassle.

19.2.2 Create a Trace

You can use SQL Server Profiler to create traces in SQL Server, or you can use trace creation wizards or extended stored procedures. Here we will describe how to use SQL Server Profiler to create a trace.

(1) Start SQL Server Profiler (same as starting SQL Server Enterprise Manager), select New from the file's Drop-down menu, and then select the Trace option, which opens the Trace Properties dialog box, as shown in 19-2 shows.

In this dialog box, define the name of the trace, the type, the SQL Server that runs the trace, and how to track the way output data is stored. The meaning of each option is Shared: it means that all users who are allowed to log on to the SQL Server Profiler server can use the trace; Private: Indicates that only the current creator can use the trace; Capture to file: represents storing trace results to File, you can reduce the memory overhead when tracing; Capture to table: Indicates that the trace results are stored in a table, which makes it extremely easy to incur significant additional overhead.

(2) Select the Events tab, as shown in Figure 19-3, which is used to determine which event will be tracked. In the Available Events window select the event to track, the bottom of the box will show the meaning of the event, click Add, add to the selected events event;

(3) Select the Data Columns label page, as shown in Figure 19-4. In this dialog box you can choose which data columns to track events, and usually select the columns that we care more about.

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

(5) Click "OK" button to complete the trace creation.

19.2.3 view, analyze Trace

Using SQL Server Profiler, you can view the event data in a trace, and each row in the trace represents an event that is determined by the properties of the trace. 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, but usually we use SQL Server Profiler to track points Analysis

With SQL Server Profiler, you can open a trace file with the. trc extension, or you can open a log file with the extension. log, as well as a generic SQL script file

Because trace information is usually saved in a file or table, you can view and parse the trace by opening the table or file.

The steps to turn on tracing are:

Select Open from the File menu, and then select Trace Files to eject the dialog shown in Figure 19-6.

See the full set of "MS SQL Basics Tutorials"