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