Whats New in Microsoft SQL Server 2000 Profiler Sri Kasam and Ajay Manchepalli SQL Server Support Mi

1
Whats New in Microsoft SQL Server 2000
ProfilerSri Kasam and Ajay ManchepalliSQL
Server SupportMicrosoft Corporation
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
• Auditing
• Replay facility as a stress tool

3
Terminology

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

4
SQL Trace Architecture
Queue manager
SQL Trace
C O N S U M E R S
Event Producers
Self Describing Format
Client manager
Event queues (filtering and routing)
Trace file
Query processor
Trace rowset
Storage engine
Lock manager
Log manager
Error log
User defined...
5
SQL 2000 Profiler

• Rewritten the server and client implementation
• No more xp_trace
• SQL Trace server side
• More stable, guaranteed events captured
• Uses server engine I/O API
• SQL Profiler client side
• Not guaranteed to capture all events
• Has an option to generate using SQL Trace

6
SQL Trace

• Integrated with engine
• UMS (scheduler)
• I/O (writing server side files)
• Native server-side files
• New Trace options
• Time and size-based trace
• File roll over on size n
• Shutdown on error
• Changes
• AutoStart now using stored procedure
• C2 auditing
• Black box trace (flight recorder)

7
SQL Profiler

• Supports tracing 7.0 and 2000
• Profiler templates
• Scenarios and canned trace definitions like
  deadlock, replay, tuning, and others
• Replaces Create Trace Wizard and Trace
  Definitions saved in registry
• Import and export trace definitions as SQL Script
• Save As SQL Server 7.0 and SQL Server 2000
  script
• Asynchronous file loading in Profiler UI
• Scalability and performance improvements
• Connection based like SQL Query Analyzer
• Support for server-side trace file creation
• Rewritten replay
• Increased reliability and better synchronization

8
Data Columns

• Minimum set of data columns for a trace to be
  useful are
• Start time, end time, duration
• SPID, connection ID
• Event class, event subclass
• Text
• Integer data, binary data
• CPU, reads, writes
• Application name, Microsoft Windows NT user
  name, SQL user name, host name
• Pre-filtering data columns is not critical
• Pre-filtering events is critical
• Understanding event-to-data column relationship
  is important

9
Event Categories (1)

• Cursors
• Events Open, Close, Implicit Conversion
• Data Columns Integer Data, Binary Data, Handle
• Database
• Events Data and Log File autogrow, autoshrink
• Data Columns Duration, File Name, Integer Data
• Errors and Warnings
• Events Attention, Errorlog, Eventlog, Exception,
  Hash warning, Missing Column Statistics, Sort
  warning
• Data Columns EventSubClass, Severity, Integer
  Data
• Locks
• Events Acquired, Deadlock, Deadlock Chain
• Data Columns Binary Data, Integer Data, Object
  ID, Index ID, Event Subclass

10
Event Categories (2)

• Objects
• Events Auto Stats
• Performance
• Events Degree of parallelism (7.0), Execution
  Plan, Show Plan All, Show Plan Text, Show Plan
  Statistics
• Data Columns Event Subclass, Integer Data,
  Binary Data
• Scans
• Events Started, Stopped
• Data Columns Event Sub Class, Index ID, Object
  ID
• Security Audit
• Events Audit Login Event, Audit Logout Event,
  Audit Login Failed Event, Audit DBCC Event, Audit
  Add DB User Event
• Data Columns Subclass, Text Data, Binary
  Data, Success
• Sessions
• Events ExistingConnection
• Data Columns Binary Data

11
Event Categories (3)

• Stored Procedures
• Events RPCStarting, RPCCompleted, SPStarting,
  SPCompleted, SPRecompile, SPStmtStarting,
  SPStmtCompleted
• Data Columns Start Time, End Time, Duration,
  CPU, Reads, Writes, Text Data
• Transactions
• Events DTCTransaction, SQLTransaction,
  TransactionLog
• Data Columns Binary Data, Event Sub Class
• TSQL
• Events Exec Prepared SQL, Prepare SQL,
  SQLBatchStarting, SQLBatchCompleted,
  SQLStmtStarting, SQLStmtCompleted
• Data Columns Start Time, End Time, Duration,
  CPU, Reads, Writes, Text Data, Integer Data,
  Binary Data, Handle

12
User Configurable Events

• Profiler provides nine User Configurable event
  classes UserConfigurable(0-9)
• An example
• CREATE TABLE Tab1(Col1 INT, Col2 CHAR(10))
• CREATE TRIGGER Trig1 ON Tab1 FOR INSERT AS
• EXEC MASTER.. sp_trace_generateevent
• _at_event_id 82, _at_user_info N'Trigger Fired',
• _at_user_data 0x1
• Note All Parameters are Strongly Typed
• INSERT INTO TAB1 VALUES ( 1, ABC )

13
Creating Traces

• Creating a trace using
• Profiler ( Includes Profiler Trace Templates )
• System Stored Procedures
• Defining a Trace Using Profiler
• Connect to any SQL Server (need not be
  registered)
• Name the trace and specify the Server to be
  traced
• Choose an appropriate Trace Template
• Add more Events and Data Columns if needed
• Define Filters In Place Editing filters
• Save trace output to
• File (recommended)
• Table
• Specify a predefined time to stop tracing

14
Saving Trace Output

• To file
• Maximum file size (in megabytes)
• Enable file rollover
• New Files created automatically on max size
• Server processes SQL Server trace data
• All of the tracing and writing to file is done on
  the SQL Server by SQL Trace
• To table
• Saving to a table is client side by Profiler
• No stored procedure to directly trace into a
  table, first to a trace file and then to a
  database table
• Max rows (in thousands )

15
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
• Re-organize by Data columns
• Create new trace files after filtering the
  original trace o/p
• Output to a table and perform powerful DML
  operations
• Detailed later

16
Using System Stored Procedures and Functions (1)

• Create a trace definition
• sp_trace_create
• Specify black box, shutdown on error, rollover
  option
• Add or remove an event or data column to trace
• sp_trace_setevent
• Applies a filter to a trace
• sp_trace_setfilter
• Modify the current state of the specified trace
• sp_trace_setstatus
• Create a user-defined event
• sp_trace_generateevent

17
Using System Stored Procedures and Functions (2)

• To get all the traces running on the SQL Server
• SELECT FROM fn_trace_getinfo(default)
• Gather information about events and data columns
  for a specific Server trace
• SELECT FROM fn_trace_geteventinfo(_at_trace_id)
• Gather information about filters applied to a
  specific Server trace
• fn_trace_getfilterinfo
• Returns trace file information in a table format
• fn_trace_gettable
• Use this to load trace file to a database table,
  because no stored procedures exist for putting
  trace data directly to a table

18
System Stored Procedures Example

• DECLARE _at_RC int, _at_TraceID int, _at_on BIT
• EXEC _at_rc sp_trace_create _at_TraceID output, 0,
  N'C\test7'
• SELECT RC _at_RC, TraceID _at_TraceID
• SELECT _at_on 1
• exec sp_trace_setevent _at_TraceID, 10, 1, _at_on
• exec sp_trace_setevent _at_TraceID, 10, 2, _at_on
• exec sp_trace_setevent _at_TraceID, 10, 3, _at_on
• exec sp_trace_setevent _at_TraceID, 10, 6, _at_on
• exec sp_trace_setevent _at_TraceID, 10, 7, _at_on
• exec sp_trace_setevent _at_TraceID, 10, 8, _at_on
• exec sp_trace_setevent _at_TraceID, 10, 9, _at_on
• exec sp_trace_setevent _at_TraceID, 10, 10, _at_on
• -- Set the Filter
• EXEC sp_trace_setfilter 1, 10, 0, 6, N'MS
• -- Start Trace (status 1 start)

19
Troubleshooting Traces Using SProcs

• Check for the Return Codes as soon as script is
  run
• No errors, only return codes ( selecting return
  code must)
• Books Online has good explanation for return
  codes (sp_trace_create )
• Generally check for duplicate filenames
• Syntax errors are caught at compile time
• File size not updated until the trace is stopped
  or if you have roll over enabled and the max file
  size is reached
• All parameters are strongly typed
• Example sp_trace_create _at_TraceID output, 2,
  N'C\test', _at_maxfilesize
• To know all traces running on the SQL Server
• SELECT FROM fn_trace_getinfo(default)
• First stop and then shut down the trace
  (sp_trace_setstatus)
• No errors if done the other way, but no action
  taken either
• Stopping the trace
• sp_trace_setstatus _at_traceId, 0
• To close and then delete its definition from
  server
• sp_trace_setstatus _at_traceId, 2

20
Using Profiler Trace Templates

• Replaces Profiler Trace Wizard
• From Profiler menu
• On the File menu, click New Trace Template
• Useful scenarios
• Standard scenarios provided
• Create custom templates as desired
• On the File menu, click Save As, and then click
  Trace Template
• Good place to start
• Based on the specific scenario
• Add additional Events and Data Columns
• Define prefilters

21
Scenarios (1)

• Baseline Performance
• CursorOpen and CursorExecute and
  CursorImplicitConversion
• SPRecompile
• Attention
• Audit login and logout, sessions
• Exec Prepared SQL and PREPARE SQL
• Errors and warnings
• SQLStmtCompleted, RPCCompleted
• Application Performance
• Similar to above scenario
• Pre-filter By Application name
• Pre-filter by Host name

22
Scenarios (2)

• Slow running queries
• SQLStmtCompleted, RPCCompleted
• Sessions, security audit
• Exceptions, missing column statistics, sort
  warnings, missing join predicate
• Execution plan, show plan statistics, show plan
  all
• SQLStmtStarting, RPCStarting
• SQLBatchStarting, SQLBatchCompleted
• Stored procedures, scans
• Stored procedure problems
• SPStarted, SPCompleted, SQLStmtCompleted,
  SPRecompile
• Sessions
• Exceptions, missing column statistics, sort
  warning
• Pre-filter by database ID, SPID, SQL user
  name, Windows NT user name, text, Host name

23
Scenarios (3)

• High CPU utilization issues
• RPCCompleted, SQLStmtCompleted
• Security audit, sessions
• Show plan, statistics, exec plan, scans
• Check sp_configure settings
• Use Performance Monitor, master..sysprocesses
  output, other DBCC commands such as DBCC
  inputbuffer
• Trace using system stored procedures instead of
  Profiler, pre-filter to include only required
  events

24
Scenarios (4)

• Auditing the server
• Adding events from Security Audit event category
• Audit Login, Audit Logout, Audit Login Failed,
  Audit Login Change Password Events
• Audit Add DB User, Audit Add Login to Server
  Role, Audit Statement GDR events
• Setting the C2 Security mode
• sp_configure 'c2 audit mode', 1
• Server shuts down if it cant write to the audit
  trace
• Default location of trace file \Data\directory
• Creates a new trace file every 200 MB
• No flexibility on limiting events to trace
• May cause performance degradation
• Start SQL Server with f to disable auditing

25
Scenarios (5)

• Reverse Engineering
• SQLBatchStarting, RPCStarting
• Security Audit, Sessions
• Pre-filter by application name, host name
• Key points
• XYZCompleted event is important to get duration,
  CPU and related
• XYZStarted event is important to capture timeout
  errors, hangs, failed events, statements that
  never complete
• Pre-filter helps reduce 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 because it can result in huge
  trace files
• As stated above, the same is true with many other
  events such as execution plan, locks, objects,
  scans

26
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
• 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
  the ordering depends on the specific problem
  scenario an example
• Event Class, Text, Start Time, Duration, SPID,
  CPU, Reads, Writes, Application Name

27
Analyzing Traces (2)

• After 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, Show Stats, Show Plan (to analyze
  identified problem queries)
• Increase filtering capability by moving trace
  output to 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 SPRecompile
• or StartTime between 2000-04-29 1645 and
  2000-04-29 1715)
• ORDER BY Duration (or CPU or TextData ) DESC

28
Replaying a Trace (1)

• Execute contents of a trace file against SQL
  Server
• From Profiler menu, select Replay
• Replay requirements (SQL Server Books Online)
• Event classes
• Data columns
• Trace template SQLProfilerTSQL_Replay.tdf
• Replay options
• Output file name
• Replay events in the order they were traced
  (enables debugging)
• Replay events using multiple threads (disables
  debugging)
• Display replay results

29
Replaying a Trace (2)

• Single-stepping traces
• A single event at a time
• To a breakpoint
• To a cursor (highlighted portion in the trace)
• 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
• Contains operations involving GUIDs
• Contain sp_getbindtoken and sp_bindsession -
  session binding system stored procedures
• From SQL Server 6.5 trace .log files contain
  server-side cursor statements (like sp_cursor)

30
Index Tuning Architecture
Input Workload
Index Tuning Wizard
SQL Script File

• Trace File
• Trace Table
• SQL Script

• Reports
• Index Usage (recommend)
• Index Usage (current)
• Table Analysis
• View Table Relations
• Query Index Relations (Recommend)
• Query Index Relations (Current)
• Query Cost
• Workload Analysis
• Tuning Summary

Index variations
Exec Query Return Only Execution Plans
Cost (I/O, CPU)
31
Index Tuning Wizard (ITW)

• Added support for Indexed Views and indexes on
  computed columns
• Projected volumes (via scaling factor)
• Index Analysis in Query Analyzer replaced with
  full Index Tuning Wizard
• Works on current script file or
• Highlighted selection within script
• Optionally limit the number of queries to tune
• Customizes recommendations via advanced options
  like disk space constraints
• Recommendations not only include creation of new
  indexes, but also dropping of ineffective indexes

32
Itw.exe

• -D database_name -S server_name
• -U user_name -P password -E
• -i workload_file -t workload_table_name
• -f tuning_feature_set (0, 1, 2)
• -K keep_existing_indexes (0, 1)
• -M recommendation_quality (0, 1, 2)
• -B storage_bound (in MB)
• -n number_of_queries (def 200)
• -C max_multicolumn_width (def 16)
• -T table_list_file
• -o script_file_name
• -m minimum_improvement (x)
• -F overwrite output file
• -v verbose

33
ITW Examples

• Minimum parameters, using local serveritw -D
  orders i orders_wkld.sql
• Remote serveritw S skasam2 -Usa P -D
  northwind -i sample1.sql
• Storage bound of 3 GBitw D orders i
  orders1.sql -B 3000 o d\script1.sql
• Only tune 10 queriesitw D orders i
  orders_wkld.sql -n 10
• Thorough analysis modeitw D orders i
  script1.sql M 2

34
Known Issues and Suggestions

• All statements within a stored procedure are
  captured as a SQLStatementStarting and/or
  Completed event.
• Replay facility does not replay Attention events
• Replay does not handle DTC transactions
• Show Plan events not populated if Binary Data
  column is not captured
• Replay of rollover files is not supported in the
  current version you can replay only the first
  trace file from the rolled over traces
• Profiler cant open trace files with more than
  1023 connections
• File rollover cannot take place when files with
  the same name already exist in the target
  directory
• ITW does not recommend indexes for cases
  involving triggers
• Consolidation of events from different servers to
  one trace file not possible using sp_trace_create
  (documentation bug )
• Search in SQL Profiler is case-sensitive

35
Tips and Tricks (1)

• You can trace SQL statements directly from Query
  Analyzer without calling Profiler.
• You can set an option to start tracing
  automatically upon making connection using
  Profiler options
• You can start Profiler using command line
  arguments
• When saving to a file you can enable rollover
  option so you can back up files written to the
  server without interrupting the trace
• When examining trace/file/table contents, you can
  set bookmarks and navigate between them using F2
  and CTRLF2 sequence
• You can see how many events are in a
  trace/file/table from the status bar below
• You can examine the total number of Profiler
  connections displayed in the main window status
  bar
• You can extract and save trace definitions from
  an existing trace file or table into a new
  template from the menu
• You can open and replay large trace files,
  exceeding 4 GB on Windows 2000 and Windows NT
  up to 4 GB on Windows 95 and Windows 98

36
Tips and Tricks (2)

• You can use connection level sync option when
  replaying trace files which turns Profiler into a
  real stress tool to load up your server and see
  the bottlenecks
• You can generate a T-SQL script for a trace to be
  run using Query Analyzer (on File menu, click
  Script Trace)
• If Profiler displays data on the screen only,
  click Save As later, which will persist data in
  either local file/table
• All string parameters to the system stored
  procedures for SQL Trace should be UNICODE
  cannot be ASCII
• Screen buffer size is limited to Temp directorys
  disk space and not restricted to available free
  memory
• Profiler takes about 1.5 times the original trace
  file size while tracing to a file/screen and also
  while opening a trace file in the directory
  pointed to by TMP env variable it actually
  creates two temp files
• In Filtering, Include has precedence over Exclude

37
Tips and Tricks (3)

• Tracing to a database table is by Profiler
  (client side)
• You can specify to stop tracing on error, but
  this is not possible for C2 traces
• Loading the trace file is asynchronous, unlike in
  SQL Server 7.0
• SQL Server 6.5 trace and 7.0 trace files can be
  used to REPLAY against SQL Server 2000 using
  Profiler
• While defining a trace for Replay, do not
  pre-filter on
• Dependent tables that have Primary Key-Foreign
  Key relationship
• When a trace file is saved to a table, you
  cannot
• Group By textdata column, because as it is of
  type Ntext
• In case of large trace files, after post
  filtering a trace, save the new, concise,
  scenario-specific trace to a new file

38
Tips and Tricks (4)

• For fast replay, clear the Replay option Display
  Replay Results
• For RPC events, the Completed events show values
  for OUTPUT values going back to the client in the
  DECLARE/SET syntax
• Audit Login Event class Integer Data gives
  Network Packet Size
• Text Data data column gives Network Library as a
  comment for the Audit Login and Existing
  Connection event classes
• Tracing stops when you exit Profiler GUI, use
  sp_trace_create for persistent tracing
• Multiple filters can be specified in SQL 2000
  Profiler properties unlike in SQL Server 7.0

39
Additional Resources

• Troubleshooting SQL Server Profiler section in
  SQL Server 8.0 Books Online
• Monitoring with SQL Server Profiler chapter in
  SQL Server 8.0 Books Online
• (Contents gtgt Administering SQL Server gtgt
  Monitoring Server Performance )
• Q214799 - INF SQL Profiler from Command Line
  Parameters to Temp File Usage

40
(No Transcript)