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.



Vincent · May 24, 2017 at 9:01 am

Thanks Quan. It’s very good writing and well explained. Love your passion.

Daniel Ovaska · May 28, 2017 at 12:26 pm

It’s amazing what a little SQL profiling can do to help performance. Key is to find out exactly what calls are slow and optimize those. SQL profiler usually has really good suggestions what indexes and statistics you need to add. It helped speed up one of my large solutions with a factor 50 in just a few hours of work. Nice article!

Maintaining your indexes - Quan Mai's blog · August 18, 2017 at 9:51 am

[…] Indexes are crucial to SQL Server performance. Having the right indexes might make the difference of day and night with your application performance – as I once talked here. […]

Leave a Reply

Your email address will not be published. Required fields are marked *