Spikes in SQL CPU usage after upgrading to Commerce 9.24.1

Henrik S

Vote:

Hi,

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?

#249718

Mar 08, 2021 11:17

Quan Mai

Vote:

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

#249719

Mar 08, 2021 11:26

Henrik S

Vote:

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

#249721

Mar 08, 2021 11:34

Henrik S

Vote:

By running

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000 total_elapsed_time_in_MS,
qs.last_elapsed_time/1000 last_elapsed_time_in_MS,
qs.last_execution_time,
qp.query_plan
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]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[CatalogEntry] ([ContentGuid])

GO
*/

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.

#249722

Mar 08, 2021 12:06

Quan Mai

Vote:

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

#249723

Mar 08, 2021 12:25

Henrik S

Vote:

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?

#249725

Mar 08, 2021 12:30

Quan Mai

Vote:

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 )

#249727

Mar 08, 2021 12:58

Henrik S

Vote:

Adding the mentioned index seems to have helped! Thanks!

#249897

Mar 11, 2021 11:56