SQL Server 2000 Profiler
For More Info See - What's New in SQL Server 2000 Profiler
Typically, you use SQL Profiler to:
General Events Data Columns Filters
An important advantage of saving the trace output to a file is that when you reopen the file later for examination or for replay, it loads asynchronously and you can start examining the file while it's still loading.
Maximum file size - set from 1MB to 1000MB
Enable file rollover
If you specify a maximum file size but don't enable the file-rollover option, SQL Server stops writing events to the file after the file reaches the size you specified. Newer events do not overwrite previous events so you will get only the first events that fit the specified file size, events will still write to the screen but they will not be saved.
If you enable the file-rollover option, as soon as the trace file reaches the specified maximum size, SQL Server creates a new trace file.
The first trace file's name is the name you specified in the "Save to file" text box. SQL Server uses three elements to construct the next file's name: the file name you specified, an underscore, and a number, starting with 1 and incremented by 1 with every new file. For example, suppose you specify C:\traces\mytrace.trc as the trace filename and you leave the default maximum file size of 5MB. If you trace 17.5MB of trace data, you'll have four files: mytrace.trc, my-trace_1.trc, mytrace_2.trc, and mytrace_3.trc.
Server processes SQL Server trace data
This specifies that the server, not the client, should handle the trace data. This means that a server-side trace file writer, based on the transaction log writer, is responsible for writing the trace data to the file. If you enable server-side processing, you're guaranteed to capture all events that you requested. This option can degrade the server's performance. However, not enabling this option means that all events may not be captured under stress conditions.
Save to table
Saving the trace output to a table lets you analyze the trace data by using T-SQL queries or even a Microsoft Excel pivot table or Analysis Services cube to manipulate the trace data dynamically.
If you send the trace output to a file instead of a table, you can use the function fn_trace_gettable() to return the trace data in table format. The fn_trace_gettable() function accepts the trace filename and optionally a maximum number of files to process as parameters; it returns a rowset containing the trace data. For example, suppose that Profiler generated the following three files for a certain trace: C:\traces\mytrace.trc, C:\traces\mytrace_1.trc, and C:\traces\mytrace_2.trc. To copy the content of only the first file to a table, you would use fn_trace_gettable(), specifying 1 in the second parameter, as follows:
SELECT * INTO #Mytrace
FROM ::fn_trace_gettable('C:\traces\mytrace.trc', 1)
See also: Microsoft article "BUG: fn_trace_gettable Function Cannot Read Rollover Files Generated by SQL Profiler"
|Choose which events
you want to include in your trace. These are some of the new events.
See also the SQL Server Books Online - Monitoring with SQL Profiler Event Categories
Stored Procedures and TSQL
Completed events are important when you want to get performance measures of activities such as CPU time and logical disk reads, because performance measures are known only after activities have finished. For example, you would use Completed events to locate slow-running queries. In some situations, you'd want to capture both Starting and Completed events. For example, suppose you're troubleshooting a certain client application that experiences a lot of timeouts, such as lock request time out period exceeded errors. You need to pinpoint the queries that start but never finish. If you capture only Completed events, you won't get the queries that experienced timeouts because those queries never completed. However, you could look for Starting events that don't have corresponding Completed events to find the queries that never finished.
You can divide Starting
and Completed events by their level of detail; we call these events the
less granular (not so detailed) and more granular (very detailed) events.
|DATA COLUMNS TAB|
See also the SQL Server
Books Online - Monitoring with SQL Profiler Event Categories and Monitoring
Server Performance and Activity
On the Data Columns tab, your
results are grouped by the order in which you select the columns. You
will not get a seperate caption for each group.
Data columns generally fall into three groups: performance, informative, and event-specific.
Let you limit the events that
a trace collects.
Exclude system IDs
As a rule, filter out what you're certain you don't need, but if you're not sure, don't specify a filter. By reopening the trace file and using post-filters, you can examine the trace output more dynamically.