Tag: Index

Turn Off Indexes in Microsoft Dynamics 365 Business Central v28

In version 28 of Microsoft Dynamics 365 Business Central, Microsoft introduced a powerful database optimization feature that allows administrators to disable non-unique indexes with low usage.

This capability provides greater control over database performance, storage usage, and write operations, especially in environments with large datasets and high transaction volumes.

Why Index Optimization Matters

Indexes improve query performance, but every index also introduces maintenance overhead.

Whenever records are inserted, updated, or deleted, the SQL engine must also update all associated indexes.

In high-transaction systems such as inventory, warehouse, and manufacturing operations, unnecessary indexes can lead to:

  • Slower posting performance
  • Increased SQL storage consumption
  • Higher index maintenance cost
  • Longer backup and restore time

By allowing administrators to disable rarely used indexes, Business Central now provides more flexibility to tune database performance.

How to Disable an Index

To disable an index, follow these steps:

  1. Open Table Information in Business Central.
  2. Click on Table No
  3. It will open Table Data Management Card
  4. Clear the Enabled in Database checkbox.

Once disabled, the index is immediately removed from active maintenance, meaning the database engine no longer updates it during data modifications.

Article content

Index Types That Cannot Be Disabled

Article content

These indexes are essential for system functionality and consistency, so they cannot be turned off.

Activation Behavior

Index management behaves differently depending on the operation.

Article content

The delayed activation for enabling indexes prevents heavy index rebuild operations during working hours.

Technical Impact on Performance

1. Faster Write Operations

Each index must be updated when records change.Disabling unused indexes reduces the number of updates required, resulting in faster:

2. Reduced SQL Storage Consumption

Indexes can consume a significant portion of database storage. In large systems with millions of records, removing unnecessary indexes can free several gigabytes of space.

3. Better Performance During High Transactions

Environments with high transaction volumes, such as:

  • Manufacturing
  • Retail
  • E-commerce integrations
  • Warehouse management

can benefit from reduced index overhead during posting.

For example:

When posting thousands of item journal lines, fewer indexes mean less SQL maintenance work.

4. Improved SQL Maintenance Operations

Large numbers of indexes increase the workload for:

  • SQL statistics updates
  • Index rebuilds
  • Database maintenance jobs

By disabling unused indexes, administrators can reduce maintenance windows and resource consumption.

The ability to turn off non-unique indexes per company in Microsoft Dynamics 365 Business Central v28 is a valuable addition for system administrators and technical consultants.

It provides greater flexibility in optimizing database performance, storage usage, and transaction throughput, especially in large Business Central environments.

When used carefully with proper analysis, this feature can significantly improve system efficiency and operational performance.

FieldRef.IsOptimizedForTextSearch() Method in Business Central 2025 Wave 1

With the release of Business Central 2025 Wave 1, Microsoft continues to empower developers with more control and insights into the performance and behavior of their extensions. Among the new additions is the method FieldRef.IsOptimizedForTextSearch(), designed to help developers make more performance-conscious decisions when implementing search functionalities.

💡 What is FieldRef.IsOptimizedForTextSearch()?

FieldRef.IsOptimizedForTextSearch() is a method that returns a Boolean value indicating whether a particular field in a table is optimized for text search.

It is a method on the FieldRef data type, which is used in AL code to dynamically refer to fields of records, especially in scenarios involving field iteration, metadata handling, or dynamic filters.

✅ Syntax:

Boolean := FieldRef.IsOptimizedForTextSearch();

⚙️ How to Optimize a Field for Text Search

While IsOptimizedForTextSearch() only checks if a field is optimized, setting it up is done via the table metadata or through the table schema in AL.

To mark a field for text search:

field(10; Description; Text[100])
{
    Caption = 'Description';
    DataClassification = ToBeClassified;
    OptimizeForTextSearch = true;
}

Setting OptimizeForTextSearch = true; enables text search optimization (depending on SQL backend settings as well for on-premise).

Lets see how we can utlize above method to check optimize search

var
    MyRecordRef: RecordRef;
    MyFieldRef: FieldRef;
    IsOptimized: Boolean;
begin
    MyRecordRef.Open(Database::Customer);
    if MyRecordRef.FindSet() then begin
        MyFieldRef := MyRecordRef.Field(Name); // Let's check the "Name" field
        IsOptimized := MyFieldRef.IsOptimizedForTextSearch();

        if IsOptimized then
            Message('The "%1" field in the Customer table is optimized for text search.', MyFieldRef.Name())
        else
            Message('The "%1" field in the Customer table is NOT optimized for text search.', MyFieldRef.Name());
    end;
    MyRecordRef.Close();
end;

To be optimized for full-text search, a field typically needs:

  • A Text or Code data type.
  • An active index that supports full-text search (defined in the table metadata or via table extensions).
  • Proper settings in SQL Server or Azure SQL (if full-text search is enabled).

The FieldRef.IsOptimizedForTextSearch() method is a small but powerful tool in the AL developer’s toolkit. Whether you’re designing smarter search UIs or optimizing performance in large datasets, this method gives you the metadata visibility to make informed choices.

By leveraging this feature, you can:

  • Improve app performance
  • Avoid slow queries
  • Create better user experiences

Stay tuned for more..