SQL Server 7.0 Effective Usage of SQL Server Profiler Ajay Manchepalli and Sri Kasam SQL Server Supp - PowerPoint PPT Presentation

1 / 30
About This Presentation
Title:

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

Description:

A Graphical Tool used to monitor Microsoft SQL Server engine events. Profiler Scenarios ... Pre filtering events is critical ... – PowerPoint PPT presentation

Number of Views:1187
Avg rating:3.0/5.0
Slides: 31
Provided by: supportM
Category:

less

Transcript and Presenter's Notes

Title: SQL Server 7.0 Effective Usage of SQL Server Profiler Ajay Manchepalli and Sri Kasam SQL Server Supp


1
SQL Server 7.0Effective Usage of SQL Server
Profiler Ajay Manchepalli and Sri Kasam SQL
Server SupportMicrosoft
2
What is Profiler ?
  • A Graphical Tool used to monitor Microsoft SQL
    Server engine events

Profiler Scenarios
  • Monitoring SQL Server activity
  • Performance tuning
  • Diagnosing problems
  • Debugging applications
  • Index Tuning Wizard
  • Replay SQL Server activity - simulation

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

4
SQL Trace Architecture
Event Controller (Queue management)
Event Consumers
Event Producers
ODS
Event Queues (filtering and routing)
SQL Server Profiler
Query Processor
Flat file
Storage Engine
Table
Lock manager
Log manager
Event log
Error Log
Remote SQL Server
User defined...
Your app here...
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
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
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 SPStarting SPCompleted
    SPStmtStarting SPStmtCompleted
  • Transactions
  • Events DTCTransaction SQLTransaction
    TransactionLog
  • Data Columns Binary Data, Event Sub Class
  • TSQL
  • Events RPCStarting RPCCompleted
    SQLBatchStarting SQLBatchCompleted
    SQLStmtStarting SQLStmtCompleted
  • Data Columns Integer Data

8
User Configurable Events
  • Profiler provides five User Configurable event
    classes
  • An Example
  • CREATE TABLE tab1 ( Col1 INT, Col2 CHAR(10) )
  • CREATE TRIGGER Trig1 ON Tab1 FOR INSERT AS
  • EXEC MASTER..xp_trace_generate_event
  • _at_event_class 82, _at_integer_data 1,
  • _at_event_text_data Inserts into Tab1,
  • _at_application Trigger Fired Trig1
  • INSERT INTO TAB1 VALUES ( 1, ABC )

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
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
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
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
Using Profiler Trace Wizard
  • Step-by-step method of defining a trace
  • From Profiler menu
  • Select Tools gt Create Trace Wizard
  • From Enterprise Manager
  • Select Tools gt 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
Scenarios (1)
  • Baseline performance
  • CursorOpen and CursorExecute and
    CursorImplicitConversion
  • SPRecompile
  • RPCStarting and RPCCompleted
  • SQLStmtStarting and SQLStmtCompleted
  • Attention
  • Sessions
  • Exec Prepared SQL and Prepare SQL
  • SPStarting and SPCompleted
  • SPStmtStarting and SPStmtCompleted
  • 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
Scenarios (2)
  • Slow running queries
  • SQLStmtCompleted RPCCompleted
  • Sessions
  • Exceptions Missing Column Statistics Sort
    Warnings Missing Join Predicate
  • Execution Plan
  • SQLStmtStarting RPCStarting
  • SQLBatchStarting SQLBatchCompleted
  • Stored Procedures SQL Operators Scans Objects
    Locks
  • Stored procedure problems
  • SPStarted SPCompleted SPStmtCompleted
    SPRecompile
  • Sessions
  • Exceptions Missing Column Statistics Sort
    Warning
  • SPStmtStarting
  • TSQL, Transactions, Scans, Objects, Locks,
    Cursors
  • Pre Filter by Database ID SPID SQL User
    Name NT User Name Text Host Name

16
Scenarios (3)
  • Deadlocking issues
  • RPCStarting SQLBatchStarting
  • LockDeadlock and LockDeadlockChain
  • Sessions
  • SPStarting SQLStmtStarting SQLStmtCompleted
  • SPCompleted SPRecompile RPCCompleted
  • High CPU utilization issues
  • RPCCompleted SQLStmtCompleted
  • Sessions
  • SPCompleted SPStmtCompleted
  • SQLStmtStarting SQLBatchStarting
    SQLBatchCompleted SPStarting SPStmtStarting
  • 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
Scenarios (4)
  • Application logic related issues
  • SQLBatchCompleted RPCCompleted
  • Sessions
  • Exception
  • SPCompleted
  • SPStarting SPStmtStarting SPStmtCompleted
    SQLBatchStarting SQLStmtStarting
    SQLStmtCompleted RPCStarting
  • Locks Misc Objects Scans SQL Operators
    Transactions
  • Understanding failure events
  • SQLStmtStarting SQLStmtCompleted
    RPCStarting RPCCompleted
  • Sessions
  • Exception
  • SPStmtStarting SPStmtCompleted SPStarting
    SPCompleted SQLTransaction
  • SQLBatchStarting SQLBatchCompleted
  • Locks Misc Objects Scans SQL Operators

18
Scenarios (5)
  • Reverse engineering
  • SQLBatchStarting RPCStarting
  • Sessions
  • SPStarting
  • 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
  • SPStmtStarting and SPStmtCompleted 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
Analyzing Traces (1)
  • Baseline for performance issues
  • Group By Event Class to understand event
    distribution such as
  • SPRecompile
  • Attention
  • Exec Prepared SQL and Prepare SQL
  • Missing Column Statistics
  • Include only events such as
  • SPCompleted SQLStmtCompleted RPCCompleted
  • 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
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)
  • SPStmtCompleted (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 ltTrace TableNamegt
  • WHERE Duration gt 1000
  • (Or TextData like Text
  • Or EventClass like SPRecomplie
  • Or StartTime between 2000-04-29 1645 and
    2000-04-29 1715
  • ORDER BY Duration (Or CPU or TextData ) DESC

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
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
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
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 gt 5 (secs) These parameters
    can be found under
  • Select Trace Properties window
  • Click icon next to SQL ServerltServer_Namegt
  • 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 _at_Max_Items, _at_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
Known Issues and Suggestions (2)
  • In trace output gt Properties gt 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
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
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 gt 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
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 gtgt
    Administering SQL Server gtgt Monitoring Server
    Performance )
  • Index Tuning Wizard for SQL Server 7.0 found at
    http//www.microsoft.com/SQL/productinfo/indextuni
    ng.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
  • Q A

30
(No Transcript)
Write a Comment
User Comments (0)
About PowerShow.com