Optimize SQL Server performance with Dynamics 365 Business Central on-prem and Dynamics NAV (all previous versions). There are so many factors that contribute to performance issues in the SQL world that it can be downright confusing where to even start.
As the Dynamics application and associated SQL databases grow, things change. Rows are changed, schema is updated, and often things slow down. These performance issues can occur suddenly or intermittently, but it's important to know how to distinguish performance issues so you can fix the individual problem.
The post looks at some steps you should take if your SQL database is behaving strangely. So you can diagnose exactly what is going wrong and focus on speeding things up. Optimizing SQL Server Performance with Dynamics 365 Business Central should start with checking your SQL Server configuration, if possible.
Is it the database? Or the server?
If you have access to the server, you can use a tool like System Monitor or Perfmon to check the processes running on the server itself. High memory usage, high CPU usage, and slow network traffic are red flags where the problem may not be the database itself.
There are many other system diagnostic tools e.g. V8 Search XE that can help with this process. However, one of them should at least point you in the right direction before you spend countless hours looking at tables, indexes and more.
SQL Server has an excellent profiling tool that can both fix existing problems and isolate slow-running queries. XEvent Profiler displays a live view of extended events. It's also a fantastic way to retrieve additional data about your database for performance optimization purposes (e.g. see which queries are executed most often, examine things like memory and CPU usage, identify potential blocks, etc.).
In V8 Search XE, optimizing SQL Server performance with Dynamics 365 Business Central is divided into several areas.
SQL Server System Check
V8 Search XE is infinitely useful and a good first step in diagnosing poor performance (or errors in general). It is useful in both development and production environments.
By default, all databases in the SQL Server instance are eligible for the various database-specific checks, and you can use the optional parameter to restrict these checks to specific databases. Valid from SQL Server 2012 onwards.
Performs checks:
- Processor
- Memory
- Pagefile
- I/O
- Server
- Service Accounts
- Instance
- Database and tempDB
- Performance
- Indexes and Statistics
- Naming Convention
- Security
- Maintenance and Monitoring
These various performance-related queries may be worth reviewing periodically to find potential problems that may arise over time.
Found the perpetrator? Find out now why the query is slow.
Once you have identified a particular query that has been identified as running slowly in the Analysis views in the V8 Search XE "SQL Server Check", you can view the execution plan to see exactly what SQL Server is doing behind the scenes. You can access it from a specific query simply by, the "Show Plan" button in the toolbar or from the ribbon:
Execution plans can easily reveal potential indexing issues that cause your entire table to be scanned instead of looking for exactly what it needs. At first glance, they may seem incredibly complex (and they can be), but once you've worked with them, you'll learn to identify patterns and what they're associated with (e.g., the X operation indicates a missing index for a table, etc.).
A few things to look for here:
- Look for Warnings - Warnings like "No Join Predicate" should be very obvious red flags that you probably need to address. Most warnings should at least warrant further investigation.
- Order of operations (cost) - Consider the most expensive operations and determine whether such ordering makes sense. Does a simple join consume 90% of the processing power for the entire call? If so, something could be wrong.
- Scans vs. Seeks - Neither is necessarily bad, but if one takes much longer than expected (one of them), it's probably worth determining if an index is missing (i.e., SQL Server scans the entire table instead of grabbing a well-defined seek value).
Understanding execution plans comes with experience, and hopefully you won't have to deal with them too often. But if you do, know that they can be a valuable ally in the fight against poor performance.
Investigate possible bad / missing indexes
SQL indexes are the A and O for performance optimization in your Dynamics NAV SQL database. You can think of them as similar to the indexes you might find in a book, in that they allow SQL to "know" where to look for specific data, rather than arbitrarily flipping through page after page.
It's worth noting that indexes are not "error-free" and as with most things, if you use them incorrectly, they can do more harm than good (i.e. imagine going on a scavenger hunt with exceptionally trails or lousy clues).
One great thing is that you don't always have to do this work yourself. People like us, have developed tools like V8 Search XE to recommend indexes based on table usage. Invaluable for optimizing SQL Server performance with Dynamics 365 Business Central on-prem and Dynamics NAV!
Statistics
As valuable as it can be to optimize SQL queries, it's worth noting that SQL Server does some of these "well" and others "poorly"by itself. This is accomplished by using statistics to monitor calls made, caching execution plans, and making judgments about how a particular call could/should best be executed.
Note that we mentioned "good or bad" and that is intentional. Statistics, while incredibly valuable, can also cause problems if they are not captured correctly or are outdated. In most scenarios, you probably won't need to deal with them on a regular basis, but it's important to know that they exist.
As with almost any technology, it is very unlikely that just one of these items will cure all of your performance issues with SQL Servers and Dynamics 365 Business Central or Dynamics NAV. Consider all of these as valuable tools in your troubleshooting arsenal and use them together when problems arise.
We will be happy to personally answer any further questions you may have on this topic.
Your dynamicsproject.com Team
English 
German