Edit

Optimizing SQL Server Performance with Business Central

  • Article

The following articles describe how to optimize performance in Dynamics 365 Business Central when accessing data from the SQL Server database.

Setting SQL Compatibility Level to Optimize Database Performance

Data Access

Table Keys and Performance

Bulk Inserts

AL Database Methods and Performance on SQL Server

Query Objects and Performance

Configuring Query Hints for Optimizing SQL Server Performance with Business Central

Using Read Scale-Out for Better Performance

Troubleshooting: Analyzing Long Running SQL Queries Involving FlowFields by Disabling SmartSQL

Troubleshooting: Using Query Store to Monitor Query Performance in Business Central

Troubleshooting: Using the Event Log to Monitor Long Running SQL Queries in Business Central

Database Monitoring and performance tuning with Azure SQL Database

Azure SQL Database and Azure SQL Managed Instance provide tools for collecting metrics and diagnostic information to monitor your database and improve its performance. For more information, see Azure SQL Database and Azure SQL Managed Instance monitoring and performance tuning.

Integrating directly on SQL Server objects

Warning

During operations such as upgrade and app/extension synchronization, the Business Central Server synchronizes the metadata model defined in AL to physical tables on the SQL Server database.

While it's possible to integrate directly with SQL Server objects on the database (bypassing the Business Central Server), it's not recommended or even supported.

Altering SQL objects created by the Business Central Server directly on the database can break operations such as upgrade and app/extension synchronization.

Adding additional SQL objects such as triggers or stored procedures directly on the database can break operations like upgrade and app/extension synchronization, but will also break system integrations that depend on such additional objects if the table schema created by the Business Central Server synchronization changes.

See Also

Installation Considerations for Microsoft SQL Server
Microsoft SQL Server documentation
SumIndexField Technology (SIFT)