This article describes steps needed to optimize site performance around the heavy use of taxonomy and content list retrieval.
This article explains the issues and provides steps for resolving them.
1. Updating Ektron version.
2. Refactoring template code.
3. Tuning the database against TempDB contention.
Additional information for each step is below.
1. Updating Ektron version
Ektron understands that it may take time to upgrade due to other factors, but by moving to the latest version, you get the fixes documented in this article.
It is important to identify your Ektron version, because key improvements and fixes are only available with certain updates. Here is relevant fixes and updates history, taken from the "Site/Service Update Release Notes (CU's)," found here.
Issue #73219 - Database Performance Issue - 8.6.1
Issue #72746 - Performance issues with Framework api getlist - 8.7 sp2
Issue #73592 - ContentManager API SQL Performance Fixes - Merged from 73219 into 8.7sp2
Issue #73729 - cms_loadtaxonomy SQL performance fix added - 9.0sp2
To grab the latest Ektron base installer, click here.
2. Refactoring template code
A developer or development vendor needs to review the templates making the requests to see if they are using Ektron Legacy APIs or the latest Ektron.Cms.Framework APIs.
If the API on the template does not use the latest framework, it is not taking advantage of the fixes and optimizations. After code is refactored to use the newer framework, it is important to use caching to reduce the number of database calls. For a sample caching implementation, please click here.
If there is a need for a full code review, Ektron has a Professional Services Team, which can be reached through your Account Manager.
3. Tuning the Database against TempDB Contention
This information is not specific to Ektron. However, when included with the previous steps, it has proved useful to customers and provided here for convenience.
In issues with performance and taxonomy, it is common to see PAGELATCH_UP waits in the SQL tracing. Too many PAGELATCH_UP waits (due to calls made to the stored procedure cms_loadtaxonomy) can overload the tempDB and generate resource contentions. For more information on breaking down tempDB contention, click here.
Latches are internal SQL Server locks, which are very lightweight, short-term synchronization objects. Latches are not primarily for protecting pages being read from disk into memory. They are a synchronization object for any in-memory access to any portion of a log or data file. A Wait Type: PAGELATCH_UP occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode. This information can be found here.
Finally, please look into the article here.