Additional indexes help optimize DNN performance

When dealing with the slow performance of a DNN site I quickly analyzed it in the test environment with the ANTS Performance Profiler and understood that most of the time the system spent inside the DB querying some data.

The analysis moved then to SQL Performance Tuning tools. The Activity Monitor in the Server Management Studio comes in very handy. With enough server roles you can get to this screen that shows you the "Recent Expensive Queries" window where you can see the top queries and their execution plans.

I added just 2 indexes:

• An index on ModuleId, IsPublished and LastModifiedOnDate for the HtmlText table
• An index on ModuleId and PortalId on the Modules table

As a result the average execution time on the 2 top queries with hundreds of executions went down from 3ms to 0ms with logical reads greatly reduces.

I wonder why these indexes do not come right out of the box.