I know that rebuilding database index is a polarizing topic. Anyway I want to share with you what I´ve recently found.
Over the last service updates the Microsoft Dynamics 365 for Finance and Operations database got some handy new stored procedures to e. g. rebuild indexes. You can use those stored procedures on Tier1 environments but also on Tier2+ environments e. g. bevor you start a load test run.
As an excurse: Normally Data Administration and Management Service (DAMS) should take care of your indexes on Tier2+ environment, but when you´re not trusting also this “Hokuspokus”, you could use the procedure on Tier2+ environments.
The stored procedure I´m referring to has the name AXPerf_IndexMaintenance and you can start it in SQL Server Management Studio via the following command.
DECLARE @return_value int
EXEC @return_value = [dbo].[AXPerf_IndexMaintenance]
@MAXDURATION = 10,
@TOPLIMITOFINDEXES = 100,
@ONLINEDEFRAG = 1,
@MAXDOP = 1,
@SCHEMA = N’dbo’
SELECT ‘Return Value’ = @return_value
GO
The parameters are most likely self-explanatory:
MAXDURATION = Max duration of the index maintenance in minutes
TOPLIMITOFINDEXES = How many index should be considered in the current maintenance run
ONLINEDEFRAG = 1 to run online defragmentation/rebuild
MAXDOP = Max degree of parallelism
SCHEMA = Schema to run the maintenance for
Comments
Post a Comment