Indexes on tables in the database

Topics: Administration, Core
Oct 7, 2014 at 2:58 PM
Why are there only clustered indexes on the tables and no non-clustered indexes to help with read speeds?
Oct 7, 2014 at 4:43 PM
Because we were waiting for your to help us make it better. Could you explain what needs to be changed and the impact?
Oct 8, 2014 at 9:04 AM
Well it's unusual to see all the tables in a database without any non-clustered indexes but Orchard may not require them (I would be surprised if there wasn't a performance gain to be had).

Basically the addition of non-clustered indexes can help the database engine to successfully optimise a wider range of different queries on the current tables and achieve quicker and more efficient data reads and writes. This does come at a cost but if planned well the gains far outweigh the costs.

I have an Orchard deployment in production so I will monitor usage over the next few days and try to come up with some indexing suggestions.
Oct 8, 2014 at 11:33 AM
I had some time to take a look today and only really found one table that would benefit from a non-clustered index (My deployment is very small with little amounts of data so if/when this increases additional index requirements may appear).

The table is: Orchard_Framework_ContentItemVersionRecord

After inspecting the query plan cache and various system DMVs / system tables I can see that queries (generated by Nhibernate) like the one below are running roughly 10 times each minute. This could be caused by some of the custom code we have running but from your blog post on 'Understanding Orchard's Database' I can see that queries like this could be quite common.

SELECT TOP (@p0) this_.Id as Id736_2_, this_.Number as Number736_2_,
this_.Published as Published736_2_, this_.Latest as Latest736_2_, this_.Data as Data736_2_,
this_.ContentItemRecord_id as ContentI6_736_2_, contentite1_.Id as Id735_0_,
contentite1_.Data as Data735_0_, contentite1_.ContentType_id as ContentT3_735_0_,
contenttyp4_.Id as Id737_1_, contenttyp4_.Name as Name737_1_

FROM Orchard_Framework_ContentItemVersionRecord this_
inner join Orchard_Framework_ContentItemRecord contentite1_ on this_.ContentItemRecord_id=contentite1_.Id
left outer join Orchard_Framework_ContentTypeRecord contenttyp4_ on contentite1_.ContentType_id=contenttyp4_.Id
WHERE contentite1_.Id = @p1 and this_.Published = @p2

The WHERE clause on 'published' and 'ContentItemRecordId' isn't really helped by the clustered index on the id column in the Orchard_Framework_ContentItemVersionRecord table.

I would recommend a covering non-clustered index that prioritises optimising that WHERE clause but also helps variations of the query above that may require data from the other columns of that table. The code below would create such an index:

CREATE INDEX [IDX_Published_ContentItemRecordId]
ON [CMS].[dbo].[Orchard_Framework_ContentItemVersionRecord] ([Published], [ContentItemRecord_id])
INCLUDE ([Id], [Number], [Latest], [Data])

This index would be susceptible to fragmentation as records are inserted and deleted so maintenance would be required (the costs I mentioned in my previous post) but the sysadmin/DBA who is responsible for the installation should be performing index maintenance alongside backups, fragmentation checks, etc.

If this is helpful information I will report any more that I find in the future.
Oct 8, 2014 at 5:05 PM
Records in this table are not deleted. Would that help for maintenance ?
Oct 8, 2014 at 5:58 PM
Yes deletes are probably the biggest problem when it comes to index fragmentation.

Updates on string fields that reduce the length cause some fragmentation but updates that extend the length of a variable length string field (varchar / Nvarchar) can potentially cause a lot of fragmentation as they may have to do nasty things like page splits because the data in the row no longer fits on one page in the data file.

If neither of these things are an issue on that table then you should be OK but it's easy to search for and fix fragmentation if it does occur. You can do this by using maintenance tasks in SQL Server but even they have issues.

Any decent size deployment is going to need some database maintenance to make sure the company is covered against outages and data loss so this would be done then. Smaller deployments in small teams with no DBA are likely to have much less data (in general) so fragmentation is going to occur less and be less of a problem.