SQL Server 7.0 Effective Usage of SQL Server Profiler Ajay Manchepalli and Sri Kasam SQL Server Support Microsoft

1. SQL Server 7.0Effective Usage of SQL Server Profiler Ajay Manchepalli and Sri Kasam SQL Server SupportMicrosoft

2. 2

3. 3 Terminology Event Event producers Event consumers Event queues Event class Data column Event category Event filter

4. SQL Trace Architecture

5. 5 Data Columns Minimum set of Data columns while defining a trace: Start Time, End Time, Duration SPID, Connection ID Event Class, Event Sub Class Text Integer Data, Binary Data CPU, Reads, Writes Application Name, NT User Name, SQL User Name, Host Name Other Data columns based on specific scenario: Database ID Index ID, Object ID Severity Pre filtering Data columns is not critical Pre filtering events is critical Also, understanding Event-to-Data column relationship is crucial

6. 6 Event Categories (1) Cursors Events: Open; Close; ImplicitConversion Data columns: Integer Data; Binary Data; Event Sub Class Errors and Warnings Events: Errorlog; Eventlog; Exception; Missing Column Statistics Data Columns: EventSubClass; Severity; Integer Data Locks Events: Acquired; Deadlock; Deadlock Chain Data Columns: Binary Data; Integer Data; Object ID; Index ID Event Sub Class Misc. Events: Attention; Auto-UpdateStats; Execution Plan; Exec Prepared SQL; Prepare SQL; Unprepare SQL Data Columns: Binary Data; Integer Data; Event Sub Class Scans Events: Started; Stopped Data Columns: Event Sub Class; Index ID; Object ID

7. 7 Event Categories (2) Sessions Events: Connect; Disconnect; ExistingConnection Data Columns: Binary Data SQL Operators Events: Select; Insert; Update; Delete Data Columns: Event Sub Class Stored Procedures Events: SP:Starting; SP:Completed; SP:StmtStarting SP:StmtCompleted Transactions Events: DTCTransaction; SQLTransaction; TransactionLog Data Columns: Binary Data, Event Sub Class TSQL Events: RPC:Starting; RPC:Completed; SQL:BatchStarting SQL:BatchCompleted; SQL:StmtStarting; SQL:StmtCompleted Data Columns: Integer Data

8. 8 User Configurable Events

9. 9 Creating Traces Creating a trace using: Profiler ( Includes Profiler Trace Wizard ) Extended stored procedures ( XPs ) Defining a trace – using Profiler Select “All Event Classes” and “All Data Columns” Name the trace and specify the server to be profiled Pick appropriate “Events” and “Data Columns” Define filters Save trace output to: File ( recommended ) Table Application log ( using XPs only ) Forward to another Server ( using XPs only ) Saving Trace Definition File ( TDF ) Export and Import options

10. 10 Filtering Data Pre filtering Filter by Events and Data columns Pros: Reduces output size Reduces impact on performance degradation Increases system stability Simplifies post filtering operations Cons: Increases the possibility of not capturing vital information Post filtering Further filter by events and Data columns Group by Data columns Reorganize by Data columns Create new trace files after filtering the original trace output Output to a table and perform powerful DML operations Detailed later

11. 11 Using Extended Stored Procedures (1) Create New Trace Queue xp_trace_addnewqueue Specify Events to be captured xp_trace_seteventclassrequired Specify Event Filters xp_trace_setappfilter Specify Destination for Captured Event Data xp_trace_setqueuedestination Start Consumer xp_trace_startconsumer Pause a trace xp_trace_pausequeue Restart a paused trace xp_trace_restartqueue

12. 12 Using Extended Stored Procedures (2) Define a Filter Use xp_trace_set% ( Refer BOL ) Save a Trace Definition File ( TDF ) xp_trace_savequeuedefinition Load a saved TDF xp_trace_loadqueuedefinition Retrieve all queue handles defined xp_trace_enumqueuehandles Auto start trace at server start time xp_trace_setqueueautostart Define Queue History xp_trace_setqueryhistory Extract Queue History xp_trace_flushqueryhistory

13. 13 Using Profiler Trace Wizard Step-by-step method of defining a trace From Profiler menu Select Tools > Create Trace Wizard From Enterprise Manager Select Tools > Wizards Expand Management and select Create Trace Wizard Useful pre-defined scenarios: Find the worst-performing queries Identify scans of large tables Identify the cause of a deadlock Profile the performance of a stored procedure Trace Transact-SQL (T-SQL) activity by application Trace Transact-SQL activity by user Good place to start Based on the specific scenario Add additional Events / Data columns Define pre filters

14. 14 Scenarios (1) Baseline performance CursorOpen and CursorExecute and CursorImplicitConversion SP:Recompile RPC:Starting and RPC:Completed SQL:StmtStarting and SQL:StmtCompleted Attention Sessions Exec Prepared SQL and Prepare SQL SP:Starting and SP:Completed SP:StmtStarting and SP:StmtCompleted SQL Operators; Scans; Objects; Misc; Locks; Transactions Errors and Warnings Application performance Similar to above scenario Pre filter by application name Pre filter by host name

15. 15 Scenarios (2) Slow running queries *** SQL:StmtCompleted; RPC:Completed Sessions Exceptions; Missing Column Statistics; Sort Warnings; Missing Join Predicate Execution Plan SQL:StmtStarting; RPC:Starting SQL:BatchStarting; SQL:BatchCompleted Stored Procedures; SQL Operators; Scans; Objects; Locks Stored procedure problems *** SP:Started; SP:Completed; SP:StmtCompleted; SP:Recompile Sessions Exceptions; Missing Column Statistics; Sort Warning SP:StmtStarting TSQL, Transactions, Scans, Objects, Locks, Cursors *** Pre Filter by Database ID; SPID; SQL User Name; NT User Name; Text; Host Name

16. 16 Scenarios (3) Deadlocking issues RPC:Starting; SQL:BatchStarting Lock:Deadlock and Lock:DeadlockChain Sessions SP:Starting; SQL:StmtStarting; SQL:StmtCompleted SP:Completed; SP:Recompile; RPC:Completed High CPU utilization issues RPC:Completed; SQL:StmtCompleted Sessions SP:Completed; SP:StmtCompleted SQL:StmtStarting; SQL:BatchStarting; SQL:BatchCompleted; SP:Starting; SP:StmtStarting Execution Plan; SQL Operators; Scans; Objects; Locks Check sp_configure settings Use Performance Monitor; master..sysprocesses output; other DBCC commands such as DBCC inputbuffer and DBCC pss Trace using XPs in place of profiler Pre filter to include only required events

17. 17 Scenarios (4) Application logic related issues SQL:BatchCompleted; RPC:Completed Sessions Exception SP:Completed SP:Starting; SP:StmtStarting; SP:StmtCompleted; SQL:BatchStarting; SQL:StmtStarting; SQL:StmtCompleted; RPC:Starting Locks; Misc; Objects; Scans; SQL Operators; Transactions Understanding failure events SQL:StmtStarting; SQL:StmtCompleted; RPC:Starting; RPC:Completed Sessions Exception SP:StmtStarting; SP:StmtCompleted; SP:Starting; SP:Completed; SQLTransaction SQL:BatchStarting; SQL:BatchCompleted Locks; Misc; Objects; Scans; SQL Operators

18. 18 Scenarios (5) Reverse engineering SQL:BatchStarting; RPC:Starting Sessions SP:Starting Pre Filter by App Name; Host Name Errors and Warning; Locks; Misc; Objects; Scans; SQL Operators; Transactions Key points XYZCompleted event is important to get duration, cpu and related XYZStarted event is important to capture timeout errors, hangs, failed events, and statements that never complete Pre filter assists in reducing the impact of using profiler, but a careful balance should be picked so as to not miss out on vital information SP:StmtStarting and SP:StmtCompleted should be picked cautiously as it can result in huge trace files Same is the case with many other events such as Execution Plan, Locks, Objects, Scans, SQL Operators; Transaction Log

19. 19 Analyzing Traces (1) Baseline for performance issues Group By Event Class to understand event distribution such as SP:Recompile Attention Exec Prepared SQL and Prepare SQL Missing Column Statistics Include only events such as: SP:Completed; SQL:StmtCompleted; RPC:Completed Identify and group long running; CPU intensive; I/O intensive queries using Group By Duration CPU Reads, Writes Re-order the Data columns to make analysis easy, and the ordering depends on the specific problem scenario. As an example: Event Class, Text, Start Time, Duration, SPID, CPU, Reads, Writes, Application Name

20. 20 Analyzing Traces (2) Once the problem is narrowed Depending on what has been captured, one may have to re-run the trace to obtain specific data such as Execution Plan (To analyze identified problem queries) SP:StmtCompleted (To identify problem section of a stored procedure ) Increase filtering capability by moving trace output to a table This enables DML operations against the trace output *** Group By, Order By and Aggregate operations cannot be used against the TextData Column An Example: SELECT EventClass, TextData, SPID, Duration, CPU, Reads, Writes FROM <Trace TableName> WHERE Duration > 1000 (Or TextData like ‘%Text%’ Or EventClass like ‘%SP:Recomplie%’ Or StartTime between ‘2000-04-29 16:45’ and ‘2000-04-29 17:15’ ORDER BY Duration (Or CPU or TextData ***) DESC

21. 21 Replaying a Trace (1) Execute contents of a trace file against SQL Server From Profiler Menu : Select Replay Replay Requirements Event classes Data columns Sample 6 – T-SQL for replay Replay options Synchronization levels Full / Partial / No Sync Replay rate As fast as possible Maintain Interval between events Maintain relationship to start time

22. 22 Replaying a Trace (2) Single-Stepping Traces A single event at a time To a breakpoint To a cursor SQL Profiler cannot replay traces when events: Are captured from connections that connected using Windows NT® Authentication Contain replication and transaction log activity Contain operations on text, ntext, and image columns involving BCP, Bulk Insert and full-text operations Contain sp_getbindtoken and sp_bindsession - session binding system stored procedures From SQL 6.5 trace .log files contains server-side cursor statements (like sp_cursor)

23. 23 Index Tuning Wizard (ITW) Helps identify optimal set of indexes and statistics Requires workload such as Profiler trace file or table Given a work load, ITW: Recommends the best mix of indexes Analyzes the effects of the proposed changes Recommends ways to tune the database for a small set of problem queries Customizes recommendations via advanced options like disk space constraints Recommendations not only include creation of new indexes, but also dropping of ineffective indexes For more information, please refer to the white paper mentioned in the References section

24. 24 Known Issues and Suggestions (1) On a server under high stress, it should help to: Increase “Number of Rows to buffer on server” value from 1000 (default) to 8000. Also make sure “Server Timeout” is > 5 (secs) These parameters can be found under: Select “Trace Properties” window Click icon next to SQL Server:<Server_Name> Use XPs to trace instead of using Profiler While using XPs make sure the “timeout” is 5000 (msec) or greater and “queue size” is around 8000 xp_trace_addnewqueue @Max_Items, @Timeout,… Carefully pick the events to be traced Do not have more than one trace executing at any given time Avoid tracing to a table Make sure to apply the latest SQL Server service pack SQL 7.0 BOL has a misprint: “Timeout Period” for xp_trace_addnewqueue is shown as seconds; it should read milliseconds

25. 25 Known Issues and Suggestions (2) In trace output > Properties > Events Tab You may not see all the events you originally requested Only the events that occurred will be listed To confirm that the trace was started with all requested events, include “Event Sub Class” Data Column in the TDF At the very start of the trace output you will see Events “EventRequired,” and the DataColumn “EventSubClass” will contain the events that have been requested to be captured Scenarios where it is better to use XPs than Profiler On very busy servers ( high CPU and memory ) To have better control over size of trace file To be able to schedule as a job To enable forwarding of events to a remote server To log exceptions to application event log To create user-configurable events

26. 26 Tips and Tricks (1) SQL Server 6.5 trace output files can be used to replay against a 7.0 server using Profiler To save a 7.0 trc file as a text file: Save the trace file to a table Then BCP out to a file “Duration” Data column includes: Compile, Recompile, Execution and “Data transfer to a client” time While defining a trace for Replay, do not pre filter on: Dependent tables that have primary key/foreign key relationship While defining Group By on Data columns, include a filter on Text column to exclude: Profiler%, Disconnect%, Declare%, While%, SET% When a trace file is saved to a table, it is not possible to: Group By or Order By “textdata” column - as it is of type NText

27. 27 Tips and Tricks (2) Profiler does not support filtering on multiple SPIDs; therefore, first save to a table and then use DML Helpful keyboard shortcuts + Expands a trace grouping - Collapses a trace grouping Before defining a trace: Go to Tools menu > Options Check All Event Classes and All Data Columns In case of large trace files, after post filtering a trace, save the new, concise, and scenario-specific trace to a new file. SQL Server Profiler can display the Object Name instead of the Object ID if the Server Name and Database ID Data columns also appear in the trace

28. 28 Additional Resources “Troubleshooting SQL Server Profiler” section in SQL Server 7.0 Books Online “Monitoring with SQL Server Profiler” chapter in SQL 7.0 BOL ( Contents >> Administering SQL Server >> Monitoring Server Performance ) “Index Tuning Wizard for SQL Server 7.0” found at http://www.microsoft.com/SQL/productinfo/indextuning.htm Q214799 - INF: SQLProfiler From Command Line Parameters to Temp File Usage Q199037 - INF: Trapping Error Messages Sent to Clients from a SQL Server Q224587 - INF: Troubleshooting Application Performance with SQL Server Q243588 - INF: Troubleshooting Performance of Ad-Hoc Queries Q243589 - INF: Troubleshooting Slow-Running Queries on SQL Server 7.0 Q243586 - INF: Troubleshooting Stored Procedure Recompilation Q224453 - INF: Understanding and Resolving SQL Server 7.0 Blocking Problems

29. 29 Q & A

30. 30