Small and medium business | Business Central, N...

Database Size is increasing due to index size

(3) Share

Report

Posted on by ND-16070845-0

Dear Microsoft Support,

We are experiencing performance issues and Database size issue in our Business Central environment, particularly related to the G/L Entry table (Table ID: 17). Upon reviewing the Table Information page within Business Central, we have noticed that the index size on this table has significantly increased, which we suspect is contributing to degraded performance during posting and reporting operations.

We kindly request your assistance in:

Reviewing the index fragmentation and size of the G/L Entry (Table 17) table
Performing any necessary index maintenance or reorganization
Advising if any additional action is required from our end.

Please treat this as a performance-impacting issue. Let us know if any further information is required to proceed with the investigation.

Thank you for your support.

Regards,

Dinesh

Categories:

Dynamics 365 Business Central

Table Info.jpg

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

DAnny3211 11,397 on at

Like (0)

Report
Hi Dinesh,

You're right to focus on index size and fragmentation, especially in high-transaction tables like G/L Entry (Table 17), as it can significantly impact performance and storage.

Recommended Actions:

Review Index Fragmentation:

Use SQL Server Management Studio (SSMS) to run:

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.[G_L Entry]'), NULL, NULL, 'DETAILED')

Identify indexes with fragmentation >30% for potential rebuild, and 10–30% for reorganize.

Perform Index Maintenance:

Schedule regular index maintenance using SQL Agent jobs or a third-party tool.

Example:

ALTER INDEX [IndexName] ON [dbo].[G_L Entry] REBUILD;

Check for Unused Indexes:

Use sys.dm_db_index_usage_stats to identify indexes that are rarely used and consider removing them if safe.

Database Growth Monitoring:

Enable SQL Server Alerts or use Azure Monitor if hosted in Azure SQL to track sudden growth.

Business Central Optimization:

Review whether custom extensions or reports are causing excessive reads/writes to G/L Entry.

Consider archiving old entries if feasible.

Let me know if you'd like help writing a maintenance script or analyzing index usage!

Please verify if this response was helpful.

Best regards!

Was this reply helpful? Yes No
ND-16070845-0 20 on at

Like (0)

Report

I am using Cloud Environment so don't have SQL Access.

Was this reply helpful? Yes No
Suggested answer

Sumit Singh 10,079 on at

Like (0)

Report
Findings:-
One Retail Group LTD Live

Records: 23.3 million

Total Size: ~22.2 GB

Index Size: ~19.4 GB (≈ 87% of total size)

Data Size: ~2.8 GB

Index size is disproportionately large, indicating severe index fragmentation or over-indexing.
Recommended Actions
1. Review and Rebuild Indexes

Use SQL Server Management Studio (SSMS) or PowerShell to:

Check fragmentation:

SELECT

  dbschemas.[name] as 'Schema',

  dbtables.[name] as 'Table',

  dbindexes.[name] as 'Index',

  indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo."G_L Entry"'), NULL, NULL, 'LIMITED') AS indexstats

INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

  AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.avg_fragmentation_in_percent > 10

Rebuild or reorganize indexes:

ALTER INDEX [IndexName] ON [dbo].[G_L Entry] REBUILD;

-- or

ALTER INDEX [IndexName] ON [dbo].[G_L Entry] REORGANIZE;

Rebuild if fragmentation > 30%, Reorganize if between 10–30%.

2. Evaluate Unused or Redundant Indexes

Use SQL to find unused indexes:

SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, i.index_id,

       dm_ius.user_seeks, dm_ius.user_scans, dm_ius.user_lookups, dm_ius.user_updates

FROM sys.indexes AS i

INNER JOIN sys.dm_db_index_usage_stats AS dm_ius

   .object_id = dm_ius.object_id AND i.index_id = dm_ius.index_id

WHERE OBJECTPROPERTY(i.object_id,'IsUserTable') = 1

  AND i.name IS NOT NULL

  AND dm_ius.database_id = DB_ID()

  AND OBJECT_NAME(i.object_id) = 'G_L Entry'

Drop indexes that are never used and have high update cost.

3. Enable Compression (if not already)

Consider row or page compression for the G/L Entry table:

ALTER TABLE [dbo].[G_L Entry] REBUILD PARTITION = ALL

WITH (DATA_COMPRESSION = PAGE);

⚠️ Test in sandbox first. Compression can significantly reduce size and I/O.
4. Archive Historical G/L Entries

Use a custom archiving strategy to move old entries (e.g., > 5 years) to a separate table or database.

This reduces the working set size and improves performance.

5. Optimize Posting and Reporting

Ensure reports and posting routines use filtered queries and indexed fields.

Avoid using non-SARGable expressions (e.g., WHERE YEAR(Date) = 2023) in reports.

Additional Suggestions

Schedule index maintenance weekly or monthly via SQL Agent or BC Job Queue.

Monitor table growth using Table Information and telemetry.

Consider partitioning if the table continues to grow rapidly.

SQL Maintenance Script for G/L Entry Table (Table 17)
USE [YourDatabaseName]; -- Replace with your actual BC database name
GO

-- Step 1: Check fragmentation level
SELECT
    i.name AS IndexName,
    i.index_id,
    ps.avg_fragmentation_in_percent,
    ps.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo."G_L Entry"'), NULL, NULL, 'LIMITED') AS ps
INNER JOIN sys.indexes AS i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.avg_fragmentation_in_percent DESC;
GO

-- Step 2: Reorganize or Rebuild indexes based on fragmentation
-- Reorganize if fragmentation is between 10% and 30%
-- Rebuild if fragmentation is above 30%

-- Example: Rebuild all indexes on G/L Entry
ALTER INDEX ALL ON [dbo].[G_L Entry] REBUILD WITH (ONLINE = ON);
GO

-- Optional: Enable PAGE compression to reduce size
ALTER TABLE [dbo].[G_L Entry] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
GO

-- Step 3: Update statistics to improve query performance
UPDATE STATISTICS [dbo].[G_L Entry] WITH FULLSCAN;
GO

Best Practices

Run this script during off-peak hours to avoid performance impact.

Always test in sandbox first, especially compression.

Schedule this as a SQL Agent Job to run monthly or quarterly.

Monitor index size and fragmentation regularly via the Table Information page or telemetry.

Note: This response was created in collaboration with Microsoft Copilot to ensure clarity and completeness. I hope it helps to some extent.
Mark the Answer as Verified if this is Helpful.

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,331 Super User 2025 Season 2 on at

Like (0)

Report

Please note that this forum does not have Microsoft support staff (except for forum administrators). For special cases like yours, please submit a SR to Microsoft so that they can assist in investigating.

PS: Dynamics 365 Business Central: Data cleanup tasks page (Data Administration page)

https://yzhums.com/15246/

Thanks.

ZHU

Was this reply helpful? Yes No
Suggested answer

Teddy Herryanto (Th... 14,284 Super User 2025 Season 2 on at

Like (0)

Report

Since you are on SaaS, everything is handled by Microsoft side.

You can open a ticket with them and ask to investigate.

Was this reply helpful? Yes No