Keep your AX2012 Modelstore performing

I had an interesting conversation with a very skilled and helpful Microsoft employee regarding maintenance of the Modelstore database. He sent me a SQL script prepared by the Microsoft Support Team.
First let's have a look at the proposed script, then let's look at why we would want to run it.

ALTER INDEX ALL ON [dbo].ModelElement REBUILD
ALTER INDEX ALL ON [dbo].ModelElementData REBUILD
ALTER INDEX ALL ON [dbo].Sources REBUILD
ALTER INDEX ALL ON [dbo].ModelElementLabel REBUILD
ALTER INDEX ALL ON [dbo].MODELSECPOLRUNTIMEEX REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityCommon REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityPermission REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityPolicy REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityPolicyConstraint REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityPolicyTable REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityRole REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityHierarchyCache REBUILD
ALTER INDEX ALL ON [dbo].ModelSecuritySubRole REBUILD
 
UPDATE STATISTICS [dbo].ModelElement WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelElementData WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].Sources WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelElementLabel WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].MODELSECPOLRUNTIMEEX WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityCommon WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityPermission WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityPolicy WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityPolicyConstraint WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityPolicyTable WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityRole WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityHierarchyCache WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecuritySubRole WITH FULLSCAN, COLUMNS

Before I created this post I did a quick test on one of my Modelstores and I gained a little bit on the compilation time, from 1h 53m to 1h 47m. That is just an improvement of 5 minute. This particular Modelstore only contained a vanilla R2 with DIEF, so it's not exactly a grand example.

We know the Modelstore of R2 is between 3,5 and 4 GB. The Modelstore database will easily be double the size. It will contain large indexes to support the tables. The image above shows some of the biggest tables with their indexes. We also know there is a hotfix released that makes a small adjustment to the schema reducing the compilation time by 20-40%.

Why bother to rebuild the indexes of the Modelstore and update the statistics? Well, obviously every time you fill it with a new awesome model, it creates new records in the Model-tables for each single element in that model. You probably uninstall less awesome models once in a while, leaving gaps in your indexes. Perfectly normal in a development environment. So running the script above once a week wouldn't hurt anyone - most likely just be an advantage.

The rebuild of the indexes will completely rebuild the index instead of just reorganizing the leafs. Since we want column statistics to be fully updated, we then run a new set of statements to make full scans and update any column statistics. Running this will make sure the model database is fit 4 fight. :-)