How to control table hints

Erhan Cakirman asked on April 18, 2017 02:51


Is there any way to control(Enable/Disable) the Table hints (NOLOCK, NOEXPAND) used in Kentico Queries ? The below query doing 10K Logical Reads with using NOEXPAND on View_CMS_Tree_Joined but it only does 18 when not using NOEXPAND. I don't have any idea when this particular query is created and executed but it raises issues with the DBA's, also this is a Kentico Project upgrades from 8 to 9 and it seems the DB has no problems with the indexes on that view.

SELECT [NodeID], [NodeAliasPath], [NodeName], [NodeParentID], [NodeClassID], [NodeLevel], [NodeHasChildren], [NodeLinkedNodeID], [DocumentID], [DocumentName], [DocumentCulture], [DocumentModifiedWhen], [DocumentMenuRedirectUrl], [ClassName], [DocumentType], [DocumentIsArchived], [DocumentPublishedVersionHistoryID], [DocumentWorkflowStepID], [DocumentCheckedOutByUserID], [DocumentCheckedOutVersionHistoryID], [DocumentCanBePublished], [DocumentPublishFrom], [DocumentPublishTo], [DocumentIsWaitingForTranslation], [DocumentMenuRedirectToFirstChild], [NodeACLID], [NodeSiteID], [NodeOwner] FROM ( SELECT [NodeID], [NodeAliasPath], [NodeName], [NodeParentID], [NodeClassID], [NodeLevel], [NodeHasChildren], [NodeLinkedNodeID], [DocumentID], [DocumentName], [DocumentCulture], [DocumentModifiedWhen], [DocumentMenuRedirectUrl], [ClassName], [DocumentType], [DocumentIsArchived], [DocumentPublishedVersionHistoryID], [DocumentWorkflowStepID], [DocumentCheckedOutByUserID], [DocumentCheckedOutVersionHistoryID], [DocumentCanBePublished], [DocumentPublishFrom], [DocumentPublishTo], [DocumentIsWaitingForTranslation], [DocumentMenuRedirectToFirstChild], [NodeACLID], [NodeSiteID], [NodeOwner], ROW_NUMBER() OVER ( PARTITION BY NodeID ORDER BY CASE WHEN [DocumentCulture] = @DocumentCulture THEN 1 ELSE 2 END, DocumentCulture ) AS [CMS_C], ROW_NUMBER() OVER ( ORDER BY NodeAliasPath ) AS [CMS_ORN] FROM View_CMS_Tree_Joined AS V WITH ( NOLOCK, NOEXPAND ) LEFT OUTER JOIN COM_SKU AS S WITH ( NOLOCK ) ON V.NodeSKUID = S.SKUID WHERE [NodeSiteID] = @NodeSiteID AND [NodeAliasPath] = @NodeAliasPath ) AS SubData WHERE [CMS_C] = @CMS_C ORDER BY CMS_ORN;

Thank You

Recent Answers

Trevor Fayas answered on April 19, 2017 00:37

Well the views and such are editable through SQL commands / SQL management studio, but i would be VERY cautious modifying a view that is so widely used by the system as this. The No Expand hint helps it understand the best way to index. It may make a lot of logical reads, but it also may in the long run reduce the overall load.

I would suggest not touching it...

0 votesVote for this answer Mark as a Correct answer

Erhan Cakirman answered on April 19, 2017 02:15

Actually, before creating this I checked all the views/Sp's for that query but this is not a View or a Stored Procedure. The Sql logs also shows that this is a prepared statement, so I need to know if there is a way to control this table hints.


0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on April 19, 2017 16:50

You are correct Erhan, the query you posted above is NOT a regular view, all queries Kentico use for the API are dynamically generated. ANY query or API request to pages or page related items will always use the View_CMS_Tree_Joined view joined with at least 1 other table. If you look in your query you will see this view is in that query joined with COM_SKU table. So I'm guessing it is looking for product data. This would be the very basic query Kentico would run. As Trevor mentions, you'd have to modify any of the base views/queries with extreme caution but unfortunately in this case, you cannot modify this unless you have the source code of Kentico. Even then I'd question the benefit of what you're trying to achieve.

If you are concerned about it, I'd suggest contacting Kentico Support for more information about why it is used, what your concerns are and how you might overcome those concerns with some custom code, db modifications, etc.

0 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on April 19, 2017 17:08 (last edited on April 19, 2017 17:08)

Great point Brenden, just thought of one other thing.

IF you want to use this View, but have a better Index/Structure to it, you can always consider running a 'caching' task that copies a subset of this data into a cache table that you can then put your own indexes on to increase performance without having to worry about the things that affect it.

There's a nifty little trick that if you create the cache table in two schemas (dbo, and cache), you can build the cache into one (which may take a bit), then use a VERY quick "schema swap" to switch the dbo with the cache, and cache with the dbo.

It looks like this:

-- Trick learned online, build into a cache table, then swap the Cache and the regular table using schema swapping.
TRUNCATE TABLE cache.View_CMS_Tree_Joined_Cache;
-- (if you need to maintain FKs you may need to delete)
INSERT INTO cache.View_CMS_Tree_Joined_Cache Select * from View_CMS_Tree_joined with (nolock)

-- step 2:
-- this transaction will be almost instantaneous, 
-- since it is a metadata operation only: 
    ALTER SCHEMA hold  TRANSFER dbo.View_CMS_Tree_Joined_Cache;
    ALTER SCHEMA dbo   TRANSFER cache.View_CMS_Tree_Joined_Cache;
    ALTER SCHEMA cache TRANSFER hold.View_CMS_Tree_Joined_Cache;

Just make sure you apply your indexes to both the cache. and dbo. tables, as during the swap operation you are literally replacing one with the other, so you need indexes on both.

1 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.