The most costy queries by CPU

Before to start to change everything, you must have a baseline that it established in normal load operation for comparisons.

SELECT TOP 20  
 qs.sql_handle
, DB_NAME(CAST(pa.value as INT)) as DatabaseName
, qs.execution_count
, qs.total_worker_time AS Total_CPU
, total_CPU_inSeconds = qs.total_worker_time/1000000
, average_CPU_inSeconds = (qs.total_worker_time/1000000) / qs.execution_count
, qs.total_elapsed_time
, total_elapsed_time_inSeconds = qs.total_elapsed_time/1000000
, st.text , qp.query_plan
FROM        sys.dm_exec_query_stats                 AS qs
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)     AS st
cross apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where pa.attribute = 'dbid'
ORDER BY qs.total_worker_time DESC
go

You can use SQL Server Performance Dashboard Reports.
https://sqldashboards.codeplex.com/

Source :
https://blogs.msdn.microsoft.com/sqltips/2005/10/05/find-top-n-costly-query-plans-in-adhoc-batches-or-modules/

https://www.johnsansom.com/how-to-identify-the-most-costly-sql-server-queries-using-dmvs/

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql