CPU負荷をかけているクエリを手っ取り早く探すにはDMV（動的管理ビュー）を使うのが一つの手ですが、「まさに今、高負荷をかけている原因となっている機能は何か？」を探すときに使っている手法を紹介します。
ちなみにDMVだと↓みたいな感じ
--ORDER BY を変えればいろいろな順番でデータ取得できる
SELECT TOP 1000
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY total_worker_time DESC
1.SQLServerProfilerでトレース
結果の保存先を指定します。テーブルでもOKですが、本番稼動しているDBにトレースデータを保存したくない場合は、一旦ローカルのファイルに保存しておいて、開発環境DBにINSERTすることで気兼ねなく分析することも可能です。（僕は後者をよく使います）
トレースするイベントを指定します。イベントは図に示す3つがあれば大丈夫かと思いますが、必要に応じて増やします（イベントを増やせば増やすほどトレース時のサーバーへの負荷が増大するため、必要最小限に留めたほうがいいです）
取得する列は
・TextData
・LoginName
・CPU
・Reads
・Writes
・Duration
・SPID
・StartTime
・HostName
・DatabaseID
あたりがあれば良いかと思います。
大量にクエリが飛んでるサーバーの場合は、Durationでフィルタをかけた方が特定しやすいし、トレースデータ量も抑えることができるので望ましいです。
例ではDuration(実行時間)が100ミリ秒以上のイベントだけを採取するよう設定しています。
あとはトレースを実行して待つだけ。必要に応じて10分～1時間ほど採取すれば良いかと思います。
トレースはサーバーへ負荷がかかるのと、たまにフリーズしちゃうと大きなロックがかかっちゃうようでタイムアウトエラーが頻発するので、本番DBでのトレースは本当に慎重にやったほうがいいです。
2.トレースファイルをテーブルに保存
開発環境にSSMSから接続し、SQLServerProfiler立ち上げて、先ほど保存したトレースファイルを開きます。
開いた後は、[ファイル]-[名前を付けて保存]-[トレーステーブル]-認証後、適当なDBに適当な名前つけて保存（例：TraceTable）
3.トレーステーブルにクエリを投げる
ここからは、機能単位で最もCPUに付加をかけているクエリを見つけていきます。
機能単位とは、例えば「WEBサービスのログイン機能」などです。
クエリ単位だとアドホックであったり、パラメータ化クエリだとしてもパラメータが違っていれば、単純にTextDataでgroup by すると、機能単位で集計することができません。
なので、↓のようにTextDataの先頭○文字でgroup by することにより、機能単位での集計を実現させます。
SELECT
max(cast(TextData as varchar(max))) as textdata
,max(hostname) as hostname
,max(loginname) as loginname
,sum(cpu) as cpu_sum
,avg(cpu) as cpu
,avg(reads) as reads
,avg(cast(duration as float) / 1000.0) as duration
,count(*) as cnt
,(sum(cast(cpu as float)) / (select sum(cpu) from TraceTable) * 100) as 'cpu 全体に占める割合(%)'
,(sum(cast(duration as float)) / (select sum(cast(duration as float)) from TraceTable) * 100) as 'duration 全体に占める割合(%)'
,(cast(count(*) as float) / (select count(*) from TraceTable) * 100) as '実行回数 全体に占める割合(%)'
FROM TraceTable
--ここのgroup by がポイント。SUBSTRING使うときに何文字分を切り取るかは、最初にgroup by せずにクエリをざっと眺めて、何文字文くらいでうまくグループ化できるか検討する
group by SUBSTRING(TextData, 1, 20)
-- order by avg(cpu) desc --平均CPU多い順
-- orde by sum(cpu) desc --CPU合計値多い順
--order by count(*) desc --実行回数多い順
order by (sum(cast(cpu as float)) / (select sum(cpu) from TraceTable) * 100) desc --CPUの全体に割合が大きい順
4. 特定したクエリに対応する機能を探す
クエリが特定できたら、あとは地道にプログラムのどこで呼び出されているか調べていけば、負荷をかけている機能を特定できると思います。
この手順で、僕は最もCPU負荷をかけている機能をみつけだしています。
他に便利な手順があれば是非知りたいです！