SQL Server Performance Counters -CPU

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:

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.

Dynamic Management Views

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