How to control table hints

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...

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.

Thanks

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.

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: 
BEGIN TRANSACTION;
    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;
COMMIT TRANSACTION;

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.