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. Continue reading “Watch your indexes closely”