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

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.

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!