Query analysis using SQL Profiler. SQL Profiler solves problems

What do you learn from this article?

Purpose Tracing Tool SQL Profiler
How to track the text of the request to the DBMS in which 1C query is broadcast
Tracing filter settings
How to fulfill personal configuration SQL Profiler

Often, the situation arises when a request in 1C for some reason works slowly, but the query text analysis does not tell us any problems.

In this case, you have to study this problem at a lower level. To do this, we need to watch the SQL query and the request plan. To do this, you can use SQL PROFILER.

SQL Profiler - purpose

SQL Profiler is a program that includes MS SQL Server, which is designed to view all the events that occur in the SQL server. In other words, it is needed to record trace.

In what cases this tool can be useful to 1C programmer? First of all, you can get the text of the SQL query and see its plan. It can also be done in the technological journal (TZH), but the query plan in the TH is not so convenient and requires some skills and skills. In addition, the profiler you can see not only a text, but also a graphical plan for performing a query, which is more convenient.

Also, the profiler allows you to find out:

requests are longer than a certain time
requests for a specific table
expectations on locks
taimauts
interlocking, etc.

Query Analysis Using SQL Profiler

Often, Profiler applies specifically to analyze requests. And at the same time you need to analyze not all executable requests, and how a specific query in 1C language is broadcast in SQL, and pay attention to its execution plan.

In particular, it is necessary to understand why the request is performed slowly. Or when building a large and complex request, you must make sure that the SQL query does not contain connections with a subquery.

To track the query in tracing, perform the following steps:

1. Run SQL Profiler: Start - All Programs - Microsoft SQL Server 2008 R2 - Performance Means - SQLProfiler.

2. Create a new trace: File - Create Trace (Ctrl + N).

3. Indicate the DBMS server on which our database is located and click Connect:

Nothing prevents us from carrying out the DBMS server tracing, located on any other computer.

4. In the window that appears Trace properties Switch to bookmark Selection of events:

Since we need queries and request plans, you need to enable the appropriate events. To display a full list of properties and events turn on flags Show all events and Show all columns. Now you need to select only the events shown in the figure below, the rest - you want to disable:

Description of these events:

ShowplanStatisticsProfile- text execution plan
Showplanxmlstatisticsprofile- graphic execution plan
RPC: Completed- query text if it is executed as a procedure (if 1c request is executed with parameters)
SQL: BatchCompleted- query text if it is executed as a regular query (if 1c request is satisfied without parameters)

6. At this stage, you need to configure the filter for selected events. If the filter is not set, then we will see requests for all databases located on this DBMS server. By the column filters button, install the database filter:

Now we see in trace only requests to the database "Testbase_8_2".

You can also put a filter and other fields, most interesting of them:

Duration
TextData (usually this is the text of the query)
ROWCOUNTS (number of rows returned by the request)

Suppose we need to "catch" all requests to the "_inforg4312" table with a duration of more than 3 seconds in the database "TestBase_8_2". For this you need:

a) Install the database filter (see above)
b) Install the duration filter (installed in milliseconds):

c) Install the filter by the text text:

To set the filter by the text text, use the mask. If you need to track requests that contact multiple tables, several items are created in the section "Looks like". Called filter conditions work together.

7. Now run the trace using the button Run in the window Trace properties And we see the events falling under the installed filters, the display of which was configured.

The command panel buttons serve to control trace:

Purpose of buttons:

Eraser - Clears the trace window
Start - Runs trace
Pause - puts a trace by pause, when you press the start, tracing is renewed
Stop - stops tracing

8. Trace window consists of two parts. At the top there are events and their properties, in the bottom - information depending on the type of events. For our example, either the text of the query will be displayed here, or its plan.

9. We will start a request in the 1C query console and see how it affects the profiler:

According to the behavior of tracing, it can be seen that the requests in the end it turned out several, and only one of them is interesting. The remaining requests are service.

10. Event properties make it possible to assess:

how many seconds query satisfied (duration)
how many logical readings (reads)
how many rows a request returned as a result (rowcounts), etc.

In our case, the request was performed 2 milliseconds, made 4 logical readings and returned 1 string.

11. If you look at one event above, you can see a request plan in graphical form:

From the plan it can be seen that the search is carried out on the price index, this plan cannot be called perfect, as the index is not covers, the field fields and the name are obtained using Keylookup, which takes 50% of the time.

Using the context menu, the resulting request graphic plan can be saved to a separate file with the * .sqlplan extension and open it in a profiler on another computer or using the SQL Sentry Plan Explorer program, which is more advanced.

12. If you rise even higher, we will see the same query plan, but already in text form. It is this plan that is displayed in the TJ, the PC and other means of controlling the performance of 1C.

In the format of the profile itself, that is, with the extension * .trc
In XML format
Make a template template (see the next item)
Hard trace in the form of a database table. This is a very convenient way when, for example, you need to find the slowest query in tracing or filter queries by any parameter.

Then select the database on the specified server, specify the name of the table where the trace will be saved. You can use an existing table, or give it a new name, and then this table will be created automatically.

Now it is possible to build queries of any complexity to our table: for example, search for the most long-run requests.

It should also be remembered that the Duration is stored in a table in the million dollars of a second, and when the result is derived, it is necessary to translate a value to milliseconds. Also in the table there is a RowNumber column showing the number of this string in trace.

14. When using a profiler to analyze requests, the constant setting of the desired events and filters will constantly take away from you.

In this case, trace templates will help us, where we configure the filters we need and the order of columns, and then simply use an existing template when creating a new trace.

To create a template, use the menu File - Templates - New Template:

On the first tab, specify the type of server, the name of the template and, if necessary, set the flag to use this default template.

On the second tab, we make the choice of the necessary events and setting the filters (as shown above).

When creating a new trace, we can specify the desired template, and then on the second tab all filters and events will be filled automatically by the created template.

Andrey Burmistrov

When developing application modules of the Lexema.ru system, the need for an analysis of database requests occurs periodically when operating on-screen forms, queries, reports, stored procedures and other objects to diagnose problems. To solve such tasks, SQL query profiling tools are intended. They allow:

track events of different types on the database server (execution of requests, stored procedures, etc.)
filter events on various criteria (bd name, user login, etc.)
record the sequence of actions in the form of events trace. Subsequently, tracing can be analyzed, as well as stored in a file or in database.
analyze performance (speed of execution) requests for searching and eliminating "bottlenecks" system
etc.

This article discusses two tools:

Lexema SQL Profiler, built into the application model
MS SQL Server Profiler, which is part of MS SQL Server

Lexema SQL Profiler

This tool is designed to analyze requests to the database server, initiated by the modulor - program to develop application configurations. With it, you can explore the structure of the applied logic database and its objects.

To use Lexema SQL Profiler, run the application model. Click on the button with the image of the barrel in the upper left corner of the window:

To start recording the trace of SQL modeller requests to the database server, click the Run button on the toolbar.

Perform the actions in the modulator for which you want to explore the database requests. For example, after opening a list of models, tracing from several requests will be created:

The table from above contains a list of events (SQL queries), the field at the bottom - the contents (SQL code)

Table fields:

EventClass.
TextData.
Duration.
StartTime
Endtime.
ApplicationName.
Reads.
Writes.
Transaction.

For example, from the trace of events when opening a list of models, you can draw the following conclusions: data from three tables (L8_MODEL, L8_MODELPROPERTY and L8_NAMESPACE) are requested; The longest query is made to the L8_MODELPROPERTY table (242 ms).

MS SQL Server Profiler

MS SQL Server Profiler is a tool included in the MS SQL Server package that allows you to intercept the database server events. Events can be saved in the trace file or in the database for further analysis or use in order to repetition a specific series of steps to play a problem for its diagnostics. Schedule scenarios using SQL Server Profiler:

sQL Server Database Engine Instance Performance Control
debugging TRANSACT-SQL instructions and stored procedures
performance analysis by identifying slowly working requests
performing stress testing and quality control by playing traces
playing a trace of one or more users
check TRANSACT-SQL instructions and stored procedures at the project development stage in step-by-step mode to guarantee the correct execution of code
troubleshooting problems in SQL Server using event interception in the production system (product version) and play them in debugging (test version). This is a very useful opportunity because it allows during check or debugging to continue using the production system.
audit and tracking of actions occurring in the SQL Server instance. This feature allows the security administrator to view any audit events, in particular successful and unsuccessful attempts to log in to the system and access authorities and objects
saving trace results in XML format, which provides a standardized hierarchical storage structure of trace results. This allows you to make changes to existing traces or create them manually for subsequent playback.
statistical analysis of trace results, allowing to produce and analyze similar classes of events. The results contain meters obtained on the basis of grouping one column
providing the possibility of creating tracing users who are not administrators
setting trace templates, which can then be used for subsequent traces

Run and connect to server

You can run MS SQL Server Profiler from the Windows OS menu ("Start" menu) or from the MS SQL Server Management Studio program menu (Service Point - "SQL Server Applicer"). After launch, you must log in to the server - enter the server address, the name of the account and password:

Setting Tracing Parameters

Then before the start of the trace, it is necessary to set its properties:

Tracing name - it is advisable to set in the event that it is planned to be saved
Use the template - defines the default trace configuration. Namely, it includes the classes of events that need to be monitored to SQL Server Profiler. For example, you can create a template that specifies the events used, the data columns and filters. The assemblies are not executed, but are saved in files with the extension TDF. After saving the template controls the data capture if trace based on this template starts.
Save to the file to re-open and analyze
Save to the table - in this case, the trace will be saved in the database and it will be possible to analyze SQL tools.
Turn on the trace stop time - it is necessary in case of long-term observations

Selection of types of events and their attributes

An important step of setting the trace is the choice of events (you need to go to the appropriate tab). The "Select Event" tab contains a grid - a table that contains each of the event classes available for tracing. On each class of events in the table accounts for one line. Event classes can vary slightly depending on the type and version of the server to which they are connected. Event classes are identified in the grid event column and are grouped by event categories. The remaining columns lists the data columns that can be returned for each class of events. To enable events in tracing, check the box in the event column.

By default, this list contains only some categories and types of events in accordance with the selected template (see above). Not all columns are also displayed. For more detailed settings, it is recommended to put the checkboxes "show all events" and "show all columns".

If the tracing is performed to track the requests produced by the Lexema.ru application, it will be enough to mention 3 types of events in 2 groups:

Stored Procedures (Stored Procedures)
- RPC: Completed - occurs when the remote call is completed (RPC)
- SP: Completed - occurs when the stored procedure is completed
TSQL - Tracking the execution of TRANSACTSQL instructions transmitted by customers to the database server
- SQL: BatchCompleted - occurs when executing the execution of the TRANSACTSQL instruction

Note: The checkbox in the Events column may be in three states:

no checkbox - an event is not tracked
a black tick is installed - all data columns are selected - all possible data will be collected for the selected event.
a gray tick is installed - only some data columns are selected - only some data will be collected for the selected event in accordance with the stamps in columns.

By default, not all columns are selected for some events (it is worth a gray tick). To select all columns, you must remove the check mark and install it again. In this case, ticks will be installed for all visible data columns.

Below are other useful categories and types of events:

Security Audit.

Setting the filtering parameters

Filters limit the accumulation of events in trace. If the filter is not installed, then all the events of the selected event classes are returned to the tracing output. Setting the trace filter is optional, but the filter minimizes resource costs during tracing. Filters for trace definitions are added on the "Event Selection" tab in the "Trace Properties" dialog box or "Trace Template Properties".

When tracking events taking place when using the lexema.ru web interface with a specific user, it is advisable to install the "ApplicationName" filter looks like<логин_пользователя>+ & 1, for example, "Petrovan & 1", where Petrovan - user login:

With this setting of the filter, only events generated by the specified user will fall into trace.

Tracking events

Suppose that after settings described above and launching tracing, user with login " aIRAT " enters the system and opens the registry of the categories of income and expenses of the Home Accounting Module, and then opens one of the documents (as an example):

As a result, a list of events will be displayed in the SQL Server Profiler tracing:

Analyzing a list of trace events can be seen that one of the longest requests was the following:

exec Sp_executeSQL N. "Select as, as, as, as, as, as, as, as, as, as, as, as, as, as, as, as From ", n "@PrimaryKeyBoundary Bigint, @ topcount bigint", @ PrimaryKeyBoundary \u003d NULL, @ TOPCOUNT \u003d NULL

Judging by the object name (VTransActionCategory), this is a request to select a list of transaction categories. Type of this event - RPC: Completed (completion of the execution of the remote procedure).

Also in the list you can see SQL type events: BatchCompleted:

This is the result of the query (QuerySource) Lexema.ru.

To search in the text of requests tracked in tracing, you must click the "Find a string" button (with the binoculars icon) on the toolbar or click the Ctrl + F key combination:

Then you need to enter the desired text and select a search column (the text of the query is contained in the TEXTDATA column). After clicking on the "Next" button, the cursor will be positioned on the event string containing the desired text.

For testing and debugging data, these requests can be re-performed manually. To do this, you need to copy their text, open the SQL Server Management Studio, connect to the corresponding server, select the database, create a query, insert it text and execute.

The SQL Server Profiler software product is a graphic shell intended for creating traces and analyzing trace results. Events are saved in the trace file, which can then be analyzed or used to play certain steps sequences to identify the problems that have arisen.

In order to track the steps at the moment, you need to start MS SQL Profiler, create a new track and configure the analysis of the indicators:

On the General tab, you must specify the name of the trace. Specify where the data of the removed route will be saved - to the file and / or in the database table.

Of great interest is the "Selection of Events" tab:

This page indicates the events that must be monitored. In this example, specify the data you need to track query plans.

Get 267 video tutorials for 1C for free:

By default, tracing passes through all specified events in all databases. In order to apply selected to the data obtained, you must click the "Column Filters ..." button:

For example, set the selection by the identifier of the information base (you can learn the base ID using the SELECT DB_ID query (N'imäabase ')).

Running trace in Profiler for 1C

After all the settings are made, it remains to run tracking, for this you need to click "Run" (RUN). From now on, all the actions specified in the filter will be traced from this point.

For example, I launch the track for the time of the document "Receipt of goods and services" in order to track the most labor costs.

After tracing is obtained, it is necessary to analyze it.

Data analysis from Profiler

To analyze the trace, you can save either to a file or in the table. We will save the database table:

One of my favorite tools is the SQL Server Profiler, which is often called simply profiler. This utility displays data on any number of Detailed SQL Server events. These server events can be viewed in the Trace Properties window (Fig. 49.3), as well as write to a file or table for subsequent analysis. To register all events or their selected subsets, you can install filters.

PUC. 49.2. In this example, the meter protocol will register information

about SQL Server Performance in Catalog C: \\ Perf Logs

The SQL Server Profiler utility can be run from the Tools menu of the Management Studio program or directly from the SQL Server 2005 folder of the Start menu. To view actions, you need to either define a new trace, or use the existing file.

With the output of the SP1 SQL Server Profiler update package, it has received the opportunity! Tracing large data arrays on large computers.

In addition, earlier, when monitoring the analysis service, the time was displayed in universal synchronized time units (UTC). Now uses local system time. Previously, successfully reproduced events were calculated incorrectly, as a result, the user received incorrect statistics. This problem was eliminated.

Defining a new trace

When a new trace is created (either using the File ^ New Trace menu command, or using the New Trace button toolbar), a new connection to SQL Server is also created and the Trace Properties dialog box opens (Fig. 49.4). In the General tab of this window, tracing (in particular, the name, location of the file, etc.) is configured, and in the Events Selection tab, the recordable events, data and filters are defined. If the tracing is running, then these parameters can be viewed, but not to change. The trace configuration can be saved as a template to facilitate the creation of new traces in the future.

Fig. 49.4. The Events Selection tab of the Trace Properties windows allows you to select Events Tracking Profiler Utility

Tracing can be viewed in real time, but these data can simultaneously be recorded in a file or SQL Server table. This is useful for subsequent intelligent analysis, comparisons with data monitor counters or for imports in the Database Engine Tuning Advisor utility.

When the readings are written to a file, to increase productivity, they are combined into a chain of 128 KB; Similarly, when recording to the table, data is grouped by several lines.

To save the data obtained by Profiler program, for later analysis, use a high-performance file method, as well as a server tracing (we will talk about it later). If you want to analyze data using T-SQL instructions, use exactly the same approach, but after the trace session is completed, open the received file in the Profiler utility and select the File ^ Save As ^ Table command in the menu.

Selection of events

The Events Selection tab determines the composition of the actions performed by the database server to be recorded by the Profiler utility. Like a performance monitor, Profiler can track a variety of SQL Server key events. To simplify the selection setting, you can use the default templates.

I SQL Batch Completed Event is based on T-SQL packages

SVS Overall (separated by terminators of packages), and not individual instructions.

| * Based on this, Profiler registers the data of only one event, independent

SIMO from the length of the package. To register individual instructions

DML Use the SQL Statement Complete event.

Not all events can be used to play trace. For example, the SQL Batch Start event can be played, and the SQL Batch Complete event is not.

Depending on the events for tracing, different data becomes available. Despite the fact that the SPID data column seems optional, this impression is deceptive - it is obligatory.

Filtering events

Profiler Program is able to collect for you such a number of information that can easily fill the disk device in the blink. Fortunately, the program offered by the program (Fig. 49.5) will help you limit this array only by the data you are interested in.

Fig. 49.6. The SQL Server Profiler utility is able to integrate performance monitor data and synchronize them with tracked events.

Using SQL Trace

The SQL Profiler program is typically used interactively, and it is quite enough for non-permanent data collection. However, long traces are able to easily accumulate hundreds of thousands of records, which can cause quite specific problems on the workstation that performs tracing. The solution is reduced to creating trace log directly on the server. Such tracing will cause a small additional load on the server; At the same time, the files will be recorded by blocks of 128 KB.

In industrial systems engaged by tracing on the server side, record data to the file on the server is the best way to collect information.

about performance when minimizing additional load on the server.

Tracing running on the server can be defined and implemented using a set of system stored procedures. You can write software code yourself or using the SQL Server Profiler program.

When the tracing is configured and tested to SQL Server Profiler, select the File ^ Export ^ Trace Definition ^ for SQL Server 2005 in the menu to generate the T-SQL script capable of tracing on the server side.

I To find out which traces are running on the server, perform a request to

SVS Namic Presentation of Systraces Management. When you look at I * the results of this request, you will see an additional trace. The first * Number always has the so-called default trace that collects data for SQL Server logs, it is impossible to stop it.

To stop server tracing, use the SP_TRACE_SETSTATUS system stored procedure. The first argument (TRACEID) is a trace identifier, and the second determines the nature of the action. The zero value of the parameter of the action leads to a trace stop, one - to its launch, and a two-to-closing and removal. The next code stops tracing with number 2.

In this lecture, we will continue to study the stored procedures that began in the "Creating stored procedures and management of these procedures." You will learn how to analyze stored procedures and other T-SQL statements using Microsoft SQL Server Query Analyzer Query Analyzer and SQL Server Profiler Profile. From this analysis you can determine how effective T-SQL operators are. An effective SQL Server request uses a suitable sequence of operations and suitable indices to reduce the number of row processed and minimizing the number of I / O operations.

Using Query Analyzer, you can see the execution plan selected for the T-SQL operator optimizer of requests SQL Server. Query Optimizer - This is an internal module that is looking for the best execution plan for each T-SQL operator. Query Optimizer Analyzes each T-SQL statement, looks through a number of possible execution plans and evaluates the "cost" of each plan from the point of view of the required resources and processing time. The plan is selected with the smallest cost. The cost of each plan is determined based on the existing statistics, which is collected by the system and may be outdated. As you can know more about your database and your data than query OptimizerYou may be able to create a plan that will be better than the query optimizer. Using the information that Query Analyzer displays, you can determine whether the query optimizer plan for a specific operator will be effective, and if not, you can try to optimize this operator, modifying it or using the SQL prompt. In this lecture, you will learn how to optimize T-SQL statements, which will be an addition to the study of the use of Query Analyzer.

Using Profiler, you can analyze operations inside your SQL Server system to determine which SQL statements and stored procedures use unnecessary system resources. Possessing this information, you can focus your tuning efforts primarily on these operators and stored procedures. In addition to the description of how to use Profiler, this lecture also shows how to use the information obtained using Profiler.

Using SQL Query Anysenzer

The Query Analyzer utility comes with Microsoft SQL Server 2000 in return