This is an unusual post – it is not about Commerce – my area of expertise, but about CMS. Recently I’ve been working on some support cases where SQL Server instance is on high utilization, and in some scenarios it eventually slows down the site. After investigation, it’s likely to come from a small, simple and helpful feature: Simple address.
CMS content can have a property named “Simple address”, which allows you to create a “shortcut” url for that content. So if you have one page with “name in url” as “contact-us” under a page name “about-us” under Home page, you can access it via https://mysupercoolsite.com/en/about-us/contact-us. Or you can set the Simple address for that page as “contact-us”, and then you can access it directly via https://mysupercoolsite.com/contact-us.
Now it’s an interesting part: To make the simple address concept work, Episerver has to call a special stored procedure (“netquicksearchbyexternalurl” if you wonder) to check if any published content has that simple address (“ExternalUrl” is the field internally). If a content is not found, a 404 error will be shown.
Sounds reasonable, right? But that small and handy feature can come with a drawback: performance!
What? Why? How?
The simple address is the first router to be registered by Episerver – so it will also be the first one to process all URLs (except for static files for example, of course). To see which URLs are being processed by Simple Address – let’s play a bit. Note that this is highly experimental so you should not try it on production (You’ll likely not to, but I feel obliged to say). Let’s add a table to track all requests to netquicksearchbyexternalurl
CREATE TABLE [dbo].[SimpleAddressRequest](
[RequestTime] [datetime2](7) NULL,
[RequestUrl] [nvarchar](260) NULL
) ON [PRIMARY]
And modify the stored procedure itself to insert to that table whenever it try to resolve an url:
ALTER PROCEDURE [dbo].[netQuickSearchByExternalUrl]
SET NOCOUNT ON
DECLARE @LoweredUrl NVARCHAR(255)
SET @LoweredUrl = Lower(@Url)
INSERT INTO dbo.SimpleAddressRequest VALUES(GETDATE(), @LoweredUrl)
Performance notes: The subquery "Pages" must not have any more predicates or return the values used in the outer WHERE-clause, otherwise
SQL Server falls back to a costly index scan. The performance hints LOOP on the joins are also required for the same reason, the resultset
from "Pages" is so small that a loop join is superior in performance to index scan/hash match, a factor 1000x.
tblLanguageBranch.LanguageID as LanguageBranch
) AS Pages
INNER LOOP JOIN
tblPage ON tblPage.pkID = Pages.fkPageID
INNER LOOP JOIN
tblPageLanguage ON tblPageLanguage.fkPageID=Pages.fkPageID AND tblPageLanguage.fkLanguageBranchID=Pages.fkLanguageBranchID
INNER LOOP JOIN
tblLanguageBranch ON tblLanguageBranch.pkID = Pages.fkLanguageBranchID
tblPageLanguage.StartPublish <= @CurrentTime AND
(tblPageLanguage.StopPublish IS NULL OR tblPageLanguage.StopPublish >= @CurrentTime)
That’s all. Now just try to visit the website. There are the requests SimpleAddress router handled when I just browse around Quicksilver:
You can see many of them are clearly not simple addresses – they are assets or catalog content. However, all of them will be queried against tblPageLanguage. The router for Simple Address has cache internally, but if you have enough requests, it’ll soon fill in the cache and trigger the cache trimming, therefore the view will be queried again. Even worse, if you have enough rows in tblPageLanguage, each query will take more time and resource – tblPageLanguage (and its underlying tblContentLanguage table) contains information for all CMS Content (Pages, assets, etc.) so it’s easy to have quite big size (The one I investigated has 3.7M rows). When you have a lot of queries which are not super fast to run, it’s only matter of time before it bring your SQL Server instance to its knees.
Is there a solution for this problem? Yes. If you don’t use Simple Address at all, just disable the router, and it will save you from queries after queries. This simple code in an InitializableModule will do that:
public void Initialize(InitializationEngine context)
Global.RoutesRegistered += Global_RoutesRegistered;
private void Global_RoutesRegistered(object sender, RouteRegistrationEventArgs e)
var simpleAddressRouter =
e.Routes.OfType<IContentRoute>().FirstOrDefault(r => r.Name.Equals("simpleaddress"));
if (simpleAddressRouter != null)
This will remove SimpleAddress from the registered routes, and it will trouble you no more.