Dynamics 365 BC SQL Server Performance

Dynamics 365 BC SQL Server performance - what should you look for in a monitoring tool? What is required?

A monitoring / analysis tool such as V8 Search XE needs to give you an understanding of the often complex performance patterns that SQL databases exhibit under load. So that you can predict how you will handle database expansion or enlargement. It also needs to help us recognize the symptoms of stress and act before they become problems. The tool should reveal what happened in your database when an intermittent problem occurred and noticeably affected Dynamics 365 BC SQL Server performance.

With Dynamics 365 BC/NAV Server responsibility, it is possible to use SQL Server's own tools to capture the diagnostic data you need to identify concerning changes or trends. Unless the person responsible for initially responding to performance problems knows where to look, the amount of information available in SQL Server is little more than a distraction. The difficulty is often finding the information needed for your system landscape from thenumerous server metrics that can indicate resource conflicts, error conditions and bottlenecks on SQL Server.

As a result, you should deploy a monitoring tool that will alert you that something is not right with the SQL Server databases and provide just enough information with the warning. Once the type of problem is sufficiently understood, you can use various additional performance metrics available in SQL Server and Dynamics NAV servers as needed.

Signs of possible performance problems

• The current performance is unusually slow.
• The server is under high processor pressure.
• There are more incidents than usual of blocking, deadlocks, and long-running queries
• There is an increasing number of wait times of certain types
• Performance degrades significantly beyond what would be expected as database size increases
• Memory requirements and the amount of space needed for logging are increasing rapidly

Collecting diagnostic data

Both Windows Server, Dynamics NAV Server, and SQL Server have integrated performance tools, and we have V8 Search XE, a tool to troubleshoot performance issues. Some of these tools provide general metrics, while others are designed to check very specific conditions that can confirm a theory about what is causing a problem. For example, is the cause of a long running query (long duration) a missing index, poor query logic such as a "non-SARGable query filter"? You can use extended events (XEvents), perfmon, DMVs (Dynamic Management Views) and traces to provide this data.

If performance problems persist after the database administrators have been alerted to the problem, you can probably find the root cause with V8 Search XE. However, if the problem is temporary and intermittent, it can be very difficult because the details you need have disappeared. By the time the problem is reported, it's too late and you'll have to wait for it to occur again.

Database administrators need a tool like V8 Search XE that continuously collects the necessary diagnostic data at regular intervals so they can quickly troubleshoot problems regardless of when they occur. An effective monitoring system must work on every SQL Server to connect all the dots across the server landscape.

Performance monitoring requirements

There are a number of ways you can use your monitoring tool to identify and diagnose performance problems based on the diagnostic data you collect. Here you can cover just a few of the more obvious possibilities.

Quickly connect server stress conditions to their root cause

Suppose Dynamics users report slow database performance, and your monitoring tool indicates that CPU utilization is "high". Does this mean that "processor pressure" is causing the performance problem? It depends! If the server is busy, your processors should be working hard to process all the requests from users! A good monitoring tool will help you quickly correlate this one metric with others that confirm (or rule out) CPU pressure and with the specific queries being executed at the time of the problem.

Is the CPU only busy because the server is busy? Is the number of connections or the number of user requests per second higher than normal? The only way you can know for sure is if your monitoring tool has a baseline for each of these metrics. In other words, if you can use it to quickly compare current values with values for the same metrics yesterday at the same time, or at the same time in the last few days or weeks.

Is the CPU increase accompanied by a significant increase in the time that user processes spend just waiting for the CPU? These frequent signal waits, along with high CPU utilization, may indicate CPU pressure. Again, you need a baseline for signal wait times to determine this, and your monitoring tool should make it very easy to add a metric to collect this data if needed.

Your performance metrics baselines may show that CPU pressure is normal during the last week of each month. At this point in the performance investigation, you want to directly associate the server stress condition, whether high CPU, memory, or I/O utilization (or all three), with long-running queries during that period. Excessive CPU utilization is often caused by long-running, complex queries that simply require a lot of processing power. Poorly tuned T-SQL statements and poorly designed indexes can lead to high loads on all resources.

Are there ways to reduce the pressure by optimizing these queries or the database indexes? If not, there are steps you can take to optimize CPU allocation for this period. For example, if they are cloud-based servers, can you elastically expand them to use more processors, optimizing the performance of your important reporting queries?

Without the tools to determine if the bottlenecks are caused by poorly written queries or missing indexes, the temptation is to increase the number of cores. Using a monitoring tool, it is often possible to find a small number of queries that are causing most of the bottlenecks. Once these queries are identified, only the queries and indexes need to be optimized.

Analyze server wait times

Wait in the statistics area with the powerful tool to determine the biggest bottleneck on your SQL servers. Each time a request is forced to wait, SQL Server records the length of the wait and the cause of the wait (the wait type), which generally indicates the resource the request was waiting for but could not retrieve. Wait times can be related to parallelism, I/O slowness, or blocking, for example. This is reported as total aggregate values recorded since the server was last restarted. DBAs typically check wait times to understand loads on the server.

Again, knowing that a particular wait time metric is "high" doesn't mean a problem in itself. Your monitoring tool needs to help you correlate it with other metrics and analyze those wait times to find the actual queries that are affected by them:

A good monitoring tool will also report the most frequent wait times over a period of time so that the DBA can easily identify trends or outliers.

Let's say you observe high CXPACKET waiting times. Is this a problem? This type of waiting occurs whenever queries are executed on multiple processors in parallel. A common misdiagnosis with very high CXPACKET wait times is that the parallel workload is overloading the hard disk I/O subsystem. However, first check the queries affected by these wait times. If it is an OLTP system such as Dynamics 365 BC or NAV, where transactions should be short and fast, you should examine the queries that have parallel schedules and look for optimization opportunities. Similarly, excessive I/O latency, for example, may simply be a sign that a large table is being repeatedly scanned and that an index can support the query.

Fast troubleshooting in real time

As proactive as a DBA wants to be in resolving issues before they become real problems, the monitoring tool always plays an important role in resolving real-time performance issues.

If a important business server is currently experiencing serious performance problems, you should know very quickly which connections and sessions are affected and what activities are taking place on the server. One way a professional monitoring tool can really help is to not only detect excessive "blocking"or report that a deadlock has occurred, for example, but also provide within the alert a list of active processes and some simple visualizations of the blocking or deadlocking chain. The following image shows a blocking chain:

The blocking in SQL Server is like a junction in a traffic jam. The query at the heart of the problem affects other queries, making them wait until the problem query has executed or finished. Common causes are excessive I/O due to scans for large tables or transactions that remain open after errors. To resolve the problem in the short term, the faulty query must be found and possibly the connection broken. A good monitoring tool, such as V8 Search XE identifies the cause of the blockage so that appropriate action can be taken.

A deadlock is a circular lock chain because each processing in the blocking chain waits for one or more other processes in the same blocking chain, so none can complete. This is a severe error condition that SQL Server resolves by terminating and resetting one of the processes. The DBA must immediately investigate to find out what caused the deadlock and take action to prevent it from occurring again.

Before and after: Baselines

Another important use of the monitoring data we collect is to create visual baselines. This helps the DBA understand if performance is predictably degrading over certain time periods or if the server's current performance is typical or unusual. They can also watch for changes as data grows or the system becomes busier over time.

How often do we hear, "Dynamics NAV was slow yesterday" or "It was faster before we released the upgrade" or "NAV is slow right now"? To validate these claims and figure out the root cause, we need resource usage data from yesterday to compare to today.

However, you may notice that the CPU utilization of this processor is higher now than last week. Is this a concerning trend? To support potential optimization efforts, you need to determine if you are seeing what can be described as normal behavior. Or, you are actually seeing a trend that is leading to pressure on this resource. Unless you are taking action, such as looking for ways to better distribute the load among the available processors during these busy times.

Conclusion

SQL databases are extremely complex even before you try to understand their behavior under load in production operations. You need this understanding of performance to predict how you will handle expansion or scale. You need to prevent stress symptoms from becoming problems that impact service. You need the information to make intelligent decisions about hosting. You also need to understand what happened in the Dynamics 365 BC/NAV SQL database when an intermittent problem occurred.

All RDBMSs have diagnostic tools. However, they are best used after understanding the general nature and context of a performance problem. Especially when time is short and problems need to be fixed before users complain. As operations staff workloads increase, it pays to have a system that keeps an eye on the Dynamics 365 Business Central / NAV system and issues alerts when a combination of metrics shows a concerning trend.

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