Symptom
Experice poor performance when you are using SAP Business One or running an SQL query directly on Microsoft SQL Server.
Environment
SAP Business One on MS SQL database.
Reproducing the Issue
Experice poor performance when you are using SAP Business One or running an SQL query directly on Microsoft SQL Server.
Cause
It could be as a result of non-optimized table statistics and indexes.
Microsoft SQL server automatically maintains the following information for each table in the database:
- Number of data pages used by each table.
- Number of rows in each table.
- Number of INSERT, UPDATE, and DELETE operations that affect the keys of the table since the last statistics update.
Resolution
The SQL Server Query Optimizer requires up-to-date statistics on all tables and indexes in the database in order to function effectively.
By default, the Query Optimizer updates statistics as necessary to improve the query plan, but in some cases you can improve query performance by manually updating the statistics on your company database.
- By default, the Auto Update Statistics option is enabled in the SQL Server.
- Right click in your affected database and choose Properties → Options.
Even though the Auto Update Statistics option is automatically set on large tables, the procedure might not work reliably and therefore we suggest that you run the following stored procedure to solve your problem:
On each affected company database from the SQL Server Management Studio, use the following syntax:
use [Company Database]
go
exec sp_updatestats
We recommend running the stored procedure during non-peak hours in order to avoid causing a heavy workload on your server.
For maintenance purposes, you can also run the stored procedure using a RSP task. See SAP Note 1522640 for details.
You can run SQL tuning advisor to identify and apply the proposed indexes, the support scope please refer to SAP Notes,
1376447 - Support Scope for SAP Business One Database Index Creation
1241422 - Database Defragmentation and Reindexing Improves Performance
