There are many reasons for CPU related performance problems on MS SQL Server.
You can use Performance Monitor to check the load on your CPU. Look for the Processor:% Processor Time counter: if it regularly exceeds 80% of the processor time per CPU then most probably you're facing a CPU related bottleneck.
Some of the CPU intensive operations are compilation and recompilation. You can monitor them using the SQL Statistics object counters. Also you should monitor the number of batches received. If the ratio of SQL Recompilations/sec to Batch Requests/sec is high then it potentially indicates a problem.
Setup and monitor these counters:
SQL Server: SQL Statistics: SQL Compilations/sec
SQL Server: SQL Statistics: SQL Recompilations/sec
SQL Server: SQL Statistics: Batch Requests/sec
Another counter to detect CPU related problems is the SQL Server: Cursor Manager By Type - Cursor Requests/Sec counter which shows you the cursors used on your server. If you can see hundreds of cursor requests per second then it is most probably because of poor cursor usage and small fetch sizes.
Intraquery parallelism can also be detected by examining the SQL Statistics: Batch Requests/sec counter. The less number of batches processed per second during high CPU utilization periods, the more likely the batches are running with parallel plans.
select plan_handle, sum(total_worker_time) as total_worker_time, sum(execution_count) as total_execution_count, count(*) as number_of_statements from sys.dm_exec_query_stats group by plan_handle order by sum(total_worker_time), sum(execution_count) desc
Performance counters to watch
Which current SQL statements are causing problems on my server, This query helps me get a view of individual statements and the resources that they are currently utilizing, as well as statements that need to be reviewed for performance enhancements.
SELECT substring(text,qs.statement_start_offset/2 ,(CASE WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,qs.plan_generation_num as recompiles ,qs.execution_count as execution_count ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time ,qs.total_worker_time as cpu_time ,qs.total_logical_reads as reads ,qs.total_logical_writes as writes FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st LEFT JOIN sys.dm_exec_requests r ON qs.sql_handle = r.sql_handle ORDER BY 3 DESC