SQL Server Profiler

SQL Server Profiler

In this article we are describing SQL Server Profiler Terminology and Architecture. SQL Server Profiler is a powerful tool. This tool is very important for DBAs and developers. It allows us to capture and explore events, such as the execution of a Stored Procedure, occurring within SQL Server. The SQL Server Profiler GUI is actually a SMO-based application that interacts with SQL Trace.

SQL Server Profiler consumes many resources and degrades your server that is already negatively impacted by performance.

SQL Server Profiler can help DBAs and Developers in various ways. We can understand in the following.

For DBAs

For DBAs SQL Server Profiler:

1. DBA Monitors all types of actions of the SQL Server Database Engine, Analysis Services, Integration Services and identify performance-related problems with front-end application, queries-SQL, transactions, SQL Server errors and warnings and so on.
2. Performs a query analysis of execution plans
3. Captures and replays traces of activity to replicate problems
4. Audits user activity
5. Analyzes trace results by grouping or aggregating them
6. Creates your own custom traces, and saves them, for use whenever you wish
7. Correlates Profiler trace events with the System Monitor performance counters to identify
8. Captures data for analysis in the Database Engine Tuning Wizard
9. Saves trace results to a file, a database table, or to XML for later analysis
10. Traces Profiler events programmatically, instead of using only the Profiler GUI

For developers

For developers SQL Server Profiler:

1. Shows how SQL Server works when interacting with a client
2. Shows T-SQL code and Stored Procedures
3. Performs stress testing
4. Performs quality assurance testing

We can understand SQL Server Architecture in the following figure.



SQL Server Events: An event is any T-SQL or performing any process in the SSMS that is related to a database, running Stored Procedures, and creating jobs.
SQL Trace: SQL Trace is a very simple tool. It captures SQL Server-related communication between a client and SQL Server.
Filte: Filters are used to create selectiveness in data that are collected in trace.
Server Management Objects (SMO): it is an object model that can be used to directly access SQL Server, including SQL Trace.

SQL Server Profiler Terminology

There is some basic Profiler terminology.

These terms are:

• Events
• Event Class
• Event Category
• Data columns
• Filters
• Trace

Event: An event is an action within an instance of SQL Server Database Engine. SQL Server Profiler allows you to capture over 170 different SQL Server-related events.



Event Class: An event class can be defined as a type of event that can be traced. Examples of event classes are SP: Starting and RPC: Completed.



Event Category: Group of events is called an event category. Examples of event category are Stored Procedure and Locks. In fact, there can be multiple event categories that can be selected for a single trace.



Data Column: A Data column is an attribute of an event class captured in the trace. A Data column contains a value of an event class.



Filter: You will often want to capture information about a certain event.



Trace: A trace captures data based on the selected event classes, data columns, and filters. A trace devoid of a filter can be very hefty. Columns that are indispensable for the analysis should be selected only in the trace. A trace can be saved in the database as well as in trace files.