The document provides a step-by-step guide on using SQL Server Profiler to monitor and troubleshoot SQL queries and stored procedures. It describes how to start the SQL Profiler, select events to monitor, and filter trace information. Key events to capture slow queries include RPC Completed, SP: StmtCompleted, SQL: Batch Starting, and SQL: Batch Completed. The SQLProfilerSP_Counts template can be used to identify heavily used stored procedures.
The document provides a step-by-step guide on using SQL Server Profiler to monitor and troubleshoot SQL queries and stored procedures. It describes how to start the SQL Profiler, select events to monitor, and filter trace information. Key events to capture slow queries include RPC Completed, SP: StmtCompleted, SQL: Batch Starting, and SQL: Batch Completed. The SQLProfilerSP_Counts template can be used to identify heavily used stored procedures.
The document provides a step-by-step guide on using SQL Server Profiler to monitor and troubleshoot SQL queries and stored procedures. It describes how to start the SQL Profiler, select events to monitor, and filter trace information. Key events to capture slow queries include RPC Completed, SP: StmtCompleted, SQL: Batch Starting, and SQL: Batch Completed. The SQLProfilerSP_Counts template can be used to identify heavily used stored procedures.
The document provides a step-by-step guide on using SQL Server Profiler to monitor and troubleshoot SQL queries and stored procedures. It describes how to start the SQL Profiler, select events to monitor, and filter trace information. Key events to capture slow queries include RPC Completed, SP: StmtCompleted, SQL: Batch Starting, and SQL: Batch Completed. The SQLProfilerSP_Counts template can be used to identify heavily used stored procedures.
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 11
SQL Server Profiler: Step by Step Guide
MS SQL Server Profiler is used to analyze log running queries and stored procedures. SQL Server profiler is used to debug, troubleshoot, and monitor SQL queries and stored procedures. You can select the events you want to monitor. You can also filter the trace information which is helpful when trace log is very large.
How to Run SQL SERVER PROFILER:
Start -> All programs -> Microsoft SQL SERVER 2008 R2 -> SQL Server Management Studio > Tools -> SQL PROFILER Another method to run SQL SERVER PROFILER in SQL SERVER, click on Start -> run ->SSMS -> Tools -> SQL Profiler Once after successful login, in TOOLS we can see SQL Profiler. Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring TSQL Statements of Database Engine. We can save and reuse the trace. We can do the following using SQL Server Profiler:
Create a trace
Watch the trace results as the trace runs
Store the trace results in a table
Start, stop, pause, and modify the trace results as necessary
Replay the trace results
Use SQL Server Profiler to monitor only the events in which you are interested. Menu Path: Start ->All Programs ->Microsoft SQL Server 2005 ->Performance Tools ->SQL Server Profiler
1 | Page
SQL Server Profiler: Step by Step Guide
The below screen will appear:
Click on <Connect> Button. New Properties Screen will appear:
2 | Page
SQL Server Profiler: Step by Step Guide
It has two selection tabs:
General: It is used for general setting for Trace Database Engine.
Event: It is used to add or remove events to monitor.
General Tab: Section 1: In this section, you have to specify the name of trace, Trace provider name and trace provider type are predefined and based upon SQL Server version. Section 2: It is the template section. You can choose different Templates based upon requirements. It is the configuration for trace. By default, it is Standard (Default) templates. Others templates are SQLProfilerSP_Counts, SQLProfilerStandard, SQLProfilerTSQL, SQLProfilerTSQL_Duration, SQLProfilerTSQL_Grouped, SQLProfilerTSQL_Replay, SQLProfilerTSQL_SPs, and SQLProfilerTuning. You can create custom Templates by selecting different Events and Event Class. Template file is saved with .tdf extension.
3 | Page
SQL Server Profiler: Step by Step Guide
Section 3: This section has option to save trace. Either as file (.trc) or in a table. While clicking on Save to file check box, File save dialog box should open and save file (with .trc extension). If Save to Table option is checked, it will connect with SQL server, mention database name to save trace table information.
Section 4: You can stop trace at particular time. Check the Enable trace stop time checkbox; enter the time to stop trace. SQL Server will automatically stop trace at defined time. Event Tab: An Event is an action or operation that is performed in your SQL Server 2008 Database Engine. Some examples of Events are:
Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
User login and logout
4 | Page
SQL Server Profiler: Step by Step Guide
Execution of Stored procedures
Operation with cursor
SQL Server profiler is able to trace all of these events and all these events are categorized on a particular Event class.
In section 1, select the Events check box based upon our requirement, section 2 will display the details of Selected events and Events class. If you check in the check box of section 3, you will get all the list of Events and Columns in Section 1. Section 4 is something like customization. Just click on the Column Filter Button. In this section, you can specify some condition (like or not like).
5 | Page
SQL Server Profiler: Step by Step Guide
By clicking on Organize Column button, the sequence of order of selected events can be changed. Now Click on Run Button, Trace window will be displayed:
Using these windows, you will get the detailed time duration of a query and all other events information that has been selected. Results can be saved for future use. Or you can extract a particular query from the trace, just right click and click on Extract Event Data, and save this as a SQL Script. 6 | Page
SQL Server Profiler: Step by Step Guide
Tracking Heavily Used Stored Procedures: If the application uses numerous stored procedures, you may want to target your fine-tuning to those stored procedures that are used most heavily. The SQLProfiler SP_Counts template aids in this process, as described in the steps that follow. To track heavily used Stored Procedures 1. Start SQL Profiler. 2. On the File menu, click New, and then click Trace. 3. Specify the relevant connection details to connect to the computer running SQL Server. 4. The Trace Properties dialog box appears. 5. In the Template name list, select SQLProfilerSP_Counts. 6. Click Run to run the trace. Running the trace displays the following events: EventClass, ServerName, DatabaseID, ObjectID and SPID. The most frequently referenced ObjectIDs are listed first. 7. You can now run your client application and begin database profiling. Figure 2 shows sample output.
Identifying Stored Procedures:
To find the name of the stored procedure given a specific ObjectID, you can use the following query. Replace <ObjectID> with the value you see in the SQL Profiler output window. Select name from sysobjects where id = <ObjectID>
Identifying Execution Duration:
7 | Page
SQL Server Profiler: Step by Step Guide
If you want to find out how long a stored procedure took to run, you need to capture the SP:Completed event. To identify the execution duration of individual statements within a stored procedure, use SP:StmtCompleted. To identify stored procedure execution duration 1. Stop the trace if it is already running. 2. On the File menu, click Properties. 3. On the Events tab, expand the Stored procedures event class present in the Available Event Class list box and select SP:Completed. 4. Click Add, and then click Run. 5. Run the stored procedures you want to analyze. For example, using SQL Query Analyzer and the Pubs database: 6. sp_help authors SQL Query Analyzer displays a list of stored procedure executions. To identify statement execution duration within a stored procedure 1. Stop the trace if it is already running. 2. On the File menu, click Properties. 3. On the Events tab, expand the Stored Procedures event class and select SP:StmtCompleted. 4. Click Add, and then click Run. SQL Profiler shows statement and stored procedure execution. 5. Run the stored procedures you want to analyze. For example, using SQL Query Analyzer and the Pubs database: 8 | Page
SQL Server Profiler: Step by Step Guide
5. sp_help authors SQL Query Analyzer displays a list of statement executions. A stored procedure can be executed in two different ways:
An RPC Event Remote Procedure call
A Transact SQL Event as a result of a call to the T-SQL Execute Statement
In order to troubleshoot slow performing queries capture the following events
RPC Completed : This event includes the information about the execution of stored procedure including the Duration, CPU, Reads, Writes together with the name of the stored procedure that ran. This event does not fire for Transact-SQL statements that occur outside the stored procedure
SP : stmt Completed : This event tells us when a statement within a stored procedure has completed. It provides us events Duration, CPU, Reads, Writes. A single Stored Procedure may contain a single or many individual statements. For example, if there are five SELECT statements, the there will be five stmt Completed events for that stored procedure. This event does not fire for Transact-SQL statements that occur outside the stored procedure
SQL: Batch Starting: This event id fired whenever a new Transact SQL batch begins, whether the transact SQL statement is inside or outside a stored procedure. This event is used because if there is any new stored procedure or Transact SQL statement, we can identify a new chain of query related event begins.
SQL: Batch Completed: This event occurs when a Transact SQL statement completes, whether the transact SQL statement is inside or outside a stored procedure. If the event is a stored procedure SQL Batch Completed provides the name of the store procedure along with the Duration, CPU, Reads, Writes of the statement
SQL: Batch Completed provides event data when a Transact-SQL statement is run outside a stored procedure and SP : stmt Completed to see more easily is inside a stored procedure. 9 | Page
SQL Server Profiler: Step by Step Guide
Row 1 shows the SQL: Batch starting event, it indicates when a stored procedure is called along with its name. Row 2 shows the showplan XML event, which provides a graphical execution plan of the T-SQL that was executed. Row 3 shows SP: Stmt Completed event shows the actual code that has run along with the time taken by the event(Duration) Row 4 shows SQL: Batch Completed event indicates that the stored procedure has completed along with Duration, CPU, Reads, Writes for the entire stored procedure If there is an event related to the firing of a single Transact-SQL statement that is not inside a stored procedure.
Row 1 shows the SQL: Batch Starting event
Row 2 shows the showplan XML event, which provides a graphical execution plan of the T-SQL that was executed. Row 3 shows SQL:BatchCompleted event which repeats the T-SQL code in the textdata column, which includes Duration, CPU, Reads, Writes for the event.
