Microsoft presents: Index management

Overview

The session from BC TechDays shows a new index management page where indexes on a table can be viewed, disabled immediately and enabled on demand. In SaaS, enabling happens overnight in local time; in Docker and on-premises it is immediate. Primary keys, system ID, SIFT and unique indexes cannot be disabled. Indexes can be defined with enable false in AL and enabled per company for company tables, but the method to enable them in setup code is currently on-premises scope only.

The talk also covers usage statistics (seek, scan, lookup, update counts since the last database restart), per-index storage, fragmentation, and the missing indexes advisor. It explains DAMS, which rebuilds indexes above about 25% fragmentation, updates statistics and runs the Database Tuning Advisor, and notes about 40,000 databases have DAMS-created indexes. It closes with extension fields merging into base tables in version 29 (early access preview, no shipping date promised), a version 28 record length warning, open questions on maintenance windows, and plans for SIFT management and wider API scope.

Key points

• Disabled indexes take effect immediately; enabling is done overnight in SaaS (local time) but immediately in Docker and on-premises.
• Primary keys, system ID, SIFT and unique indexes cannot be disabled. Non-AL-defined indexes that are disabled are deleted and cannot be re-enabled, though SQL Server-generated ones can be recreated.
• SIFT indexes cannot be managed in the first version; full SIFT management is planned later. Include columns on a well-defined index can often replace a SIFT index.
• Usage statistics (seek, scan, lookup, update, last used) reset on database restart, so they are more meaningful after long uptime; exporting to Excel on a schedule is the suggested way to keep history. They only cover existing indexes; the missing indexes page shows suggestions.
• Indexes can be defined with enabled false in AL and enabled per company at runtime for company tables. Use cases are just-in-time enabling for yearly closing and disabling indexes on backup companies to cut storage and write cost. The method to enable from code is currently scoped OnPrem only, with plans to open it up.
• DAMS rebuilds large indexes with fragmentation above about 25% nightly at low priority, updates statistics, and runs the Database Tuning Advisor, creating _DTA-prefixed indexes. Backup companies can slow the nightly fragmentation checks.
• Version 29 early access preview merges extension fields into base tables, allowing keys on extension fields and cutting inserts from two SQL inserts to one. Upgrade may take longer and no shipping date is promised.

Features

AL objects mentioned

As heard in the captions. A name that matches one object page by exact type and name links to it; the others stay as named.

• table 32 “Item Ledger Entry” at 1:07
• page 8700 “Table Information” at 2:04
• table “GL Entry” at 7:47
• table “Change Log” at 11:19
• table “DAMS re-index stats” at 25:34
• table 405 “Change Log Entry” at 28:03
• table 5802 “Value Entry” at 28:03
• table “Dams re-index stats table” at 27:09
• page “indexes list part page” at 33:41

Not found in BC28-30: table “GL Entry”, table “Change Log”, table “DAMS re-index stats”, table “Dams re-index stats table”, page “indexes list part page”.

Quotes

• 0:43 “indexes, you know, I think most of you probably know that keys get mapped down to indexes in SQL. But yeah, it was a”
• 1:50 “We have sift indices for example and the reason why I don’t talk about them here is because in the first version of this”
• 5:19 “You can disable them immediately, as you saw. You can enable them immediately in Docker locally, on prem. Uh but in SaaS, we’ll be”
• 6:28 “primary keys, system ID, sift, and unique indexes cannot be disabled. It would be a semantic like well, semantic in a different meaning, but”
• 12:04 “And then we have what is that? Three times as much just for the indices, right?”

Disclaimers in the video

• 1:50 not-in-this-release: sift indices for example and the reason why I don’t talk about them here is because in the first version …
• 3:19 subject-to-change: non AL defined index or it will be called something different maybe when you see it
• 4:52 subject-to-change: when you enable an index, it doesn’t happen immediately. It happens overnight, except if you are on my magical machine here, which is a local development
• 16:43 coming-later: will you be covering what we plan to add later or Ah. No, no, no. Maybe a little later
• 20:18 subject-to-change: Right now, unfortunately, the method is on prem only, scope on prem. But that is something we are thinking very much about changing
• 20:28 preview: we’re a little chicken. Because we’ll we just want to make to experiment with it in our own app first …
• 29:39 preview: if you take the early access preview build now, this is possible. No, actually this is not possible yet because …
• 30:02 preview: we actually have it out in the early access preview now. We’re merging back the extension fields into the base table
• 30:02 subject-to-change: if all the testing goes well, this is without I’m not promising an exact shipping date
• 31:50 preview: if it comes in version 29, this feature might take longer than other majors because we have to do that again
• 32:13 preview: When so, this is available in early access preview, we’ll also be spinning preview clusters later when we get closer to the release
• 38:33 coming-later: the full management of sift indexes we plan to add that to the index management later
• 39:47 coming-later: the plan is of course to enable some of these moving forward

Presenters (as heard): Mads Gram, Preetham, Jens, Giulio, Jesper, Stefan.