Analyzing database performance
Analyzing database performance for Business Central on-premises covers how to find and troubleshoot slow SQL queries. It answers questions about the SqlLongRunningThreshold setting, reading long-running queries in the Event Log, and using SQL Server Query Store.
Path: Business Central on-premises > Administration > Analyzing database performance · tier official · system platform · narrative reviewed (checked by Opus)
Overview
This section is about finding slow database queries in Business Central on-premises. It offers two approaches: logging long-running SQL queries from the Business Central server, and using Query Store on SQL Server or Azure SQL Database.
Start with the pages on long-running queries. One explains how to set the SqlLongRunningThreshold on the server (with the Set-NAVServerConfiguration cmdlet) so that queries over the threshold are logged. The other explains how to read those entries in Event Viewer and use the AL call stack to pick queries to optimize. The Query Store page is for deeper troubleshooting with query history, runtime statistics and query plan tracking.
Key points
- SqlLongRunningThreshold on the Business Central server defines when a SQL query counts as slow.
- The threshold is configured with the Set-NAVServerConfiguration cmdlet.
- Long-running queries are written to the Event Log and can be viewed in Event Viewer.
- Analyzing slow queries together with their AL call stacks helps find the code to optimize.
- Application Insights is also covered alongside the Event Log for monitoring long-running SQL queries.
- Query Store works with Business Central on SQL Server or Azure SQL Database.
- Query Store provides query history, runtime statistics and query plan tracking for troubleshooting.
Learn pages
- Monitoring Long Running SQL Queries to the Event Log: This topic provides an overview on how to monitor long running SQL queries in the event log starting with NAV 2017.
- Troubleshooting: Using Query Store to Monitor Query Performance: Use SQL Server Query Store to troubleshoot query performance. SQL Server Query Store feature provides insight on database query plan choice and performance.
- Using Event Log to Monitor Long Running SQL Queries: Shows how to monitor long running SQL queries in Event Viewer. Use the information determine SQL queries that are good candidates for optimization.
Source: Microsoft Learn (CC BY 4.0). Descriptions are Learn’s own.