Category: Dynamics 365 Business Central

Import SQL Extended Events data automatically and time-controlled into an SQL database

Posted on | by InfoMaster

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.

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.

V8XELoader - 00

V8XELoader - 01

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.

V8XELoader - 02

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.

V8XELoader - 03

Step 3

On the very first screen that appears, enter the basic information as follows:

V8XELoader - 04

Step 4

Click on "Steps" at the top left of "Select page" and then on the "New" button.

V8XELoader - 05

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“

V8XELoader - 06

Step 6

Create the desired schedule for the PowerShell script in the "Schedules" section.

V8XELoader - 07

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.

V8XELoader - 08

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.

V8XELoader - 09

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.

V8XELoader - 10

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.

V8XELoader - 11

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.

V8XELoader - 12

 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.

V8XELoader - 13

V8XELoader - 14

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.

V8XELoader - 15

V8XELoader - 16

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

Business Central Performance Optimierung mit V8 Search XE – Teil 1

Posted on | by InfoMaster

V8 Search XE 4 has a powerful set of tools to analyze and solve Dynamics Business Central performance issues. This article deals with the quick diagnosis of SQL Server problems in conjunction with Business Central.

Part 1: V8 Search XE SQL Toolbox

With the V8 Search XE SQL Toolbox, administrators or system administrators can save a lot of time to analyze basic performance problems with Business Central and the SQL Server in seconds.

Every database administrator naturally has his own collection of scripts and tools to administer his SQL server. The collection of scripts we have selected in V8 Search XE is designed to give you a first impression of the status of your SQL server and your databases. The selection of TSQL queries is primarily intended for all system administrators to gain a quick overview of the status of the SQL Server and the respective Dynamics Business Central databases. The advantage of V8 Search XE is that you do not have a loose collection of scripts, but a structured setup with ready-made dialogs that show you the results of the queries.

The V8 Search XE SQL Toolbox is divided into two areas; the first area contains queries relating to the SQL Server. The second section is again divided into two parts. The first section deals with the selected database and general questions about the status of the SQL database. In the second section, the queries are specifically geared towards the topic of database indexing.

V8 Search XE SQL Toolbox 01

 SQL Server Check

The SQL Server Check section had a complex substructure with a myriad of SQL scripts that can document the state of the SQL server. You will not need every script every day, some of them may never be executed. Of course, we cannot judge how your SQL server landscape is structured. The included scripts offer you the possibility to react to different scenarios.

V8 Search XE SQL Toolbox 02

In jedem einzelnen Themenordner im SQL Server Check stehen ihnen diverse Abfragen zur Verfügung.

V8 Search XE SQL Toolbox 03

 SQL Server Database

The SQL Server Database area is divided into two categories. The first part relates to information about the status of the SQL database. The second category, SQL Server Database Index, is specifically focused on the performance and the associated indexing of the database. An optimal SQL database index strategy for Dynamics Business Central is extremely important. From our point of view, every system administrator should deal intensively with this topic. For example, when a Business Central page is opened or filtered for data in a page, Business Central sends a query to the SQL server to retrieve the data. If this query does not have an optimal index, the display of the data in the page is very often delayed. In the worst case, slow queries without an optimized index lead to table locks or deadlocks. We therefore believe that indexing Business Central SQL databases is extremely important. Every system administrator should deal intensively with this topic.

To use the SQL Server database queries, first select the SQL database about which you want to obtain information.

V8 Search XE SQL Toolbox 04

The name of the individual queries in the SQL toolbox indicates the content of the information.

V8 Search XE SQL Toolbox 05

Unfortunately, we cannot go into every single query in this article, as the collection in the SQL Toolbox is extensive.

Im zweiten Teil des Artikels präsentieren wir ihnen detaillierte Details zu den einzelnen Abfragen und zeigen ihnen, wie Sie mit den gewonnenen Erkenntnissen umgehen können.
 

FAQ

A.: No, you can administer any SQL Server database on the respective instance with the V8 Search XE SQL Toolbox. You even have the option of querying every SQL Server in your network with the SQL Toolbox.

A.: In our opinion, the Missing Index query is one of the most important queries in the toolbox. We recommend that you run this query at least once a week. When introducing V8 Search XE, we always point out that the proposed new index to be created should not be created blindly. Every index that is created on the database should be checked accordingly to ensure that it fulfills the desired functionality.
We have set up special queries in the SQL toolbox for this purpose. You can use these queries to check the newly created indices for their use. Of course, you can also use them to check existing indices of the respective SQL tables.

V8 Search XE SQL Toolbox 06

Gerne beantworten wir ihnen persönlich weitergehende Fragen zu diesem Thema.

Your dynamicsproject.com Team

Business Central Performance Tool

Posted on | by InfoMaster

Business Central Performance Tool. With the Business Central Performance Tool V8 Search XE you can increase the performance, analyze and improve the performance of your Dynamics Business Central or NAV solution.

A typical problem in the Dynamics world...

Some time ago we had an inquiry about the known problems in Business Central / NAV. The SQL Server performance for Dynamics Business Central / NAV would not be better than the updated NAV version 2013. Daily many table locks, frustrated users and an IT partner who is somewhat clueless.

Our offer...

Using V8 Care to analyze the "ACTUAL" state of the SQL Server and the Dynamics Business Central solution and to collect the bottlenecks in the database. True to the new motto "Data is the gold of the 21st century" . To our surprise, we got the answer: "Sufficient data for the analysis of the Dynamics Business Central problems had been collected." Since the interested party did not have its own IT experts in the company, he uses specialized IT service providers who use the SQL Server and Dynamics Business Central care for. It is true that we live in a world in which algorithms make the decisions in many areas about what has to happen if a certain result occurs or a data limit is exceeded. But in many application scenarios, people are and remain the final authority.

Our question, what kind of data was collected? With which SQL Server monitoring tool? There are fantastic real-time SQL Server monitoring tools. Unfortunately, SQL monitoring tools do not provide information about Dynamics AL code.

Thus, when it comes to monitoring IT security systems, the IT admin or chief security officer is ultimately the one who makes the decisions.

For this reason, we would like to briefly discuss the possibilities offered by our analysis tool V8 Search XE .

The problem:

SQL Server is capable of handling queries from a large number of concurrent users. When SQL Server handles requests from many clients, there is a high probability that conflicts will occur because different processes will request access to the same resources at the same time. A conflict where one process is waiting for another process to release a resource is called a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource, a process can hold a transaction lock and not release it.

Our solution:

To unblock a blocked process from Dynamics NAV / Business Central, we must first determine which process is the blocking process. And then, if possible, analyze and optimize the Dynamics NAV/BC blocking process.
In V8 Search XE, there are many different ways to identify a blocking and unblocking process, which are listed below:

1. SQL Server: Extended Events
2. SQL Server: Dynamic Management Views (DMV)
3. Windows Trace Events: SQL Trace Events
4. Business Central/NAV Server: C/AL tracing
5. Business Central/NAV Server: Business Central Telemetry Data

 SQL Server: Extended Events

The goal of the Extended Events installed by V8 is to present all the information collected by the Extended Events sessions in a readable form. The SQL queries that exceed the specified threshold (we recommend starting with 10 seconds) are recorded and collected in different Extended Events sessions. The SQL scripts are used as a basis for code analysis in V8 Search XE. In V8 XE Profiler, SQL queries are recorded when they are created by the Dynamics Business Central / NAV object.

V8 Extended Events sessions:
1. blocked_process -> Table locks and deadlocks
2. long_duration -> Long running SQL queries in Business Central/NAV
. 3. v8_user_NAV_Trace -> This session allows you to view SQL queries for all statements issued by the C/AL code (Windows user name with SQL Server SPID). These are stored in the V8 XE profiler and displayed as comments.
/*
Get connection from the pool.
User: ComputerName\WindowsUsername
*/
4. V8_FullSQL_NAV_Trace -> The session allows SQL queries to be displayed for all statements issued by the C/AL code. These are collected and displayed as comments for a complete transaction in the V8 XE profiler of the SQL server.



 SQL Server: Dynamic Management Views (DMV)

"DMVs" are query structures built into SQL Server that provide details about the health and performance of servers and databases. DMVs provide a common mechanism for extracting "all things SQL" as well as Windows operating system performance data. There are several DMV categories that return configuration information and performance data.



 Windows Trace Events: SQL Trace Events

SQL Trace events track a specific set of SQL statements executed by the Business Central Server instance against the Business Central/NAV database on SQL Server.
The event data collected includes: session ID, tenant ID, the Business Central/NAV user, and the SQL statement. The listing is only the SQL portion of the Business Central Server trace events.

Important!
To collect this data, you need one V8 service per Business Central Server instance. This feature is available from version 7 of V8 Services and requires .NET Framework 4.7.2, which is not installed by default on older Dynamics NAV.

It is also possible to collect individual event IDs.

The following table lists a few Dynamics SQL trace events. For example:

IDEvent (task/opcode)What is traced
1ExecuteScalar/StartSQL statements that query a database table and return a single field from a row in the query result.
2ExecuteScalar/StopSQL statements that query a database table and return a single field from a row in the query result.
3ExecuteNonQuery/StartSQL statements that return a number of rows from a database table
4ExecuteNonQuery/StopSQL statements that return a number of rows from a database table
5ExecuteReader/StartSQL statements that return a set of rows from a database table.

If you are using Dynamics Business Central version 18 or higher we recommend you to read this article Watch Business Central table locks live

 Business Central/NAV Server: C/AL tracing

Since the Microsoft Dynamics NAV 2013 version, the server has a feature that allows you to view the AL call stack for SQL commands. Full SQL Trace enables/disables tracing for all new and existing sessions per Dynamics Server instance.

This allows you to view SQL queries for any statements issued by the AL. All SQL statements between consecutive comments correspond to the AL statement starting with the first comment. These comments correspond to events when the connection is retrieved and returned to the Microsoft Dynamics NAV Server connection query. These comments are needed to separate SQL query issues from different clients on the same SQL connection. The SQL statement that matches these comments is issued by Microsoft Dynamics NAV Server, but not by AL. Comments containing only the username also correspond to SQL statements issued by Microsoft Dynamics NAV Server but not by Dyanmics AL code.

For example, Microsoft Dynamics NAV Server runs queries to calculate calculated fields that are displayed in the fact fields. These types of comments are required because Microsoft Dynamics NAV Server may run an SQL query without reconnecting to the pool, and they do not originate from Dynamics AL.

Important! To collect this data, you need one V8 service per Business Central Server instance.

Collecting and analyzing data
V8 Search XE offers two ways to collect event data from Business Central / NAV.

After the SQL trace has been captured, the data is stored in the SQL table. The trace was saved in the "V8 XEvents Full SQL Trace" table in the V8 Search XE database.

2. the complete AL programming code of the respective objects. The data is stored in the "V8 Performance Profiler" table in the V8 Search XE database.

The V8 Source Code Search module allows you to search your entire Dynamics NAV/BC codebase to find where specific code elements are referenced.

 Business Central/NAV Server: Business Central Telemetry Data

Business Central sends telemetry data for various activities and processes in environments and applications/extensions. Up to Business Central version 14 (Role Tailored Client) there was the very helpful function of “SQL Tracing for Microsoft Dynamics Business Central / NAV” (EnableFullALFunctionTracing = true). After Business Central was converted to the web client version (from version 15), the function in the Business Central server options has changed slightly.

Monitoring telemetry data gives you insight into the activity and overall health of your environments/apps, so you can diagnose issues and analyze operations that are impacting performance. The V8 service of V8 Search XE collects telemetry data for analysis and presentation to Business Central or Dynamics NAV on-premise versions.

V8 Service Telemetriedaten: Analyse- und Präsentationsdaten bis BC Version 14 (Role Tailored Client)

V8 Service Full AL Function Tracing: Analyse- und Präsentationsdaten bis BC Version 14 (Role Tailored Client)

V8 Service Telemetriedaten: Analyse- und Präsentationsdaten ab BC Version 15 (Web Client)

Zeigt, welche „SQL SERVER SPID“ in BC welche Session ID ist. Die Information ist für die Analyse von Performanceproblemen enorm wichtig!

SQL Statements von Business Central (sehr wichtig für Tabellensperren und Deadlocks)

The telemetry data is expanded by Microsoft with each new version of Business Central in order to provide more detailed information on any performance bottlenecks.
V8 Search XE can analyze and present telemetry data up to the latest version of Business Central.

Conclusion
This specific data, is very difficult to capture with a real-time SQL Server monitoring tool. You need to monitor all the Business Central Server instances in parallel.

V8 Search XE the Business Central Performance Tool is intended to enable all administrators and developers supporting a Dynamics NAV/BC ERP solution to make a statement about the weaknesses of the SQL commands generated by Dynamics Dynamics Business Central / NAV and to document them.

In any case, it pays off to have the Dynamics NAV / Business Central performance checked by a third party to find out what condition the ERP system and the SQL server are in.
After that, you can consider what options you then have for further action to improve SQL Server performance for Dynamics Business Central.

We will be happy to answer any further questions you may have on this topic in person.

Your dynamicsproject.com Team

Watch Business Central table locks live

Posted on | by InfoMaster

Watch Business Central table locks live - This has been the topic in the performance optimization of Microsoft Dynamics Business Central for years. Since version 18 of Dynamics Business Central, Microsoft has added new information in this area about the Table lock causers provided. This new information enables administrators and system administrators to take the right steps to provide AL programming colleagues in-house or at partners with the location in the AL code that is causing problems.

V8 Search XE (version 4) now offers all Dynamics Business Central version 18 or higher the ability to view live table locks in Business Central. The ability to identify locks in tables is also available for older Dynamics NAV / Business Central. However, retrieving the information about the table lock originator is only possible via an advanced workflow in V8 Search XE.

See Business Central table locks "live" - what information do the new Dynamics Business Central versions provide?

The database lock controls simultaneous access to the same data by multiple users. To protect a transaction against other transactions that modify the same data, the first transaction locks the data. The lock remains in place until the transaction is completed.

Users can be blocked from completing transactions with the blocked data. They usually receive a message indicating the locked state.

What does the administrator or system administrator see?

Probably most IT departments use a monitoring tool to identify possible bottlenecks or bottlenecks on SQL Server in connection with Business Central. For example, the Activity Monitor in SQL Server Management Studio will show you such a table lock.

No Dynamics Business Central programmer can do anything with this information. But how to solve such problems?

What does the administrator or system administrator see with V8 Search XE?

You get the same information as in the Activity Monitor when a table block occurs. For example, in our example you can see that SPID 54 is blocked by SPID 55. You the the Waitype and the Wairesource of the SQL Server. The type of information provided in almost all monitoring tools as basic information and many, many more details about the SQL Server behavior on the transaction.


But what the SQL Server monitoring tools don't give you is the transaction information from Dynamics Server. And this is where the new information from Dynamics Business Central version 18 or higher comes into play. The V8 Search XE Performance Analyzer offers the possibility to retrieve this information "live"!


You finally have the connection to the database lock on the SQL server and in real time which Dynamics AL object caused the problem.

You can see in this information the SPID that caused the "blocking" in the SQL server. With this information, administrators / system administrators and AL programmers together should be able to solve many problems of Dynamics users and thus provide a performance optimized Dynamics Business Central system to the company.

V8 Search XE offers a large number of integrated performance tools for SQL Server and Dynamics Business Central/NAV. With V8 Search XE you will optimize your Dynamics Business Central system for the highest performance.

We will be happy to personally answer any further questions you may have on this topic.
Your dynamicsproject.com Team

SQL Server query optimization for Dynamics ERP

Posted on | by InfoMaster

SQL Server query optimization for Dynamics ERP

SQL Server query optimization for Dynamics ERP

1. Why perform SQL server query optimization for Dynamics ERP systems like Business Central or NAV?

In recent years, you've seen incredible improvements in many areas of database servers:

  • Database optimizers are constantly improving and finding ways to make queries more adaptable and overcome areas of poor optimization.
  • Memory speeds have increased massively due to advances in both memory technology and network bandwidth, CPUs have become much faster, and memory prices have dropped dramatically.

However, there are still professionals (like us) who make their living tuning queries and training others to optimize queries. This process involves finding certain slow queries (long durations) that are critical to Dynamics Business Central or NAV performance. These "slow SQL queries" can result from making strategic changes, whether in code, database structure, NAV server instance configuration, or something else. Optimizations are often necessary to ensure that these specific SQL queries execute consistently at a certain speed or meet a required performance standard.
But so why SQL server query optimization despite all the improvements?

Very simple:

  • Data volumes are also increasing dramatically
  • Customer/user expectations for performance/speed of applications like Dynamics Business Central have also increased
  • Customers/users expect not to wait for results now - they want to see current status immediately

2. Who should perform the SQL query optimization?

SQL query optimization is performed by:

  • Database Administrators
  • C/AL or AL developers who specialize in performance or specifically in databases.

Full-stack developers for AL and C/AL generally do not perform query optimization unless they have a specific interest or work experience. This is more of a specialization than a "quick learn" task, so most full-stack developers simply don't have time and need to hire a more specialized person to help them. Teams that don't have a readily available specialist can periodically bring in consultants like us the DynamicsProject Team to help.

There are also many database administrators who manage SQL databases where only "basic" availability and performance are required. These SQL databases are used by cost-conscious organizations that don't need to tune every database like a "race car": Most of their SQL databases are used by internal users who are used to Dynamics ERP performance and speed moderation.

3. What skills are required in SQL query optimization?

Most of us are not wizards at TSQL, but many DBAs have gotten pretty good at query tuning (e.g., through our V8 Performance Workshops).

A very unscientific estimate of what skills make someone in query optimization:

40% - The time, interest, and resources (including a network of people to ask) to build a comprehensive profile of "performance information" - this is patterns that don't optimize well, boundary conditions where performance becomes poor, understanding trace flags, and configuration on TSQL.

30% - Interest and ability to learn how the database engine optimizes and processes queries using indexes and other resources, and what affects parallelism when running multiple queries simultaneously against a live database.

10% - Understanding of the TSQL language and various ways to rewrite a query in conjunction with Dynamics AL or C/AL programming to produce a specific result set. This often only works with database DevOps.

What is database DevOps?
Let's start with the definition of DevOps. DevOps is a set of practices that combine software development (dev!) and IT operations (ops!) with the goal of delivering more features, fixes, and updates faster, in alignment with business goals. Database DevOps applies the same principles and ensures that AL or C/AL database code is included in the same process as development code.

Database DevOps helps teams further identify and streamline the application development and release process by addressing a known bottleneck: AL or C/AL Dynamics source code changes.

4. SQL query optimization tools and their cooperation.
4.1 Execution plans

How the SQL query from Dynamics Business Central is executed behind the scenes.

  • "Estimated" execution plans show the decisions the optimizer has made to execute the query, including the estimated number of rows that will flow through the different parts of the plan
  • "Actual" execution plans are estimated plans that are updated with runtime statistics, e.g. how many lines have passed through the plan. If the plan is "adaptive", it contains some information about which options were chosen
4.2 Query memory

SQL Server 2016+, all editions

  • This function tracks execution plans and aggregated runtime metrics (duration, CPU usage) along with aggregated wait statistics
  • This also has the possibility to "freeze" plans
  • Query store information is restored with the database itself so that it can be shared between environments as needed.
4.3 Dynamic Management Views and Performance Indicators (DMV)
  • These help to understand the bottlenecks of the whole instance during slow performance
  • Example: Overall maintenance statistics for the instance and metrics on memory latency during the time the queries were poorly executed can help explain whether the query really needs to be optimized
  • Query optimization often requires callbacks for "workload optimization"
  • SQL trace and enhanced event traces (enhanced events are a lightweight performance monitoring system to collect data and are the foundation of V8 Search XE).
  • The "Old" SQL Profiler is difficult to use for query optimization in "live" operations, as they can easily slow down your workload and cause performance issues when tracing.
  • Execution plans (filtering does not help in this case, the plans are all examined / collected and the filter is applied too late).
  • Wait Statistics (filtering can help here, but the collected data is so massive that you have to be very careful - and sorting and querying the collected data is also quite cumbersome
  • "Business Central Server Trace Events" There are two event trace providers that publish different trace events in the event log: Microsoft-DynamicsNAV-Server and Microsoft-DynamicsNAV-Common. The Microsoft-DynamicsNAV-Common provider is for telemetry trace events only. All other events use Microsoft-Dynamics NAV server. Usually, you need to specify the event trace provider in the monitoring tool you use (e.g. V8 Search XE)
  • "SQL Trace Events" trace a specific set of SQL statements executed by the Business Central Server instance against the Business Central database on SQL Server

5. Difficult problems - dispute over resources

It's hard to predict how SQL queries will interact with each other in a live workload

Shared resources:
  • Memory for queries - a certain amount of memory must be allocated for sorting/linking/moving data in a query. Many queries running at the same time that require a lot of memory can cause problems with this. (Sometimes queries assume they need much more of this memory than they need, and it needs to be optimized - this will probably not be aware to users outside of a live workload).
  • The number of queries that make changes and the approach to blocking are difficult to predict outside of a live workload (changes in query plans can cause blocking when they were not previously present)
Changes in server resources - even improvements - can cause blockages if they were not there before
  • Example: Switching to a new server with more memory and faster CPUs resulted in an increase in lock wait times due to reduced in-memory wait times and faster query execution.

6. Is a check in the "live" SQL database required?

Yes. One example of this is parallelism.

Optimizing the degree of parallelism for a workload and for specific queries in that workload is usually quite hardware specific, and you need a live environment.

Workload- „Replays“ sind im Toolkit von SQL-Servern verfügbar, aber sie sind:

  • Repetitions only - you can't meaningfully "boost" the activity (deleting the same lines 10 times is not the same as deleting different lines 10 times)
  • Time-consuming to set up

7. Automated SQL server query optimization: history and development
7.1 Automated plan correction

SQL Server 2017+, Enterprise Edition

  • Built on the query store
  • Detects SQL queries that are sometimes fast and sometimes slow
  • Can only recommend changes if desired and set up
  • Can freeze plans, test to see if it helps, and respond accordingly (Freezing is intended as a temporary solution - it is recommended that a user evaluate the query for optimization as a longer-term solution)
  • Very good function for identifying parameter sniffing
7.2 Intelligent Query Processing
  • "Intelligent Query Processing" (IQP) have been released in the latest versions of SQL Server
  • Several of these features fix common query optimization issues in SQL Server
  • More information: Intelligent Query Processing in SQL Databases.

8. Common errors and pitfalls in SQL Server query optimization.
  • Lack of connection between DBAs and Dynamics development teams
  • Lack of knowledge of the execution plans in the team
  • Lack of understanding of SQL Server isolation levels and "optimistic" options
  • Lack of knowledge of how Dynamics AL or C/AL programming is implemented on the SQL Server

We will be happy to personally answer any further questions you may have on this topic.
Your dynamicsproject.com Team

Identifizieren von Tabellensperren in Dynamics NAV / Business Central

Posted on | by InfoMaster

Identify table locks in Dynamics NAV

The identification of table locks in Dynamics NAV has completely changed since Dynamics NAV 2013 compared to the Navision Classic Client. The way Dynamics NAV users connect to the SQL database has been restructured by the RTC Client. Users connect to the NAV service tier from the Windows client, web client, Sharepoint client or web services. The new so-called "three-tier architecture" separates database, server and client tiers.
The user account that runs the NAV service tier is the only one that actually connects to the SQL database. There are many good reasons for this:

  • Dynamics NAV Server uses an ADO.NET interface, which is a data access layer that supports SQL Server connection pooling. This simplifies the deployment of the Microsoft Dynamics NAV three-tier architecture for deployments where the three tiers are installed on different computers. The Windows Communication Framework (WCF) is used as the communication protocol.
  • It improves security, since in the SQL database there is no need to create a SQL user with login name for each of the required NAV users. As a result, security is simpler, since there is no need for an improved security model to previous versions (client-server connection).

Great stuff! However, a side effect is that it is difficult to see which SQL Server connection (SPID) is assigned to a NAV session. This is a problem when trying to determine a Windows user to a table locking/blocking issue.
In the days of the classic client where you could see the active database sessions and blocking information along with the ability to terminate sessions if necessary are unfortunately history due to the new architecture.

Since customers keep asking us about the problem of identifying table locks in Dynamics NAV and if we didn't have a tool or solution for this problem, we developed a solution. Our first approach to a solution was our V8 NAV SQL Studio. However, the architecture of the software was suboptimal when installing Dynamics NAV service animals on different computers.

Monitor Microsoft Dynamics NAV server events:

For better understanding we would like to briefly explain the difference between Locking, Blocking and Deadlocking for all non SQL Server specialists. Locking: Locks are a mechanism used by Microsoft SQL Server to synchronize simultaneous access to the same data by multiple users.
Before a transaction retrieves a dependency for the current state of data, for example by reading or modifying the data, it must protect itself from the effects that may result if another transaction modifies the same data.

Blocking:

A BLOCKING occurs when two connections need access to the same data item at the same time and one connection is blocked because only one connection can have access at any given time.

This message then appears in NAV:

Microsoft Dynamics NAV
—————————

The operation could not be completed because a record in the '...' table was locked by another user. Perform the action again.
---------
OK
---------

Deadlocks:

DEADLOCKS occur when two tasks permanently block each other because each of the tasks maintains a lock on a resource needed by the other task.

Consequently, transaction A cannot be completed until transaction B is completed. However, transaction B is blocked by transaction A.

This message then appears in NAV:

Microsoft Dynamics NAV
—————————

The operation could not be completed because a record in the '...' table was locked by another user. Perform the action again.
---------
OK
---------

Locking is an integral part of Microsoft SQL Server to ensure parallelism and physical integrity of each transaction. Blocking is bad when a connection/transaction waits unnecessarily for a long time, and deadlocking is a phenomenon that should never occur.

V8 Search XE - Detect and fix deadlocks and blockages in Dynamics NAV Server.

Event tracing in V8 Search XE provides detailed information about what occurs on the Microsoft Dynamics NAV server when users are working with Microsoft Dynamics NAV and blocking or deadlocks occur. All data on specific Dynamics NAV trace events is captured in a V8 SQL Server database. This can help you identify and analyze issues or conditions affecting Dynamics NAV / SQL Server performance.

Identify table locks in Dynamics NAV. With V8 Search XE event tracking, you can dynamically monitor Microsoft Dynamics NAV servers without restarting the server or Microsoft Dynamics NAV clients.

You can use the V8 Search XE to track, for example, the following operations on Microsoft Dynamics NAV Server instances and the SQL Server:

  • Execution of SQL statements from Microsoft Dynamics NAV Server.
  • Execution of NAV C/AL functions.
  • Execution of Microsoft Dynamics NAV reports, queries and XMLports.
  • Process number, status, locks and commands (TSQL and C/AL or AL) executed by the active users.
  • Locked objects as well as the type of locks set up.
  • Full Dynamics NAV SQL trace of a locking process (SPID | DYNAMICS NAV USER).
  • Full Dynamics NAV SQL trace of active users by total waiting time (SPID | WAITTIME).

Which Dynamics BC / NAV Windows user locks the table?

V8 Search XE Tutorial Video



Read more…

We will be happy to personally answer any further questions you may have on this topic. Simply contact us via our contact form or by e-mail to info@dynamicsproject.com!

Your team from DynamicsProject.com

Back to Top

SQL Server Buffer Cache

Posted on | by InfoMaster

SQL Server Buffer Cache

What is the SQL Server Buffer Cache and how does it affect the performance of, for example, Microsoft Dynamics ERP systems.

In SQL Servers, the "Buffer Cache" is the memory used by applications such as Dynamics 365 Business Central to quickly retrieve frequently accessed data. When data is written to or read from a SQL Server database, the Buffer Manager copies it to the "Buffer Cache" (also referred to as Buffer Pool - buffer memory of a database management system). When buffer pool is full, older or less frequently used data pages ("data pages") are moved to disk.

Why should you monitor the buffer cache?

Memory usage can have a significant impact on performance. If there is not enough memory, data pages are often deleted from the buffer cache. This slows down queries because SQL Server must go to disk to find the data page. Then the server must restore the data page to the buffer cache and then read the page before returning the query result.

There are many reasons why queries execute slowly. But if you want to rule out memory problems, take a look at what's going on in the buffer cache. A look inside shows which database, table, or index is loading memory and putting pressure on the buffer.

Use the following SQL query to determine which database is using the most memory (for a Dynamics 365 Business Central system, this should always be the "live" database):

SELECT CASE database_id
		WHEN 32767
			THEN 'ResourceDb'
		ELSE db_name(database_id)
		END AS database_name
	,COUNT(1) / 128 AS megabytes_in_cache
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id)
	,database_id
ORDER BY megabytes_in_cache DESC;

Run this query on the database you want to examine to identify the table or index that occupies the most memory.

USE [your database]

SELECT COUNT(1) / 128 AS megabytes_in_cache
	,name
	,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN (
	SELECT object_name(object_id) AS name
		,index_id
		,allocation_unit_id
	FROM sys.allocation_units AS au
	INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
		AND (
			au.type = 1
			OR au.type = 3
			)
	
	UNION ALL
	
	SELECT object_name(object_id) AS name
		,index_id
		,allocation_unit_id
	FROM sys.allocation_units AS au
	INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id
		AND au.type = 2
	) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name
	,index_id
ORDER BY megabytes_in_cache DESC;

Manage memory with metrics

Although it is helpful to check databases and indexes for memory overuse, tracking buffer cache metrics is the best way to identify and troubleshoot performance issues.

Here are the top five metrics to monitor to improve memory-related performance issues (SQL Server Buffer Cache):

1. Buffer Cache Hit Ratio

  • This metric shows how SQL Server uses the buffer cache
  • The hit ratio indicates the percentage of page requests that were executed by data pages from the buffer cache compared to all data page requests
  • Pages that are not found in the buffer cache are read from the hard disk, which is much slower
  • The ideal buffer-to-cache ratio is 100 (i.e. SQL Server reads all pages from the buffer cache and none from disk)
  • The recommended buffer cache value is greater than 90.

2. page life expectancy (PLE)

  • The page life expectancy measures how long (in seconds) a data page remains in the buffer cache
  • The longer the PLE is, the better the chance that SQL Server will read the pages from the buffer cache and not have to go to disk
  • When there is not enough memory, data pages are deleted from the buffer cache more frequently to free up memory for new pages
  • In the past, a "normal" PLE value was 300 seconds, when systems had much less memory than today
  • For newer SQL Servers, the following formula is used to determine "good" PLE :Page lifetime = 300 seconds for every 4 GB of RAM on your server.
  • The PLE should remain stable if monitored over time
  • Rapid, frequent decreases indicate memory problems
  • A decline of more than 50% should be investigated immediately

3. Page reads / Sec. (Server)

  • This metric shows how many physical reads (i.e. reads from disk) occurred in one second across all databases in an instance
  • Physical reads are expensive and slow
  • Reduce physical reads by using a larger data cache, smart indexes, and more efficient queries, or by changing the database design
  • The recommended value is below 90.
  • A value above 90 indicates insufficient memory and indexing problems.

4. Page Writes/Sec

  • This metric indicates how many times pages were written to disk in one second at server level
  • The recommended value is below 90.

5. Pages Input/Sec and Pages Output/Sec (Memory Counters)

  • Page input / sec. Is the number of pages that are inserted from the hard disk per second
  • The page output / sec. Is the number of pages written to disk per second to make room in the buffer cache
  • Pages / sec. is the sum of page input / sec. and page output / sec.
  • If the value for pages / sec. is consistently more than 50, additional testing is required

In V8 Search XE you can find a query to measure the SQL Server buffer cache at:

V8 Search SQL Server Buffer Cache Navi

V8 Search SQL Server Buffer Cache

An error-free SQL Server buffer cache is an important component of optimizing SQL Server query speed. Although memory problems are just one of several factors that can slow down query responses, they are relatively easy to identify and troubleshoot.
Tracking these five key metrics can help you keep data pages in the buffer pool longer, so SQL Server doesn't have to waste time searching the disk before returning query results.

We will be happy to personally answer any further questions you may have on this topic.
Your dynamicsproject.com Team

Warum ist Dynamics NAV langsam

Posted on | by InfoMaster

Warum ist Dynamics NAV langsam?

Warum ist Dynamics NAV langsam? Im Laufe der Jahre sind wir bei DynamicsProject.com oft Unternehmen um Hilfe gebeten worden, da das Dynamics NAV System aus irgendeinem Grund immer langsam wurde. Sehr oft bei Unternehmen die eine Dynamics NAV Datenbank Größe über 50 GB haben.

 

Grundsätzlich sind die neuen Dynamics NAV Versionen und die Microsoft SQL Datenbank-Server gut aufeinander abgestimmt. Das Dynamics NAV Standardsystem („Dreischicht-Architektur“) arbeitet tadellos mit SQL Server zusammen. Aber wer arbeitet schon mit dem Dynamics NAV Standard?

 

Nun stellt sich die Frage, wo die Performance verloren geht. Wir bei Dynamics Project unterscheiden bei unseren Performanceanalysen zwischen Infrastruktur- und Anwendungs-Engpässen.

 

 

1. Die Infrastruktur

 

Die Infrastruktur ist schuld, wenn das System träge reagiert. Entweder ist der Server zu langsam oder das Netzwerk ist überlastet. Das ist die Wahrnehmung der meisten Dynamics NAV User. Leider ist das auch sehr oft auch die erste Aussage der Dynamics NAV Consultants gegeben über dem Kunden.

 

Die Lösung muss sein, entweder das Verbessern der Komponenten oder das Austauschen der Hardware. Oft können große Verbesserungen durch die Modernisierung der Infrastruktur vorgenommen werden. Allerdings gibt es noch andere Möglichkeit, die oft übersehen wird, nämlich die Anwendung.

 

2. Die Anwendung

 

Für die meister Dynamics NAV Anwender ist der RTC oder Classic Client nur eine geheimnisvolle Sache, die geschieht, wenn der Benutzer mit dem Computer interagiert. Die Geschwindigkeit dieser Anwendung wird oft nur im Zusammenhang mit der Leistung der aktuellen Workstation, Server oder Netzwerk gesehen. Diese Annahme könnte nicht weiter von der Wahrheit entfernt sein. Eine schlecht programmierte Dynamics NAV Anpassung kann noch viel schlimmer Auswirkungen haben, als jedes Performance-Problem der Hardware.

 

Unsere Empfehlung:

Sehen Ihr Dynamics NAV und Microsoft SQL Server immer als eine Einheit. Leider wird der Microsoft SQL Server oft nur als „Daten“ Behälter angesehen und dem entsprechend nicht ausreichend im ERP-Gesamtkonzept berücksichtigt.

 

Was können Sie tun?

Je nach Einsatzzweck kann der Microsoft SQL Server sehr komplex erscheinen. Und wenn es um Leistungsoptimierung mit Dynamics NAV geht, wissen viele DBAs einfach nicht, wo sie anfangen sollen. Leistungsoptimierung ist definitiv einer der Bereiche, wo Erfahrung ein guter Lehrer ist.
Aber irgendwo müssen jeder Datenbankverantwortliche beginnen Erfahrungen zu sammeln.

 

Wir möchten Ihnen hier ein paar generelle Anregungen zum SQL Server Performance-Tuning geben. Hierbei handelt es um einfache Dinge der Leistungsoptimierung für den SQL Server.

 

1. Identifizierung problematischer SQL Abfragen

 

In einer bestimmten SQL Server-Instanz gibt es vermutlich 7 bis 10 Dynamics NAV Abfragen, die für ca. 80 bis 90 Prozent der schlechten Performance verantwortlich sind, die im SQL Monitoring (z.B. Ablaufverfolgung mit dem SQL Server Profiler) im Laufe des Tages zu sehen sind.
Wenn Sie diese „Problem“ Abfragen identifizieren können, dass bei Dynamics NAV nicht ganz so einfach ist, haben Sie eine gute Ausgangsbasis, um die Beeinflussung auf die Gesamtleistung Ihres Servers zu optimieren.

 

Einrichten einer Blocked Process Report-Ereignisklasse (SQL Server Extended Events)

 

Die Blocked Process Report-Ereignisklasse zeigt an, dass ein Task länger als die angegebene Zeitspanne blockiert wurde. Diese Ereignisklasse schließt keine Systemtasks oder Tasks ein, die auf Ressourcen warten, für die keine Deadlocks erkannt werden können. Wir benutzen die SQL Server Extended Events um z.B. Sperren von Dynamics NAV zu analysieren.

 

2. Suchen Sie nach Datenträgerengpässen I/O

 

Die Auflistung der I/O-bezogenen Datenbank-Management-Objekte (DMOs) hilft ihnen bei Untersuchung, wenn Daten geschrieben und vom Datenträger gelesen werden. I/O Engpässe sind mit die wichtigsten Gründe, warum die Leistung des SQL Server leidet. Wenn Sie feststellen, dass viele physische I/O-Engpässe auftreten, sollte der Schritt sein, die Ursache aller Abfragen mit höhen physischen I/O sind zu finden, bevor Sie mehr Hardware hinzuzufügen.

 

Sie haben relativ einfache Methoden zur Verfügung, um festzustellen, ob Sie I/O Probleme haben:

  • sys.dm_exec_query_stats – Gibt die Aggregatleistungsstatistik für zwischengespeicherte Abfragepläne im SQL Server zurück.

  • sys.dm_exec_connections – Gibt Informationen über die zu dieser SQL Server-Instanz hergestellten Verbindungen zurück.

  • sys.dm_exec_sessions – Ist eine Sicht des Serverbereichs mit Informationen zu allen aktiven Benutzerverbindungen und internen Tasks. Sie können hiermit die aktuelle Systemlast anzeigen sowie eine relevante Sitzung ermitteln.

  • sys.dm_os_workers – Gibt eine Zeile für jeden Arbeitsthread im System zurück.

 

3. Indexverwendung

 

Die sys.dm_db_index_operational_stats DMF (Dynamic Management Function) ist eine oft vernachlässigte Quelle von Informationen. Sie kann Ihnen wertvolle Informationen über den benutzten Index einer Tabelle geben. Durch die Nutzung dieser DMF, können Sie alle Arten von Informationen entschlüsseln, nicht nur welche Indizes, sondern auch wie sie verwendet werden.

 

Conclusion

Inzwischen werden Sie bemerkt haben, dass einige dieser Themen größere Konzepte und Techniken erfordern, um in die Tiefe der Materie vorzudringen. Allerdings ist keines dieser Themen unlösbar für die DBAs.

 

Lesen Sie im zweiten Teil: Warum ist Dynamics NAV langsam – Die neue Sicht der Dinge.

If this or similar topics have piqued your interest, I would be happy to engage in an open dialogue with you.

 

Your team from DynamicsProject.com

Back to Top