Hi,

We have two sites running under the same umbraco installation. The sites are hosted on azure and running properly for the last 2 years. For the last one month, we often found a DB query is taking very long time to execute and essentially blocked other DB operations. The Database DTU becomes 100%. The sites were down twice in the last one month for around 5-10 minutes.

Following is the query that blocking DB:

(@0 nvarchar(40),@1 int,@2 int)SELECT cmsPropertyData.* FROM cmsPropertyData INNER JOIN cmsPropertyType ON cmsPropertyData.propertytypeid = cmsPropertyType.id INNER JOIN (SELECT cmsContent.nodeId, cmsContentVersion.VersionId FROM [cmsDocument] INNER JOIN [cmsContentVersion] ON [cmsDocument].[versionId] = [cmsContentVersion].[VersionId] INNER JOIN [cmsContent] ON [cmsContentVersion].[ContentId] = [cmsContent].[nodeId] INNER JOIN [umbracoNode] ON [cmsContent].[nodeId] = [umbracoNode].[id] LEFT OUTER JOIN [cmsDocument] [cmsDocument2] ON ([cmsDocument2].[nodeId]=[cmsDocument].[nodeId] AND [cmsDocument2].[published]=1) WHERE ([umbracoNode].[nodeObjectType] = @0) AND ([umbracoNode].[parentID] = @1) AND ([cmsDocument].[newest] = @2) ) as docData ON cmsPropertyData.versionId = docData.VersionId AND cmsPropertyData.contentNodeId = docData.nodeId LEFT OUTER JOIN cmsDataTypePreValues ON cmsPropertyType.dataTypeId = cmsDataTypePreValues.datatypeNodeId

The site is using Umbraco 7.3.7, and the database is running at Standard tier S1:20 DTUs (Max data size: 2GB, 56% used).

It would be great if anyone could suggest any workaround to resolve the problem.