D365 index maintenance is essential for keeping the SQL database queries performing optimally. Data insertion, updates, and deletions cause table indexes to become fragmented over time. This article explores various index maintenance techniques and provides a brief history of how index maintenance has evolved within Dynamics 365 Finance & Supply Chain. Additionally, we’ll discuss how to perform index maintenance using direct SQL zero-downtime scripts for production environments and utilize other scripts for lower-tier environments.
Forms of D365 Index Maintenance
When discussing index maintenance, the three most common operations are updating statistics, reorganizing indexes, and rebuilding indexes.
Updating statistics:
Updating statistics ensures the query optimizer has the most recent data distribution information from table records. The SQL optimizer then uses this data to choose the best execution plan for fetching data from tables. The SQL command to update statistics on the table ‘SalesTable’ can be seen below.
UPDATE STATISTICS SALESTABLE ;There are many parameters that can be set when using the SQL command update statistics as seen on: UPDATE STATISTICS (Transact-SQL) – SQL Server | Microsoft Learn. The system samples 30% of the table by default to capture statistics from the data. This default is normally good and preferred if the table has many records. For smaller tables or when I need more accurate statistics, I will run the command with the option for FullScan so that it uses every record in the table for statistics. I recommend testing how long the command takes to complete before running in a live system. While the command is running, it may cause blocking for other queries against the table.
UPDATE STATISTICS SALESTABLE with FULLSCAN;Reorganizing indexes:
Reorganizing an index will defragment the index without fully rebuilding it. Best practice is to normally reorganize an index when fragmentation is between 5 to 30 percent. This process uses less resources than a rebuild and does not require locking. Sample SQL commands for reorganizing a full table or a single index can be seen below.
ALTER INDEX ALL ON SALESTABLE REORGANIZE;
ALTER INDEX I_124SalesIdx ON SALESTABLE REORGANIZE;Rebuilding indexes:
This process completely defragments an index by recreating the index. This is more resource-intensive than a reorganize but provides better optimization of the data. Depending on the version of SQL, this may lock the table when rebuilding the index. With any D365 index maintenance, we recommend running these processes on off hours to avoid interfering with other SQL queries. A rebuild or reorganize also runs the update statistics functionality as part of the process. Sample SQL commands for reorganizing a full table or a single index can be seen below.
ALTER INDEX ALL ON SALESTABLE REBUILD
ALTER INDEX I_124SalesIdx ON SALESTABLE REBUILDHistory of D365 index maintenance
Anyone who has worked with a previous on-premise version (such as AX 2012 or Dynamics AX) knows that SQL performance slows down over time if you don’t create and run regular index maintenance scripts periodically. Around version PU 28 of Microsoft Dynamics 365 F&SC, Microsoft introduced a batch job ‘Database index rebuild system job that needs to regularly run at off hours’. This job would run nightly and create a log of what indexes had maintenance performed on them.
Eventually, this batch job started to cause errors when running simultaneously with other code. Thus, Microsoft deprecated this job and replaced it with a new automated system. Microsoft refers to this automated system as DAMS (Data Administration and Management Service). This tool will identify slow-running queries and run index maintenance on them, as well as force execution plans, resolve minor blocking, and, at times, even create new indexes. My other article, D365 Index Creation – My D365 Journey, goes over how to identify indexes created from this process.
Manual intervention of D365 index maintenance
Microsoft continually improves its automated index maintenance toolset. However, I have encountered a few scenarios where manual intervention is still necessary. I will cover how we can run D365 index maintenance on both a production system as well as sandbox and development environments.
Production environments:
I have identified a few instances where updating statistics on the tables within a query will help SQL choose a better execution plan. For production environments, we can create a zero-downtime runnable job that includes direct SQL commands within the job. Peter Ramer already has a great article on how to create a zero-downtime script How To Run A D365 Job In Production – Dynamics 365 Musings, so I will focus on just the code within the runnable job. The X++ code below will update statistics on two separate tables.
internal final class runIndexMaintenanceOnWaveLabel
{
public static void main(Args _args)
{
UserConnection userConnection;
Statement statement;
str sql;
SQLSystem system;
SQLStatementExecutePermission permission;
try
{
system = new SqlSystem();
sql = 'UDATE STATISTICS SALESTABLE; UPDATE STATISTICS SALESLINE;'
userConnection = new UserConnection();
statement = userConnection.createStatement();
new SqlStatementExecutePermission(sql).assert();
statement.executeUpdate(sql);
CodeAccessPermission::revertAssert();
Info("Completed.");
}
catch
{
Global::exceptionTextFallThrough();
}
}
}
Whenever creating direct SQL statements through X++ code, make sure first to verify the SQL syntax is correct in SQL Server Management Studio. Also, test your code in a non-production environment. Line 15 contains the SQL code that will run directly on the database. Notice that you can combine multiple SQL statements using proper syntax (semicolons between statements in this case) to simplify your code. Line 22 must use the executeUpdate method since this command doesn’t return any data. The alternative executeQuery method can be used for SQL select statements where data is returned. You can see an example of that syntax on Microsoft’s Learn page: SqlStatementExecutePermission Class (Dynamics.AX.Application) | Microsoft Learn. The other lines of code set up the SQL statement object and permissions required.
Below are some other use cases for direct SQL runnable jobs. You can replace the X++ for line 15 to either create an index directly in production (bypassing the wait time for a code promotion) or clear out a large table where the data is no longer needed.
sql = 'create index customFieldIdx on SalesTable (Partition, DataAreaID, CustomField);'
sql = 'Truncate Table EventCUD;'Non-production environments.
D365 index maintenance can be helpful in non-production environments, especially prior to going live on a new implementation of Dynamics 365 F&SC. Running maintenance before a system test can help ensure the system test goes more smoothly. It is also useful to run maintenance after large imports of data, such as converting/importing data from a previous system.
For non-production environments, since we have access to SQL either directly in the development environment or through just-in-time access on sandbox environments, we can run the commands ourselves. Running each command manually can be very time-consuming, so people often use a script instead. You can find scripts online, and they often include various useful features. One of my favorite scripts was created by a Microsoft employee and can be found at: How to maintain Azure SQL Indexes and Statistics | Microsoft Community Hub. I like this script because it creates a stored procedure you can call with a parameterized command. It also stores what actions it has performed in a database log table. Most maintenance scripts will check the fragmentation level of an index before deciding which process best optimizes the index.
Conclusion
D365 index maintenance is important for keeping your D365 F&SC environment running at peak performance. In most scenarios, Microsoft’s automated tools will handle all needs, and we should rarely have to intervene. In those rare cases, you can consider using the above methods as an option to resolve D365 SQL maintenance issues on your own.
