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. :-)
