Notifications
Clear all

3107985 - Performance: How to Troubleshoot Slow Performance in SAP Business One or SQL Query

1 Posts
1 Users
0 Reactions
751 Views
admin
Posts: 444
Admin
Topic starter
(@admin)
Noble Member
Joined: 3 years ago

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:

  1. Number of data pages used by each table.
  2. Number of rows in each table.
  3. 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

Reply
Share:
x  Powerful Protection for WordPress, from Shield Security
This Site Is Protected By
Shield Security