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, ((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.
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 )
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:
I can't really see any events related to the CPU spikes.
Would anyone be able to provide some advice in troubleshooting this issue?