Improve SQL query performance by adding missing SQL indexes

[nzdev]

Which exact Umbraco version are you using? For example: 9.0.1 - don't just write v9

9.4.3

Bug summary

There are a few missing SQL indexes for queries used by Umbraco.

Specifics

Missing Indexes

CREATE NONCLUSTERED INDEX [IX_umbracoContentVersion_NodeIdV2] ON [dbo].[umbracoContentVersion]
(
	[nodeId] ASC,
	[current] ASC
)
INCLUDE([VersionDate],[preventCleanup],[text],[userid])
GO


CREATE NONCLUSTERED INDEX [IX_umbracoContentVersionCultureVariation] ON [dbo].[umbracoContentVersionCultureVariation]
(
	[versionId] ASC
)
INCLUDE([id],[languageId],[name],[date],[availableUserId])

GO
CREATE NONCLUSTERED INDEX [IX_umbracoRedirectUrl_culture_hash] ON [dbo].[umbracoRedirectUrl]
(
	[createDateUtc] DESC
)
INCLUDE([culture],[url],[urlHash],[contentKey])
GO

CREATE NONCLUSTERED INDEX [IX_umbracoNode_ObjectType_trashed_sorted] ON [dbo].[umbracoNode]
(
	[nodeObjectType] ASC,
	[trashed] ASC,
	[sortOrder] ASC,
	[id] ASC
)
INCLUDE([uniqueID],[parentID],[level],[path],[nodeUser],[text],[createDate])

Steps to reproduce

Execute SQL queries for the tables listed in the SQL queries

Expected result / actual result

Appropriate SQL indexes to cover Umbraco queries.

[github-actions[bot]]

Hi @nzdev,

We're writing to let you know that we would love some help with this issue. We feel that this issue is ideal to flag for a community member to work on it. Once flagged here, folk looking for issues to work on will know to look at yours. Of course, please feel free work on this yourself ;-). If there are any changes to this status, we'll be sure to let you know.

For more information about issues and states, have a look at this blog post.

Thanks muchly, from your friendly Umbraco GitHub bot :-)

[LegateJD]

Started working on this.

[nzdev]

More missing indexes:

CREATE UNIQUE NONCLUSTERED INDEX [IX_cmsContentType_V2] ON [dbo].[cmsContentType]
(
	[nodeId] ASC
)
INCLUDE([alias],[icon],[thumbnail],[description],[isContainer],[allowAtRoot],[variations],[isElement])

CREATE NONCLUSTERED INDEX [IX_cmsTagRelationship_tagId_nodeId] ON [dbo].[cmsTagRelationship]
(
	[tagId] ASC,
	[nodeId] ASC
)
INCLUDE([propertyTypeId])

CREATE NONCLUSTERED INDEX [IX_cmsTags_languageId_group] ON [dbo].[cmsTags]
(
	[languageId] ASC,
	[group] ASC
)
INCLUDE([id],[tag])

CREATE NONCLUSTERED INDEX [IX_umbracoDocumentVersion_id_published] ON [dbo].[umbracoDocumentVersion]
(
	[id] ASC,
	[published] ASC
)
INCLUDE([templateId])

CREATE NONCLUSTERED INDEX [IX_umbracoDocumentVersion_published] ON [dbo].[umbracoDocumentVersion]
(
	[published] ASC
)
INCLUDE([id],[templateId])

CREATE NONCLUSTERED INDEX [IX_umbracoLog_datestamp] ON [dbo].[umbracoLog]
(
	[Datestamp] ASC,
	[userId] ASC,
	[NodeId] ASC
)

CREATE NONCLUSTERED INDEX [IX_umbracoLog_datestamp_logheader] ON [dbo].[umbracoLog]
(
	[Datestamp] ASC,
	[logHeader] ASC
)

This index would benefit from adding all the columns after migrating the ntext column to nvarchar(max)

CREATE UNIQUE NONCLUSTERED INDEX [IX_umbracoPropertyData_VersionId_V2] ON [dbo].[umbracoPropertyData]
(
	[versionId] ASC,
	[propertytypeid] ASC,
	[languageId] ASC,
	[segment] ASC
)
INCLUDE([varcharValue])

CREATE NONCLUSTERED INDEX [IX_umbracoNode_parentId_nodeObjectType] ON [dbo].[umbracoNode]
(
	[parentID] ASC,
	[nodeObjectType] ASC
)
INCLUDE([trashed],[nodeUser],[level],[path],[sortOrder],[uniqueID],[text],[createDate])

This index would be better used if nucacherepository did not try load the ntext column if the messagepack serializer was set as then it can better use the index.

CREATE NONCLUSTERED INDEX [IX_cmsContentNu_published] ON [dbo].[cmsContentNu]
(
	[published] ASC,
	[nodeId] ASC,
	[rv] ASC
)
INCLUDE([dataRaw])

[nzdev]

The CMS would benefit from further indexes by migrating all ntext columns to be nvarchar(max) as ntext columns cannot be part of an index.

[LegateJD]

@nzdev , CMS already has [IX_cmsContentType_icon] index.

[nzdev]

Removed from the list. The rest are correct.

[LegateJD]

Which max length should be used for changed ntext columns?

[nzdev]

nvarchar(max)

[JasonElkin]

Closing this, as #12756 is now merged.