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?
Were you able to use the Query Store to identify which queries are the most expensive?
We're on SQL Server 2012 - I'm not sure that Query Store is available
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
WHEN -1 THEN DATALENGTH(qt.TEXT)
END - qs.statement_start_offset)/2)+1),
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%.
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.
Yes we added that index in 11.1.0, which is why you are highly recommended to upgrade.
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?
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 )
Adding the mentioned index seems to have helped! Thanks!