Programming4us
         
 
 
SQL Server

SQL Server 2008 : SQL Server Profiler - Replaying Trace Data

3/13/2011 3:10:20 PM
To replay a trace, you must have a trace saved to a file or a table. The trace must be captured with certain trace events to enable playback. The required events are captured by default if you use the Profiler template TSQL_Replay. You can define a trace to be saved when you create or modify the trace definition. You can also save the current contents of the trace window to a file or table by using the Save As Trace File or Save As Trace Table options in the File menu.

To replay a saved trace, you choose File and then Open to open a trace file or trace table. After you select the type of trace to replay, a grid with the trace columns selected in the original trace is displayed. At this point, you can either start the replay of the trace step-by-step or complete execution of the entire trace. The options for replaying the trace are found under the Replay menu. When you start the replay of the trace, the Connect to Server dialog is displayed, enabling you to choose the server that you want to replay the traces against. When you are connected to a server, a Replay Configuration dialog like the one shown in Figure 1 is displayed.

Figure 1. Basic replay options.


The first replay option, which is enabled by default, replays the trace in the same order in which it was captured and allows for debugging. The second option takes advantage of multiple threads; it optimizes performance but disables debugging. A third option involves specifying whether to display the replay results. You would normally want to see the results, but for large trace executions, you might want to forgo displaying the results and send them to an output file instead.

If you choose the option that allows for debugging, you can execute the trace in a manner similar to many programming tools. You can set breakpoints, step through statements one at a time, or position the cursor on a statement within the trace and execute the statements from the beginning of the trace to the cursor position.

Note

Automating testing scripts is another important use of the SQL Profiler Save and Replay options. For instance, a trace of a heavy production load can be saved and rerun against a new release of the database to ensure that the new release has similar or improved performance characteristics and returns the same data results. The saved traces can help make regression testing much easier.


You also have the option of specifying advanced replay options in SQL Server 2008. These options are found on the Advanced Replay Options tab of the Replay Configuration dialog (see Figure 2).

Figure 2. Advanced replay options.


The first two options on the Advanced Replay Options tab relate to the system process IDs (SPIDs) targeted for replay. If the Replay System SPIDs option is selected, the trace events for every SPID in the trace file will be replayed. If you want to target activity for a specific SPID, you should choose the Replay One SPID Only option and select the SPID from the drop-down menu. You can also limit the events that will be replayed based on the timing of the events. If you want to replay a specific time-based section of the trace, you can use the Limit Replay by Date and Time option. Only those trace events that fall between the data range you specify will be replayed.

The last set of advanced options is geared toward maintaining the health of the server on which you are replaying the trace. The Health Monitor Wait Interval (sec) option determines the amount of time a thread can run during replay before being terminated. This helps avoid an excessive drain on the server’s resources. The Health Monitor Poll Interval (sec) option determines how often the health monitor will poll for threads that should be terminated. The last advanced option on the screen relates to blocked processes. When it is enabled, the monitor polls for blocked processes according to the interval specified.

Other -----------------
- SQL Server 2008 : SQL Server Profiler - Saving and Exporting Traces
- SQL Server 2008 : SQL Server Profiler - Creating Traces
- SQL Server 2008 : SQL Server Profiler Architecture
- SQL Server 2008: Administering Database Objects - Working with Tables (part 7) - Partitions
- SQL Server 2008: Administering Database Objects - Working with Tables (part 6) - Compression
- SQL Server 2008: Administering Database Objects - Working with Tables (part 5) - Sparse Columns
- SQL Server 2008: Administering Database Objects - Working with Tables (part 4) - Check Constraints
- SQL Server 2008: Administering Database Objects - Working with Tables (part 3) - Foreign Key Constraints
- SQL Server 2008: Administering Database Objects - Working with Tables (part 2) - Primary Key Constraints & Unique Constraints
- SQL Server 2008: Administering Database Objects - Working with Tables (part 1) - Default Constraints
- SQL Server 2008: Administering Database Objects - Working with Database Snapshots
- Programming with SQL Azure : WCF Data Services (part 3)
- Programming with SQL Azure : WCF Data Services (part 2) - Creating the Client Application
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 4) - EXPLICIT Mode
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 3) - AUTO Mode
- Programming with SQL Azure : WCF Data Services (part 1)
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 2) - Working with Binary Columns
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 1) - RAW Mode
- Programming with SQL Azure : Connecting to SQL Azure (part 4) - Sqlcmd
- Programming with SQL Azure : Connecting to SQL Azure (part 3) - ODBC
 
 
Most View
- iPad SDK : Preparing Dudel for a New Tool (part 5) - Rendering Multiple Styles
- SOA with .NET and Windows Azure : WCF Discovery (part 1) - Discovery Modes
- jQuery 1.3 : Developing plugins - Method parameters
- Windows Phone 7 : Pinning Favorites to Start
- Sharepoint 2010 : Content Management - Importing a Term Set
- SharePoint 2010 : Create a Personal Site
- Windows 7 : Connecting to the Remote Desktop (part 1) - Making a Basic Connection
- Security in Cloud Computing (part 2) - Identity and Access Management
- Windows 7 : Working with Users and Groups from the Command Line
- What's New in SharePoint 2013 (part 1) - THE PROGRAMMING MODEL
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS