In this article, we describe how to import extended SQL Server events data for the performance analysis of Business Central automatically and time-controlled into the V8 SQL database.
Introduction
Query performance monitoring is one of the most important diagnostic methods for detecting queries with poor performance. However, sometimes this can be a bit tedious. Therefore, one should use appropriate tools such as V8 Search XE and methods such as SQL Server Extended Events to monitor and analyze query performance metrics.
Most of our customers import data from the V8 Search XE Client manually on a daily basis. For this reason, we have decided to provide a template and show you in this article how to integrate this template into the SQL Server Agent.
If you are not yet familiar with SQL Extended Events, we will be happy to advise you.
Set up the PowerShell script (V8XELoaderTemplate.ps1) in the SQL Server agent
Please note that SQL Server Express and Azure SQL DB do not contain a SQL Server Agent.
Step 1
The V8XELoader module is a parameterized app and is normally started from the V8 Search XE Client. Depending on which task you want to fulfill with the V8XELoader, e.g. the import of SQL Extended Events files (XEvents) such as the "Long Duration (LD)", the parameters to be transferred are set up in the options of the V8 client.
These parameters must be entered manually in the PowerShell script "V8XELoaderTemplate". And saved in a new PowerShell script for the respective action. As a separate script is required for the action for each step in the SQL Server Agent.
Step 2
In SQL Server Management Studio (SSMS), navigate to the "Object Explorer" section, go to the "SQL Server Agent" section, expand it and click on the "New Job..." option.
Step 3
On the very first screen that appears, enter the basic information as follows:
Step 4
Click on "Steps" at the top left of "Select page" and then on the "New" button.
Step 5
On the screen that appears, enter the information as shown in the screenshot below and make sure that Type = "PowerShell".
To call up the PS1 file, the PowerShell command is as follows:
PowerShell.exe -File „C:\…\V8XELoaderTemplate.ps1“
Step 6
Create the desired schedule for the PowerShell script in the "Schedules" section.
Step 7
In this demo, we will not go into the areas of "Warnings", "Notifications" and "Destinations".
Then click on "OK" to create your job. It should now be displayed in the "SQL Server Agent jobs" section.
To execute the job, right-click on it and select "Start job at step". The job should then start executing or wait for the scheduled execution time.
How can I check the status of an order to see if it has been successfully completed? To answer this, you need to right-click on your order and select the "Show history" option.
In this view, a green tick indicates that everything went well (if the job was not successful, the symbol is a red circle with a cross). This view also contains other useful information such as the job duration.
You have several options in V8 Search XE to obtain information about the job.
Normally, the number of imported XEvents data and when opening the V8 Search XE client in the dashboard should have updated.
Another option is to execute a "SELECT query" from the SQL Server Management Studio (SSMS) for the "V8 NLog" table. Here you can check whether the import was successful.
A little tip: You should empty the "V8 NLog" table in good time so that the database does not grow unnecessarily large.
Alternatively, you can also use the TSQL Editor in V8 Search XE to check the "V8 NLog" table.
For example, if you open the "Long Duration XEvents" dialog in the V8 Search XE Client, you can use the filter dialog to see the smallest and largest date of the existing imported SQL Extended Events data.
Falls sie das V8XELoader PowerShell Script Template benutzen möchten, schreiben Sie bitte eine kurze Mail an info@dynamicsproject.com . Sollten Sie Unterstützung bei der Einrichtung benötigen, helfen wir ihnen natürlich gerne.
Your dynamicsproject.com Team
English 
German