Watch your indexes closely

Recently we were tasked to help a customer having a problem with a query. This specific query ate a lot of CPU resources (30-40%) and causing performance problem for other queries – as it slows the entire SQL Server instance down.

Upon investigation, we discovered that the query was accessing a table with an outdated index. The index was supposedly updated in Episerver Commerce 7.10.3, which was released almost 3 years ago.

For some reasons, the index was not updated in customer’s table. Instead of just having to do a index seek, SQL Server was forced to do a full table scan, which is much slower, causing the problem.

If you want to go into details, it’s mdpsp_getchildrenbysegment stored procedure, which looks into UriSegment column of CatalogItemSeo table, previously, the index was like this:

CREATE NONCLUSTERED INDEX [IX_CatalogItemSeo_UniqueSegment_CatalogEntry] ON [dbo].[CatalogItemSeo]
(
    [ApplicationId] ASC
    [UriSegment] ASC,
    [CatalogEntryId] ASC
)

You can see the problem: The order of the index was bad – because ApplicationId was not distinctive (in fact, in most of the cases it’s the same for every row), and because UriSegment was not the first column in the index, this index will not be used if a query uses UriSegment only.

The index was then updated (in Commerce 7.10.3) to be like this:

CREATE NONCLUSTERED INDEX [IX_CatalogItemSeo_UniqueSegment_CatalogEntry] ON [dbo].[CatalogItemSeo]
(
    [UriSegment] ASC,
    [CatalogEntryId] ASC,
	[ApplicationId] ASC
)

Because the index in customer’s database was outdated, the solution is simple enough: to drop the current (outdated) and recreate with the correct order. This solved the problem entirely: Query time went down from average 80ms, to 3-4ms, CPU usage went down from 30-40%, to almost 0%.

The customer is happy. Who would not be happy with this:

Morals of the story:

• When you add an index to a table, make sure to have the most distinctive columns first. And make sure it benefits the most frequent query to that table. Note that SQL Server execution plan can suggest some missing indexes, but you should take into account how much that specific query is gonna called, and how big is its impact. If a specific query A can be 3 times faster with an index, while query B can only be 2 times faster with another index, but query B is called 10 times more frequent than query A, then the latter index should be preferred. In ideal scenario, you can add both indexes, but having too many indexes can be a problem of itself!
• Watch your profiling regularly. If a query is called multiple times, or runs slower than expected, it’s time to raise question.
• Make sure that your database schema, including indexes, and stored procedures, are properly updated. Episerver validators can only validate if your database version is matched with your assemblies version, but it can’t verify if your indexes are updated or not. One simple way to do that is to install a new, empty site of Episerver Commerce, and compare that database to yours, and check if their schema match.
• And as always – update to latest version if possible. We at Episerver continuously incorporate new improvements from internal and external findings, to make even better performance. You will get performance bump for, well, almost free.