SQL Profiler is a user interface tool for the SQL Trace utility that collects events as they occur in an instance of SQL Server. This makes it possible to track everything that happens inside the instance. The software provides an easy-to-use interface that allows you to select tracking events and the place where you want to save the output.
The positive thing is that users working with the program do not need to know all the system processes that are part of SQL Trace. Without this tool, they would have to go through the tedious process of manually configuring each step and filtering with individual calls to stored procedures.
How SQL Server Profiler Works
The tool works by providing DBAs and developers with an overview of the system. Users create traces to collect data and track errors. The profiler is then used to store, retrieve and graphically view trace results in order to troubleshoot and repair. This function occurs on the client side, that is, it uses resources on the same machine that it is monitoring.
To start the software, go to Start> Programs> Microsoft SQL Server> Performance Tools> SQL Profiler.
List of software benefits for developers and database administrators:
- Clarity is a demonstration of how an instance works when it interacts with a client.
- Troubleshooter - Helps you focus on critical points, allowing you to capture and play key events. This feature also helps in stress testing and identifying slow-running queries.
- Allows non-administrator users to safely create footprints, can satisfy the needs of administrators, database developers, business intelligence specialists, IT professionals, and even accountants.
- Compares activity with baselines. This allows users to save trace data and compare them with new data in order to identify problem areas.
- Captures traces for Transact-SQL, SSIS, and analytics.
Database Analysis Tools
The database administrator needs tools to analyze the actions in the SQL Server database to eliminate possible problems of the application, database, or simply to monitor the general state of the system. Access to the SQL Profiler interface is obtained from Server Management Studio.
It provides an easy-to-use interface that allows the user to select events and track where to save output, without having to know all the system stored procedures that are part of SQL Trace. Without this tool, you would have to go through the tedious process of manually configuring each step and filtering with separate stored procedure calls for each event that you want to configure in your trace.
SQL Profiler has many use cases, but its main goal is to track activity in the Server instance. If there is a problem with the latter, or if you just need to check the general status and performance of the database, the program makes it possible to track everything from ordinary user activity and transactions to locks and systems errors. And you can also perform proactive maintenance on an instance to identify any inefficient requests, settings, and use it to capture a large period of time playing the test system.
Finally, MS SQL Profiler is used to perform the audit. The user has the ability to define login and logout data to see who accesses the instance and from which systems they access it. You can record all DDL events that will contain a log of all changes made to the environment.
Available versions of the program
MS SQL Profiler is available only in editions of SQL Server Enterprise, Business Intelligence, and Standard, however, if Enterprise or Standard is installed in the environment, it can be used to profile any other editions, for example, Express SQL Profiler or Web. Enhanced events were released in Server 2008, which is considered a replacement for Profiler or Trace, and is still widely used and continues to be of value to the many needs of database administrators.
Both when installing MS SQL server Profiler, and when creating a database schema, there are recommendations that should be followed. There are also some tips to consider when setting up tracing using Profiler. These recommendations will help the user control the Server instance without incurring excessive costs or increasing the impact on the performance issue.
Quick Access Templates
There are several options for starting Profiler. On different versions of Windows, they open it through the Start menu or from the Metro Interface in later releases. More advanced users prefer to simply run it from SSMS, which is always open. The user can find SQL in the tool menu.
After starting the application, a dialog box opens, allowing you to connect to the server that you want to profile. From the “Recommendations” section, they connect to the remote server. After the connection data is filled in, click “Connect”.
Now, when the user is connected to the server, he will see the "Trace Properties" window, in which there are two tabs. The first - "General" - allows you to name a trace.
Before using SQL Profiler, you need to select a template for data collection. Templates are basically a stored set of events and data columns that need to be tracked. They allow you to quickly start a trace without having to manually select all events and data columns each time a new session is started. Microsoft provides several options; the default is Standard.
Next, they determine where to save the trace data if the user does not want to just view them in the graphical interface. It is possible to save data to a file and specify rollover parameters (hidden in the template settings), or in a table specifying the number of rows in it. Determine the stop time for the trace.
Select events to track
On the second tab in this "Selection" window, you configure which events you want to track and which columns you want to include for a particular event. On the tab add filters and change the order of data organization in the lower right corner of the interface. Since the Standard template is selected on the previous tab, the events and data columns are already filled. At this point, they edit or simply click “Run” to start tracing as is. After that, a screen will appear in the lower right corner that displays events as they are captured.
In order to pause or stop tracing, you can use the buttons on the toolbar. All events are saved before the restart, otherwise they will be lost. If the user pauses the trace, he will not lose the data that he has collected, but will skip all the events that occurred during the pause.
An event is an action that occurs in a user instance of Oracle Profiler SQL Database. You can configure more than 150 different events in a trace.
To add events to the session, go to the "Select events" tab. In the lower right corner of the screen there is a checkbox “Show all events”. If you install it, it will list all the events in the window above. The Show All Columns check box is also checked so that when you select an event, all data columns are selected.
Defining Data Columns
A data column is a piece of data that describes some part of the event that occurred in the database. Things like login, start time, success or failure are different types of data that can be collected for the SQL trace configured in the session.
There are more than 60 columns of data that can be added to the trace, but depending on which event is selected, this column may or may not be available. To view a description of the available columns, you must first select a custom event.
To add columns to a session, open the Select tab. Set the Show All checkbox. In the window above, flags will be displayed for each available for any open events. Alternatively, select the individual columns of each event, and either select the column itself or select the event to be checked.
Filters in a trace session
Profiler allows the user to filter the data captured in the SQL session. r to limit the amount captured. The filter applies to any column that is part of the selected event. Even if the column is not saved as part of the SQL trace session, filters based on it are still applied. To create a filter, select the "Column Filters" button on the "Selection" tab.
Once selected, the "Edit Filter" dialog box opens. Next, the LoginName data column is selected, which contains text comparison sentences, and filtered using LoginName, for example, with the name "Server". When using the LIKE operator, it should be borne in mind that if the user does not include the wildcard character "%", he will be considered as an equality operator (=). For example, if you need to find some operator executed by the login name that starts with “app”, “app%” will be used as a filter. Depending on the selected column, various comparison operators will appear.
Setup Recommendations
Profiler is an excellent tool that allows you to see what is happening inside the Server and helps users find out what are the worst queries, how often the query is executed.
To implement the best possible indexes, 4 steps are taken:
- Gather current workload information.
- Determine the suitability of indexes for workloads using Profiler.
- Updating indices using Tuning Advisor.
- The profiler automates the collection of workload data, and the configuration consultant takes this data and creates indexes.
For tracking in a specific database, use the column filtering function. They start the trace through the configuration template and save the trace data in its file.
Process algorithm:
- Click on "Tools".
- Open the kernel configuration assistant.
- Provide a tracing file.
- Select the database in which to trace.
- In the "Settings" go to the "Advanced Options" and determine the maximum space for recommendations.
- Begin the analysis.
The results show how much improvement can be achieved if the proposals are implemented, and what needs to be implemented to increase the speed of the database.
In order to intercept deadlocks using Profiler, do the following:
- They fix a deadlock, first connect to the Server database.
- Open Profiler in Server Management Studio.
- Click on "Tools".
- Click on Profiler.
Connect to the server and analyze the situation
Before you start using SQL Server Profiler, they are connected to the server on which you want to perform profiling. In the "Tracing Properties" window, on the "General" tab, select an empty template. On the tab "Select events" select the schedule "dead end" in the "Block list".
Perform the following queries:
- execute the request in a blue rectangle;
- Run the request in a green rectangle;
- execute a grayed out request;
- execute the query highlighted in blue.
Note that process identifiers are displayed on the dashboard in SQL. A "deadlock" will be generated during step 4: for further analysis, this can be saved in the menu "File" -> "Export" -> "Extract SQL Server events" -> "Extract deadlock events".
Deadlock graph analysis:
- An oval with a blue X represents a transaction or process that has been selected as the victim of SQL Server deadlock.
- Ovals represent processes, and a cross represents a transaction or process that has completed successfully.
- The deadlock priority is set to 0 by default.
The user has a transaction log. If a transaction has made many updates, the log size will be larger. Therefore, to perform the one that performed the large number of updates, it will take a lot of costs.
The HoBtID (heap or binary tree identifier) associated with the resource node is used to find the database object involved in the deadlock and is represented using the following query:
- SELECT object_name ([object_id]) from sys.partitions;
- WHERE hobt_id = xxxxxx.
The arrows represent the types of locks that exist on each resource node. The X and S symbols on the arrows represent exclusive and general locks.
Using a profile in 1C
The SQL Server Profiler with a graphical shell is used in the 1C accounting program to create traces and analyze the resulting trace with saving events in the trace files. Based on them, an analysis of the situation of the problems formed is performed.
To create tracks, Profiler launches a program, forms a route and sets up analysis of indicators. Before configuring for 1C SQL Profiler, perform the following steps:
- Indicate the name of the General trace where the deleted trace data will be stored.
- The Event Selection tab shows the events to track.
- Begin tracing in the profiler for 1C after making settings through the "Run" button.
- For example, they start the track “Receiving materials” to track the operations that take the most time.
- After receiving the data, analyze them.
- Profile analysis is stored in a file or in a table, for example, under the name "materials".
- After saving, they work with it as with an ordinary SQL table.
Alternative Profiler Tools
Despite the popularity of SQL Profiler, this is not the only way to track a server. The following alternatives perform a similar function:
- Prefix. One of the great features that Prefix has is to view SQL queries directly from the application code to get not only accurate, but also contextual information. It is free, incredibly reliable, and provides detailed information about each web request.
- ExpressProfiler is an alternative that uses a simple and fast graphical user interface.
- Neor Profile SQL provides similar capabilities for MySQL users.
Server-side tracing is considered the best because client-side tracing can deplete enough local resources to distort results. This type of tracing does not use resources on the client side, requires less costs and provides more accurate monitoring and reporting.
Thus, it is obvious that the profiler allows developers and database administrators (DBAs) to create and process traces, as well as reproduce and analyze their results.