Identify the events that SQL Server Profiler can track.
SQL Server Profiler Events
What kind of events can Microsfot SQL Server "Extended Events" track?
SQL Server Profiler was deprecated in SQL 2016, and has had no new features since then. Extended Events (XE) is the long-term replacement for Profiler.
SQL Server profiler allows you to track many different events through traces. To make maintenance of a trace easier, SQL Server Profiler groups the events into event categories, also called event classes.
This is the caption for Layout Figure Tag
12 Classes of events that SQL Server Profiler can monitor
There are 12 classes of events that SQL Server Profiler can monitor.
Class
Description
When to Track
Analysis
Cursors
The events that are produced by cursor operations. This class keeps track of when cursors where open, closed, prepared, unprepared.
If the system’s performance is slow and you are using a lot of cursors.
Look at cursors recompiles and number of cursors opening and closing.
Error and Warning
The events that are produced when a SQL Server error or warning occurs.
If you are having any performance problems, errors or warnings can be an issue.
The important thing is the number of errors being issued.
Locks
The events that monitor database object locking.
If you are having processes hanging or deadlocks.
The important things to look at here are the length of time that locks are held and deadlocks.
Database Object
The events that are produced when database objects are closed, created, deleted, dropped, or opened.
When you are having locking problems.
You should look at the length of time objects are opened to help isolate performance problems.
Scans
The events that occur when database tables and indexes are scanned.
Any scan is a potential performance problem that should be investigated.
The length of time it takes to perform a scan.
Sessions
The events that occur when clients connect to and disconnect from SQL Server.
If the application seems to behave slowly.
A lot of sessions connecting and disconnecting may indicate an application design problem.
SQL Operators
The events that occur when SQL data manipulation language (DML) statements are used.
If you are trying to use parallel queries, this is an important area to monitor to see if it is happening.
If you are trying to use SQL Server, parallel queries look at the event subclass.
Stored Procedures
The event related to the the execution of stored procedures.
If your application uses stored procedures.
A lot of stored procedures not found in cache indicates that you should have more procedure cache.
Transactions
The events that are created when transactions are written to the transaction log.
Whenever you are having performance problems.
Check the text field and the event subclass field.
TSQL
The events that track the start and stop time of Transact-SQL to SQL Server from the client.
Whenever you are having performance problems.
Check to see that the text field contains the TSQL .
User Configurable
A set of events that are user-configurable.
This is used to track anything that you coded it to.
By default, this returns no information unless your SQL is coded to raise this.
Miscellaneous
The events that do not fit into any of the other event categories.
Whenever you are having performance problems.
In this class, you are normally concerned about what the query execution plans are and if SQL Server is acquiring and releasing memory.
Below is an example of the Trace screen.
This is the caption for Layout Figure Tag
Trace Events - Exercise
Click the Exercise link to test your knowledge of the Event classes that can be traced. Trace Events - Exercise
The next lesson covers the information that can be traced.
