Try our conversational search powered by Generative AI!

Spikes in SQL CPU usage after upgrading to Commerce 9.24.1



we're experiencing some issues where we regurarly see spikes in CPU usage (above 90%) at relatively low traffic. We would probably see around a stable 10-20% before the upgrade.

As a result of this, we're at times unable to publish content as the "Publish"-action times out with the message:

Something went wrong

Execution Timeout Expired. The timeout period elapses prior to completion of the opration or the server is not responding. The wait operation timed out.

I can't really see any events related to the CPU spikes.

Would anyone be able to provide some advice in troubleshooting this issue?

Mar 08, 2021 11:17

Were you able to use the Query Store to identify which queries are the most expensive?

Mar 08, 2021 11:26

We're on SQL Server 2012 - I'm not sure that Query Store is available

Mar 08, 2021 11:34

By running

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_elapsed_time/1000 total_elapsed_time_in_MS,
qs.last_elapsed_time/1000 last_elapsed_time_in_MS,
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

I see that the following query comes up first (

SET @Id = (SELECT CatalogEntryId FROM [CatalogEntry] WHERE ContentGuid = @ContentGuid)

And it's execution plan advices me to create the following index

Missing Index Details from ExecutionPlan6.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 99.9531%.

USE [CommerceDB]
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[CatalogEntry] ([ContentGuid])


Would a next step be to create this index? I'm guessing that if the index was necessary in the first place, it would have been created as part of the migration.

Mar 08, 2021 12:06

Yes we added that index in 11.1.0, which is why you are highly recommended to upgrade. 

Mar 08, 2021 12:25

We just made the jump to 9, with plans to upgrade all the way up to 13.

As we need to solve this issue now - I should probably just create the index according to the query in my last post? I'm guessing that it would take the table offline for a while?

Mar 08, 2021 12:30

Yes you can do that. but should note about it (in rare cases it might cause problem with upgrading to 11.1+ version because of duplicated indexes, you will then have to drop it )

Mar 08, 2021 12:58

Adding the mentioned index seems to have helped! Thanks!

Mar 11, 2021 11:56
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.