What function is making this SQL call?

Vote:
 

Hi,

We're running Episerver in Azure and our SQL is on tier P1. Intermittently our DTU maxes out. What I've found so far is that a specific query is taking a lot of CPU when the DTU is very high, but I don't know which Episerver API/method is generating this query. Is there a way to find out?

The query has a parameter called @pages, so my thinking was that it's related to IContentLoader.Get(IEnumerable), i.e. getting content from a list of content references.

But why is the tblContentSoftLink in the query? That leads me to think that this might be Find, when related content needs to be re-indexed, e.g. a block is updated and now all pages that uses that block needs to be re-indexed.

The query:

	SELECT
		tblPageLanguage.fkLanguageBranchID AS OwnerLanguageID,
		NULL AS ReferencedLanguageID,
		tblPageLanguage.fkPageID AS OwnerID, 
		tblPageLanguage.Name As OwnerName,
		PageLink As ReferencedID,
		tpl.Name AS ReferencedName,
		0 AS ReferenceType
	FROM 
		tblProperty 
	INNER JOIN 
		tblPage ON tblProperty.fkPageID=tblPage.pkID
	INNER JOIN 
		tblPageLanguage ON tblPageLanguage.fkPageID=tblPage.pkID
	INNER JOIN
		tblPage AS tp ON PageLink=tp.pkID
	INNER JOIN
		tblPageLanguage AS tpl ON tpl.fkPageID=tp.pkID
	WHERE 
		(tblProperty.fkPageID NOT IN (SELECT pkID FROM @pages)) AND
		(PageLink IN (SELECT pkID FROM @pages)) AND
		tblPage.Deleted=0 AND
		tblPageLanguage.fkLanguageBranchID=tblProperty.fkLanguageBranchID AND
		tpl.fkLanguageBranchID=tp.fkMasterLanguageBranchID
	
	UNION
	
	SELECT
		tblPageLanguage.fkLanguageBranchID AS OwnerLanguageID,
		NULL AS ReferencedLanguageID,    
		tblPageLanguage.fkPageID AS OwnerID,
		tblPageLanguage.Name As OwnerName,
		tp.pkID AS ReferencedID,
		tpl.Name AS ReferencedName,
		1 AS ReferenceType
	FROM
		tblPageLanguage
	INNER JOIN
		tblPage ON tblPage.pkID=tblPageLanguage.fkPageID
	INNER JOIN
		tblPage AS tp ON tblPageLanguage.PageLinkGUID = tp.PageGUID
	INNER JOIN
		tblPageLanguage AS tpl ON tpl.fkPageID=tp.pkID
	WHERE
		(tblPageLanguage.fkPageID NOT IN (SELECT pkID FROM @pages)) AND
		(tblPageLanguage.PageLinkGUID IN (SELECT PageGUID FROM @pages)) AND
		tblPage.Deleted=0 AND
		tpl.fkLanguageBranchID=tp.fkMasterLanguageBranchID
	
	UNION
	
	SELECT
		tblContentSoftlink.OwnerLanguageID AS OwnerLanguageID,
		tblContentSoftlink.ReferencedLanguageID AS ReferencedLanguageID,
		PLinkFrom.pkID AS OwnerID,
		PLinkFromLang.Name  As OwnerName,
		PLinkTo.pkID AS ReferencedID,
		PLinkToLang.Name AS ReferencedName,
		1 AS ReferenceType
	FROM
		tblContentSoftlink
	INNER JOIN
		tblPage AS PLinkFrom ON PLinkFrom.pkID=tblContentSoftlink.fkOwnerContentID
	INNER JOIN
		tblPageLanguage AS PLinkFromLang ON PLinkFromLang.fkPageID=PLinkFrom.pkID
	INNER JOIN
		tblPage AS PLinkTo ON PLinkTo.PageGUID=tblContentSoftlink.fkReferencedContentGUID
	INNER JOIN
		tblPageLanguage AS PLinkToLang ON PLinkToLang.fkPageID=PLinkTo.pkID
	WHERE
		(PLinkFrom.pkID NOT IN (SELECT pkID FROM @pages)) AND
		(PLinkTo.pkID IN (SELECT pkID FROM @pages)) AND
		PLinkFrom.Deleted=0 AND
		PLinkFromLang.fkLanguageBranchID=PLinkFrom.fkMasterLanguageBranchID AND
		PLinkToLang.fkLanguageBranchID=PLinkTo.fkMasterLanguageBranchID
		
	UNION
	
	SELECT
		tblPageLanguage.fkLanguageBranchID AS OwnerLanguageID,
		NULL AS ReferencedLanguageID,
		tblPage.pkID AS OwnerID,
		tblPageLanguage.Name  As OwnerName,
		tp.pkID AS ReferencedID,
		tpl.Name AS ReferencedName,
		2 AS ReferenceType
	FROM
		tblPage
	INNER JOIN 
		tblPageLanguage ON tblPageLanguage.fkPageID=tblPage.pkID
	INNER JOIN
		tblPage AS tp ON tblPage.ArchivePageGUID=tp.PageGUID
	INNER JOIN
		tblPageLanguage AS tpl ON tpl.fkPageID=tp.pkID
	WHERE
		(tblPage.pkID NOT IN (SELECT pkID FROM @pages)) AND
		(tblPage.ArchivePageGUID IN (SELECT PageGUID FROM @pages)) AND
		tblPage.Deleted=0 AND
		tpl.fkLanguageBranchID=tp.fkMasterLanguageBranchID AND
		tblPageLanguage.fkLanguageBranchID=tblPage.fkMasterLanguageBranchID
	UNION
	
	SELECT 
		tblPageLanguage.fkLanguageBranchID AS OwnerLanguageID,
		NULL AS ReferencedLanguageID,
		tblPage.pkID AS OwnerID, 
		tblPageLanguage.Name  As OwnerName,
		tblPageTypeDefault.fkArchivePageID AS ReferencedID,
		tblPageType.Name AS ReferencedName,
		3 AS ReferenceType
	FROM 
		tblPageTypeDefault
	INNER JOIN
	   tblPageType ON tblPageTypeDefault.fkPageTypeID=tblPageType.pkID
	INNER JOIN
		tblPage ON tblPageTypeDefault.fkArchivePageID=tblPage.pkID
	INNER JOIN 
		tblPageLanguage ON tblPageLanguage.fkPageID=tblPage.pkID
	WHERE 
		tblPageTypeDefault.fkArchivePageID IN (SELECT pkID FROM @pages) AND
		tblPageLanguage.fkLanguageBranchID=tblPage.fkMasterLanguageBranchID
	ORDER BY
	   ReferenceType
#178339
May 09, 2017 15:03
Vote:
 

Related to this query there is a recommendation that this index should be created

CREATE NONCLUSTERED INDEX [nci_wi_tblWorkContent_3209EB2DC6A8F46F07D65456C79E00E1] ON [dbo].[tblWorkContent] ([ChangedByName], [fkLanguageBranchID], [Status]) INCLUDE ([CommonDraft], [DelayPublishUntil], [fkContentID], [fkMasterVersionID], [Name], [NewStatusByName], [Saved]) WITH (ONLINE = ON)
#178340
May 09, 2017 15:05
Vote:
 

The SQL text look like the stored procedure "editDeletePageCheckInternal". This stored procedure get called when you put contents into the trash to verify the active references to the contents being deleted. This is quite normal if Editors are cleaning up contents on the site and doing too many delete operations so I guess you can just placed an index there to improve its performance.

#197251
Sep 27, 2018 5:33
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.