Optimizing SQL Server Performance with Business Central or Navision

2 Members

Join now

Sign in with your Dynamics 365 Community account

If you don’t have a Community account, you can create one.

Share group

Location

Matera, Basilicata, Italy

Details

Best performing steps to optimize the sql server database used by Navision or Business Central

Group leaders

NS-20101536-0

Topics

Small and medium business | Business Central, NAV, RMS

Discussion

Join the group to participate in the discussion.

NS-20101536-0 on

Like (0)

1. Run this query to check the status of the indexes:

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
OBJECT_NAME(ips.object_id) AS object_name,
i.name AS index_name,
i.type_desc AS index_type,
ips.avg_fragmentation_in_percent,
ips.avg_page_space_used_in_percent,
ips.page_count,
ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND
ips.index_id = i.index_id
ORDER BY avg_fragmentation_in_percent DESC;

2. Follow the steps in the previous post

3. Rerun the query to see the difference.

Show previous comments

NS-20101536-0 on

Like (0)

Below are what I think are the best performing steps to optimize the sql server database used by Navision or Business Central:

1) Shrink database. This operation must be performed only the first time, or every time entire companies or millions of rows are deleted from tables:

2) Rebuild all indexes offline with specifying the fill factor. Schedule this process all day:

*Microsoft recommends using the default fill factor in most cases. However, if you know how the table will be used, you can edit it. A fill factor of 100 for tables that are not read-only would immediately cause a page split on an INSERT/UPDATE, so 100 is only suitable for read-only tables. Tables that have a high amount of writing should be between 50 and 70%. All other tables should be around 80 and 90, if they are mostly SELECTED rather than INSERTED and UPDATED.

3) Rebuild all indexes offline with specifying the fill factor (VSIFTIDX). Schedule this process all day:

4) Rebuild all indexes update statistics. Launch this process manually only the first time, because on database property there are two options 'Auto Create' and 'Auto Update Statistics'.

Show previous comments

Group leaders

NS-20101536-0

Leader

Members

VU-03111838-0

Member

There are no events in this group yet.

We add events regularly, so check back at a later time.