SQL Server 2005 Performance Test CPU chapter (compilation and recompilation)

If the CPU bottleneck suddenly occurs without additional complex conditions, it may be because there is no optimized query, incorrect database configuration, or the reason for database design and insufficient hardware resources. Before deciding whether to increase the number of CPUs or to use a faster CPU, you should first check that the most CPU-intensive operations can be optimized.

If you find that the performance counter Processor:% Processor time value is high, each CPU% Processor times is more than 80%, can be considered a CPU bottleneck. You can also monitor SQL Server process scheduling (schedulers) through view sys.dm_os_schedulers to verify that the executable task is a value other than 0. A non-0 value indicates that the task is forced to wait for the time slice to run, if the value is very high, indicating a CPU bottleneck.

Select scheduler_id,current_task_count,
runnable_task_count from sys.dm_os_schedulers where scheduler_id<255

The following query gives a higher level view of the batch or process that is currently cached with the most CPU resources consumed. The query aggregates the CPU consumption by all statements with the same query handle.

Select top 50 sum (qs_total_worker_time) as total_cpu_time,sum(qs.execution_count)
as total_execution_count, count(*) as number_of_statements,
qs.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order
by sum(qs.total_worker_time) desc

Too much compilation and recompilation.

The system checks the validity and correctness of the query plan before a batch or remote procedure call (RPC) is submitted to the server for execution. If a failure occurs during the inspection process, these batches may be compiled again to produce a new query plan. Such a compilation is referred to as a recompile (recompilations). These recompilation generally must be correct and usually executed when the server determines that a potentially attractive query plan exists after the potential data has changed. The compiled feature is CPU-sensitive, so excessive recompilation can cause CPU performance problems.