sqlserver.exe takes high CPU usage, that is more than 90%. In which scenario's sql server takes so much CPU usage?

Question

I have SQL Server 2008 R2 on server, my issue is its CPU usage reaches more than 90%. I just need to know that,

In which cases or scenario's, the sql server's cpu memory usage goes so high?

Answer 1

In which cases or scenario's, the sql server's cpu memory usage goes so high?

This is a very broad question,but here are the things in order i will check..

Are there any applications hosted on same box other than SQLServer ..? if yes try to avoid them,as this also doesn't fall under best practices..

Basic troubleshooting to start with ,when SQLServer is using huge CPU is, to find out top CPU Consuming queries..

this can be found using below query or you can use Glen Berry DMV's specific to Version you are using..

 SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
   st.text,
   qp.query_plan
from
    sys.dm_exec_query_stats as qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
    cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
ORDER BY qs.total_worker_time desc

next step is to try to optimise those queries...Remember a query can use High CPU,when

1.There are no appropriate indexes,so it has to read the entire table every time..
2.Indexes are there ,but you are also facing memory pressure ,which can cause buffer pool to be flushed out..

Also Consider base lining the performance of SQLServer,which will help you in future and to see if you need to more processors..