SQL Server Profiler

Introduction

In this article, we are describing SQL Server Profiler Terminology and Architecture. SQL Server Profiler is a powerful tool.

SQL Server Profiler

SQL Server Profiler 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 the SQL Server. The SQL Server Profiler GUI is actually an SMO-based application that interacts with SQL Trace.

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

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

For DBAs

For DBAs SQL Server Profiler

1. DBA Monitors all types of actions of the SQL Server Database Engine, Analysis Services, and 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 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.
• Filter- Filters are used to create selectiveness in data that are collected in the 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 in SQL Server Profiler

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

Event Class in SQL Server Profiler

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 in SQL Server Profiler

A group of events is called an event category. Examples of event categories are Stored Procedures and Locks. In fact, there can be multiple event categories that can be selected for a single trace.

Data Column in SQL Server Profiler

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 in SQL Server Profiler

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

Trace in SQL Server Profiler

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.

Conclusion

This article taught us about SQL Server Profiler Terminology and Architecture. For learning about the uses of SQL Server Profiler go through this: Using SQL Server Profiler.