DockPanel XE Profiler
Last updated 11 November 2020.
DockPanel XE Profiler - Performance troubleshooting and profiling for Dynamics NAV | BC and SQL Server
The V8 XE Profiler is a feature of V8 Search XE that displays a live display window with advanced events. This overview describes the reasons for using this profiler, its main features, and how to get started viewing advanced events.
Extended Events is a lightweight performance monitoring system that allows users to collect data needed to monitor and troubleshoot problems in SQL Server, especially in conjunction with dynamics NAV / Business Central.
This feature provides quick access to a live streaming view of diagnostic events in SQL Server generated by Dynamics NAV/BC. This view can be customized through various templates.
This live data window displays all logged T-SQL statements including the Dynamics NAV comments that provide the executed C/AL code. Displayed is each selected event, according to the default session definition (for example deadlocks or long durations).
Only a system administrator/local host administrator is able to perform all checks or functions.
Please note that starting the XE Profiler in a very short time, a lot of data is collected. The datasets started by the "Full SQL Tracing" function of the Dynamics NAV servers via the V8 service can record hundreds of thousands of records in a short time. Only use the V8 XE Profiler for short analyses and do not leave the profiler running unattended.
This code and information are provided "AS IS" without warranty of any kind, either expressed or implied. Furthermore, the author or Olav Treffurt IT Consulting shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.
V8 XE Profiler basics
Contains the following informational sections:The first step in creating an advanced event to monitor database blocking is to check that all necessary functions are enabled for the V8 XE Profiler.
all V8 services are started (green) that are displayed in the tool window "V8 Service" (2).
Start it when it is not running.
Click Stop in the SQL Server Blocking menu. After a short while all V8 services displayed should be "red".
Then click on "Start" in the "SQL Server Blocking" menu (7).
After a short while all displayed V8 services should be "green". then the V8 services in your network are working correctly.
If one or more services are still red, check the configuration of the V8 services on the respective computer. If you cannot solve the problem yourself, please contact our support.
in the SQL Server Extended Events Treeview (3), locate the menu item:
The services are started when you see the icon . If the icon is crossed out, the services are not started.
To start or stop an XEvent session, right-click on the session and select Start Event Session or Stop Event Session.
When you end an V8 XE Profiler session, stop the V8 services and the event sessions "V8_FullSQLNAV_Trace" and "V8_User_NAV_Trace" to avoid leaving the system unnecessarily. Right click on the session and select "Stop Event Session".
Default: Not enabled
Use V8 XE Traces for SQL Server monitoring with Dynamics NAV/BC
To start a new V8 XEvent session, you have to perform several steps:
Default: Not enabled
The Activity Monitor displays information about SQL Server processes and how these processes affect the current instance of SQL Server. The activity monitor is a tabbed document window that can be shown or hidden and is part of the XE Profiler document window.
Click the "SPID" button on the SQL Server Blocking menu. By default, the window is hidden. You have the option to anchor the window.
The activity monitor starts by pressing the button. Under the tab name "SQL Server Blocking Processes" a thin light green progress bar (2) is visible in the activated state, which moves. Behind the name "SQL Server Live Blocking" there are also two characters "..." and "->" (3) in an animation.
To change the column order, click and drag the column header to a different location on the ribbon.
To sort a column, click on the column name.
Events are usually displayed in about 2 seconds (1). To change the latency, you can change the Maximum Distribution Latency value on the settings page.
You have an option to determine when the column "WAITTIME" is highlighted and when the set threshold (2) is exceeded. This is very useful to see deadlocks or table locks directly, especially when the XE profiler is running in parallel.
A blocking is automatically displayed in red in the "BLOCKED" column. It shows which SPID is responsible for the block - the XE profiler who was the Windows user!
If you press the button "SPID" again, the activity of the monitor is stopped. The green stripe is no longer visible and the animation is no longer active.
Default: Not enabled
Terminating SQL Server blocking processes
To end a blocking process, click on the button"Kill SPID" . A pop-up window will open, enter the SPID whose process you wish to terminate. You must confirm that you want to end the process. Once this is done, the process will be terminated and all uncompleted transactions will begin the rollback process.
Default: Not enabled
Extended Event session - delete the target files
Since a lot of event data is generated during live tracing, profiling should always have as much free memory as possible.
We recommend that you delete the *.XEL files of the XEvents after you have stopped profiling and imported all data.
The XEvents have an overflow by default, but will continue to add data until the backup space of the configured files is full. For the sessions "V8_FullSQLNAV_Trace" click the button "Clear XFull" and for the sessions "V8_FullSQLNAV_Trace" click the button "Clear XUser" to delete the data.
Default: Not enabled
Configuring Extended Events to Find Locking Issues in SQL Server
You can configure XEvents to point the session to a specific event. There are many event options in the library, and I would recommend that you familiarize yourself with them, as they are very powerful monitoring tools, and the more you know about them, the more useful they are.
The choice of extended events is more obvious because of the flexibility it offers. For example, you can bind each event to any target and specify any action with each event. You can also use predicates (filter criteria) to filter the events dynamically. Although the events are triggered synchronously in a host application, they can be processed either synchronously or asynchronously, depending on your needs.
In SQL Server there are several SQL Server modules (a module is an executable file or a library for dynamic links) with different packages for the extended events. A package is a top-level container that contains all extended event objects such as events, actions, targets, predicates, etc.
The V8 search XE uses (by default) advanced events to collect data from monitored SQL Server instances (SQL Server 2008 and higher). The data is used by:
SQL Server | for workload analysis
SQL Server | for wait events
SQL Server | Drilldown for SQL activity, locks - Deadlocks alert.
From this screen, you can either request that V8 Search XE not use advanced events to collect data from monitored SQL Server instances, or customize V8 Search XE's use of advanced events through advanced options.
In Object Explorer, locate the XEvents menu item and expand it by clicking the plus sign "+".
When you press the right mouse button, this topmost item opens a context menu. From this menu you can access all configuration and action functions.
The Properies Tool window is located under the Object Explorer.
The Properties window displays different types of edit boxes, depending on the requirements of a particular property. Properties displayed in gray are read-only.
Lists the currently selected object or objects. Only objects from the active editor or designer are visible.
Lists all properties and property values for the selected object, by category. You can collapse a category to reduce the number of visible properties. When you expand or collapse a category, you see a plus (+) or minus (-) to the left of the category name. Categories are listed alphabetically.
Alphabetically sorts all design-time properties and events for selected objects.
Displays the properties for an object.
The description pane appears at the bottom of the Properties window and shows the property type and a short description of the property. You can turn the description of the property off and on using the Description command on the shortcut menu.
Editing the event session
In the V8 object explorer, you can edit the event session by right-clicking on its node. Then click Properties. The same multi-page dialog box appears. Click on "Edit Event Session" in the context menu. In this dialog you have all options to configure the session.
Configure the use of extended events:
In the Event Library section, select Event Names Only from the drop-down list.
Type sqlin in the text field, which filters and shortens the long list of available events using a Contains operator.
For example, scroll to the sql_statement_completed event and then click it.
From the Field drop-down list, select sqlserver.sql_textaus.
For Operator, select an operator such as a LIKE operator.
For Value, enter the option %SELECT%HAVING%.
In this two-part name, sqlserver represents the package name and sql_text the field name. The event we previously selected, sql_statement_completed, must be in the same package as the selected field.
Click the session editor on the left side of the "Add Target" button to add a target.
From the Type drop-down list, select event_fileaus.
This means that the event data is stored in a file that we can view.
In V8 Seach XE Object Explorer, right-click the event session node, and then click Start or Stop Session (1).
Under the menu item "Drop Event session" (2) you have the possibility to delete the session completely.
Appropriate T-SQL for the event session
You can view the generated T-SQL script as follows:
Right-click Session Script as > CREATE in > Clipboard, File, or Open directly in SSMS (3).
Default: Not enabled
Start / Stop V8 Services
With the activity Start(1)/Stop(2) the V8 services are started or stopped. The Start/Stop Service activity can be used to restart the services to collect SQL XE Profiler data or to stop the services to stop receiving data. With the Check activity, you can check the current state of the services.
Stopped V8 services (red):
Started V8 services (green):