Inside SQL Server: simple CPU performance bottlenecks and checks

N a long time ago, I read the technical insider's section on CPU analysis. I wrote one article, but before I finish writing it, I have something to do. Later I thought I had published it. I saw it in the draft box today.

I sorted it out and published it...

The Analysis of CPU utilization focuses on CPU bottlenecks, compilation, and decompilation.

1. CPU bottleneck

You can check the Processor: % Processor Time counter in PERFMON to determine whether there is a hard bottleneck. If the value 1 is too high (greater than 80%), you can consider it necessary to improve CPU performance.

You can also query DMV: sys. dm_ OS _schedulers. Scheduler_id = 255 is DAC scheduling, while scheduler_id> 255 is internal scheduling of SQL Server. If runnable_tasks_count is not 0,

If a task needs to wait for a time slice to run, you can consider that the CPU performance needs to be improved.

select scheduler_id,current_tasks_count,runnable_tasks_count 
from sys.dm_os_schedulers 
where scheduler_id <255

 

-- You can query and view the most time-consuming query and processing.

selecttop20sum(qs.total_worker_time) as total_cpu_time, 
sum(qs.execution_count) as total_execution_count,
count(*) as  number_of_statements, qs.plan_handle,st.text
from sys.dm_exec_query_stats qs 
cross apply sys.dm_exec_sql_text(qs.plan_handle) as st
groupby qs.plan_handle ,st.text
orderbysum(qs.total_worker_time) des

Of course, the CPU is not just added, the price is expensive, the application is difficult, and the installation may require downtime.

2. Compilation and Decompilation

Compilation and decompilation are also very CPU-consuming processing. The existing system also has a lot of compilation and decompilation due to some changes (schema, statistics, set attribute, temporary table, and so on, CPU usage increases.

You can observe

SQL Server: SQL Statistics: Batch Requests/sec,

SQL Server: SQL Statistics: SQL Compilations/sec,

SQL Server: SQL Statistics: SQL Recompilations/sec,

As the name suggests, the lower the value of the next two counting indicators, the better. If the values of the two are very high, we need to use Profiler for tracking to find out the statements and objects that cause a large number of compilation and decompilation.

When defining a trail event, SQL Server 2005, I personally think that you only need to trace SQL: StmtRecompile.

Save the trace file and read the data to analyze the frequently compiled and decompiled objects. The analysis methods can be varied. The key and difficulty is to aggregate data by Textdata to obtain the actual analysis results.

Because the same statement may be identified as different statements by sqlServer because of different parameters.

select spid,StartTime,Textdata,EventSubclass,ObjectID,
DatabaseID,SQLHandle ,CPU
from fn_trace_gettable(N'D:\workload_20110707\workload_20110707.trc',1)
orderby CPU desc

Inside sqlServer has mentioned using a function written by Microsoft engineering for processing. If you are interested, you can learn how to use it. I personally think: Perform systematic, long-term operations, and save them to the database for data analysis,

It should be done according to the book. However, in general cases (not recommended), it seems that a few statements will come first (if many statements have serious compilation and decompilation situations, the topic should be: how to handle the cpu 100% class), you can use Substring to intercept the first 20 or 80 characters for Group By, you should be able to know the most serious statements.

 

--------------------------------------------

If it is reproduced or referenced, keep the following content:
Joe's Blog: http://www.cnblogs.com/Joe-T/