We always want to have an easy way to perform our task and also visualize the things.
If we talk about SQL Server database, sometimes we need to monitor and analyze all or some activity happening there. Whether it is to troubleshoot any database related issue or simply to monitor the performance.
In this blog, we will explore how these tasks can be accomplished using the SQL Profiler interface.
What is SQL Profiler?
SQL Profiler is a graphical user interface to SQL Trace to monitor database engine activities and analysis services. It provides an easy to use interface that allows you to select the events you want to monitor and where you want the output to be saved without having to know all the system stored procedures that are a part of SQL Trace.
Without this tool, you would have to go through the manual setting process for events and filter with individual stored procedure calls for every event you want to be configured in your trace.
SQL Profiler use cases-: SQL Profiler has many use cases, but its main purpose is to monitor the activity in your SQL Server instance.
The following activities/events can be monitored with SQL Profiler-
1) Monitoring the performance of queries/stored procedure and get useful suggestions to increase the query performance.
2) Finding and diagnosing slow-running queries.
3) Capturing the series of Transact-SQL statements that lead to a problem.
4) Monitoring the performance of SQL Server to tune workloads.
NOTE: SQL Profiler can also be used to perform auditing on your SQL Server instance. You have the ability to define login/logout events so you can see who is accessing your instance and what systems they are accessing it from. You can also capture all DDL events which would give you a log of all the changes that are made to your environment.
Limitations of availability of SQL Profiler-:
1) SQL Profiler is only available in the Enterprise, Business Intelligence and Standard editions of SQL Server.
2) It is an official announcement from Microsoft that SQL Server Profiler for Database Engine Trace Capture and Trace Replay feature may not be available in later versions of SQL Server. It is available in SQL Server 2016 but can be removed in the later versions.
NOTE: SQL Server Profiler for the Analysis Services workloads is NOT being deprecated, and will continue to be supported.
The terminology used for SQL Profiler-:
EVENT: An event is an action generated within an instance of SQL Server Database Engine such as-
1) Login connections, failures, and disconnections.
2) Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
3) The start or end of a stored procedure.
4) The start or end of statements within stored procedures.
5) The start or end of an SQL batch.
6) A lock acquired or released on a database object.
7) Security permission checks.
EVENT CLASS: An event class is a type of event that can be traced. The event class contains all of the data that can be reported by an event. Examples of event classes are the following:
1) SQL: BatchCompleted
2) Audit Login
3) Audit Logout
4) Lock: Acquired
5) Lock: Released
EVENT CATEGORY: An event category defines the way events are grouped within SQL Server Profiler. For example, all lock events classes are grouped within the Locks event category. However, event categories only exist within SQL Server Profiler. This term does not reflect the way Engine events are grouped.
DATA COLUMN: A data column is an attribute of event classes captured in the trace. Because the event class determines the type of data that can be collected, not all data columns are applicable to all event classes. For example, in a trace that captures the Lock: Acquired event class, the BinaryData data column contains the value of the locked page ID or row, but the Integer Data data column does not contain any value because it is not applicable to the event class being captured.
TEMPLATE: A template defines the default configuration for a trace. Specifically, it includes the event classes you want to monitor with SQL Server Profiler. For example, you can create a template that specifies the events, data columns, and filters to use. A template is not executed but rather is saved as a file with a .tdf extension. Once saved, the template controls the trace data that is captured when a trace based on the template is launched.
TRACE: A trace captures data based on selected event classes, data columns, and filters. For example, you can create a trace to monitor exception errors. To do this, you select the Exception event class and the Error, State, and Severity data columns. Data from these three columns need to be collected in order for the trace results to provide meaningful data. You can then run a trace, configured in such a manner, and collect data on any Exception events that occur in the server. Trace data can be saved or used immediately for analysis. Traces can be replayed at a later date, although certain events, such as Exception events, are never replayed. You can also save the trace as a template to build similar traces in the future.
SQL Server provides two ways to trace an instance of SQL Server: you can trace with SQL Server Profiler, or you can trace using system stored procedures.
FILTERS: When you create a trace or template, you can define criteria to filter the data collected by the event. To keep traces from becoming too large, you can filter them so that only a subset of the event data is collected. For example, you can limit the Microsoft Windows usernames in the trace to specific users, thereby reducing the output data.
If a filter is not set, all events of the selected event classes are returned in the trace output.