Dynamics SQL Server Settings: Five Checks for NAV, AX and CRM

Dynamics SQL Server settings decide most of the speed you get from NAV, AX and CRM. You rarely control the application code, so the server is where the work happens. Five read-only checks cover the settings that matter, and each comes with a short query.

Why Dynamics SQL Server Settings Matter

Dynamics products run mostly short transactions against a schema you can’t change. You can’t rewrite their queries, and you shouldn’t add objects the vendor doesn’t expect. The advice below comes from about eleven health checks of Dynamics systems in one quarter. It covers five areas: tempdb, autogrowth, parallelism, statistics and index upkeep.

The Dynamics SQL Server settings checks need a demo database that stands in for yours. This script creates one. It puts the log file on percentage growth, so the growth check has something to find. It also adds a small table with an index.

IF DB_ID(N'DynamicsAuditDemo') IS NULL CREATE DATABASE DynamicsAuditDemo;
GO
USE DynamicsAuditDemo;
GO
ALTER DATABASE DynamicsAuditDemo MODIFY FILE (NAME = N'DynamicsAuditDemo_log', FILEGROWTH = 10%);
GO
DROP TABLE IF EXISTS dbo.SalesLine;
CREATE TABLE dbo.SalesLine (
    LineID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SalesLine PRIMARY KEY,
    ItemNo int NOT NULL,
    Qty    int NOT NULL
);
INSERT INTO dbo.SalesLine (ItemNo, Qty)
SELECT TOP (10000) n % 200 + 1, n % 9 + 1
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS x;
CREATE INDEX IX_SalesLine_Qty ON dbo.SalesLine (Qty);
UPDATE STATISTICS dbo.SalesLine WITH FULLSCAN;
UPDATE dbo.SalesLine SET Qty = Qty + 1 WHERE LineID <= 3000;

Check 1: TempDB

Temp tables live in tempdb, and sorts and hashes spill into it when they outgrow their memory. Put it on the fastest drive you have. Create one data file per CPU up to eight, and give all of them the same size. The query compares the file count with the CPU count and shows the size range.

SELECT COUNT(*) AS TempDataFiles,
       MIN(size) * 8 / 1024 AS SmallestMB,
       MAX(size) * 8 / 1024 AS LargestMB,
       (SELECT cpu_count FROM sys.dm_os_sys_info) AS LogicalCpus
FROM tempdb.sys.database_files
WHERE type = 0;

The target is one data file per CPU up to eight, all the same size. This test server has 16 CPUs and eight equal files. The sizes depend on the server’s history, so yours will differ. On SQL Server 2014 and earlier, trace flags 1117 and 1118 gave the equal growth. On 2016 and later they aren’t needed, as AUTOGROW_ALL_FILES: What Replaced Trace Flags 1117 and 1118 explains. For the other tempdb checks, read TempDB Performance: Five Settings to Check in SQL Server.

Check 2: Autogrowth

Percentage growth gives unpredictable steps. A file at 10 percent grows by a little when small and by gigabytes when large. Use a fixed size close to the file’s growth in a week. This query reads the files of the current database and flags percentage growth.

SELECT name, type_desc, size * 8 / 1024 AS SizeMB,
       CASE WHEN is_percent_growth = 1 THEN CONCAT(growth, N' percent') ELSE CONCAT(growth * 8 / 1024, N' MB') END AS Growth,
       CASE WHEN is_percent_growth = 1 THEN N'Use a fixed size' ELSE N'OK' END AS Verdict
FROM sys.database_files
ORDER BY file_id;

The log file is the one to fix. The post on Autogrowth Settings: Check Every Database File in SQL Server checks every file on a server at once.

Check 3: MAXDOP

Dynamics databases hold mostly small transactions. The guidance for them is MAXDOP 1, so a query never splits across CPUs. On SQL Server 2014 and earlier that meant the server setting. On SQL Server 2016 and later you can set it for one database. Other databases on the same server stay alone. The next statements read the setting, change it for the demo database and read it again.

SELECT name, value FROM sys.database_scoped_configurations WHERE name = N'MAXDOP';
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;
SELECT name, value FROM sys.database_scoped_configurations WHERE name = N'MAXDOP';

The first read shows 0, which lets SQL Server use every CPU. The second shows 1, which turns parallel plans off for that database. The undo is SET MAXDOP = 0. For the product’s own rules, read Max Degree of Parallelism for Microsoft Dynamics CRM.

Check 4: Automatic Statistics

SQL Server builds query plans from statistics. The two automatic options are on by default, and they should stay on for Dynamics. Some administrators switch them off to save work, and plans suffer for it. If a flag reads 0, ALTER DATABASE [YourDatabase] SET AUTO_UPDATE_STATISTICS ON; turns it back on. One query shows the flags.

SELECT name, is_auto_create_stats_on, is_auto_update_stats_on, is_auto_update_stats_async_on
FROM sys.databases
WHERE name = DB_NAME();

Check 5: Statistics and Index Upkeep

Automatic updates wait for a threshold. For a table of 10,000 rows that threshold is 2,500 changes. The rule takes the smaller of two numbers. One is 500 plus 20 percent of the rows. The other is the square root of 1,000 times the rows. A test table refreshed at exactly 2,500 changes. A table can drift a long way before anything refreshes. A scheduled job that updates statistics closes that gap. This query shows how much of each statistic has changed since its last update.

SELECT OBJECT_NAME(s.object_id) AS TableName, s.name AS StatName, sp.rows AS TableRows,
       sp.modification_counter AS RowsChanged,
       CONVERT(decimal(5,1), sp.modification_counter * 100.0 / NULLIF(sp.rows, 0)) AS PercentChanged,
       DATEDIFF(DAY, sp.last_updated, SYSDATETIME()) AS DaysOld
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY s.name;

The Qty statistic shows 30 percent changed. The primary key statistic shows none, because the update never touched the key. Update statistics weekly or more frequently. Rebuild indexes about once a month if possible.

Index upkeep has two more halves. Add the missing indexes with Missing Index Script: Read the Suggestions Before You Create. Drop the unused ones with Unused Index Script: Find Indexes That Only Cost You Writes. Dynamics adds its own indexes, so test a drop on a copy first.

What to Remember

You could argue that MAXDOP 1 slows the big reports. It can. A single report can carry OPTION (MAXDOP 4), which overrides the database setting for that query only. Everything else stays serial.

Read the five Dynamics SQL Server settings before you change any of them, and change one at a time. Run the cleanup when the demo is done.

USE master;
GO
IF DB_ID(N'DynamicsAuditDemo') IS NOT NULL
BEGIN
    ALTER DATABASE DynamicsAuditDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DynamicsAuditDemo;
END;

A Dynamics server is not slow by nature, it is slow by settings nobody read.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.