1

I'm trying to figure out what is happening but at this point I cannot troubleshoot this issue...

I have 2 servers with same specs, actually also with almost same users connected (Only me)...

One of the servers is stable at 20% CPU usage while the other is at 1%... Same Databases, same Processes...

How I can know what is actually consuming this high amount of CPU on SQL Server compared to the other server if both of them are idle?

  • Does it happen that your page life time expectancy is zero? – vonPryz Sep 23 '16 at 7:56
  • My PLE is super high, around 100k seconds. BCHR 100% – J1mmy Sep 23 '16 at 7:58
  • What SP and CU are you on? There have been numerous lost threads issues fixed over time. – Cody Konior Sep 23 '16 at 9:06
  • Are you sure it's SQL using the CPU? Try this query. sqlblog.com/blogs/ben_nevarez/archive/2009/07/26/… – Erik Darling Sep 23 '16 at 9:35
  • SP3 CU1. I'm sure is the SQL who is using the CPU I have a query that is retrieving this information and storing it in a table, matches what the Activity Monitor is showing. – J1mmy Sep 23 '16 at 9:45
2

First of all you should find out, which service/process consuming maximum CPU.

You can you Process Monitor tool provided by Microsoft, which give details information.

You can find it here https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx

If it's SQL Server, then

1st I would recommend to use master.dbo.sysprocess as

select * from master.dbo.sysprocesses order by cpu desc

if it returns spid < 50 on top means, it's SQL Server own process not user process. So, on the basis of lastwaittype column value you can change recommended (according the wait type) SQL Server configuration or tune your HW.

If it returns process id >50 means it's user process and you can use below query to find details about the process and tune your query.

if the session is active (running, runable, suspanded)

select
            db_name(sp.dbid),sp.spid,er.wait_type,er.wait_time,er.wait_resource,er.total_elapsed_time,st.text,qp.query_plan
            ,ec.net_packet_size,ec.client_net_address,es.host_name,es.program_name,es.client_interface_name
            ,es.status,es.cpu_time,qmg.granted_memory_kb,es.total_scheduled_time,es.total_elapsed_time
            ,es.reads,es.writes,es.logical_reads

from
                sys.dm_exec_requests er
inner join      master.dbo.sysprocesses sp
on              er.session_id=sp.spid
inner join      sys.dm_exec_connections ec
on              er.session_id=ec.session_id
inner join      sys.dm_exec_sessions es
on              ec.session_id=es.session_id
inner join      sys.dm_exec_query_memory_grants qmg
on er.session_id=qmg.session_id
cross apply     (select text from sys.dm_exec_sql_text(er.sql_handle)) st
cross apply     (select * from sys.dm_exec_query_plan(er.plan_handle)) qp

If the session is not active (sleeping).

select a.spid,db_name(b.dbid) [DB Name],b.text [Query],a.cpu
from master.dbo.sysprocesses a
cross apply sys.dm_exec_sql_text(a.sql_handle) b where status='sleeping'

Thanks

  • Thanks a lot for your help... Top 5 are SQL Server own process... Top 1 is Checkpoint If I compare the results of the "busy" server with the "idle" one, they have almost the same results... 700k CPU on "busy" 500k CPU on "iddle" – J1mmy Sep 23 '16 at 9:55
  • Is there any difference between size of transaction log file of the databases available on both server? Also compare number of VLFs using DBCC LOGINFO. Too many VLF cause to slow and heavy CHECKPOINT. You can go through the link technet.microsoft.com/en-us/magazine/2008.08.database for better T-Log management. – Rajesh Ranjan Sep 23 '16 at 12:42
  • "Busy" DB has 20GB of Log File (Users have been testing loads this morning, "idle" Database has 10GB Log File. "Busy" Database DBCC LOGINFO Return 587 rows, "idle" Loginfo return 507 Rows. Each row means 1 VLF? – J1mmy Sep 23 '16 at 13:13
  • 1
    Yes each row means 1 VLF. I think this difference causing high CPU utilization by CHECKPOIT process. You should go through the link sqlmag.com/blog/sizing-your-transaction-log and resize your T-Log file. And also verify if other process eating CPU. – Rajesh Ranjan Sep 23 '16 at 14:06
1

Here is the query to get top 5 queries consuming most cpu,with their execution plan.

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
Plan_handle, query_plan   
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)  
ORDER BY total_worker_time/execution_count DESC;  
GO  
0

I can able view that in Activity Monitor which appears when you right click on the SQL server in SSMS. There you can find Process memory utilization and processor utilization of the queries that ran recently.

For more information, please try querying sys.sysprocessess table. Hope it helps.

  • Hello, nothing I can see using any tool, in fact, If I query the CPU that is being used per each Database I can see than Master database is using almost the 70% of the CPU Being used by SQL – J1mmy Sep 23 '16 at 8:46
0

Are you sure the CPU usage come from SQL Server?
Use the query below, and then let us know.

DECLARE @ts_now2 BIGINT
SELECT @ts_now2 = cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info

SELECT TOP (1000) SQLProcessUtilization AS [SQL Server Process CPU Utilization]
    ,SystemIdle AS [System Idle Process]
    ,100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
    ,Dateadd(ms, - 1 * (@ts_now2 - [timestamp]), Getdate()) AS [Event Time]
FROM (
    SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
        ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle]
        ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization]
        ,[timestamp]
    FROM (
        SELECT [timestamp]
            ,CONVERT(XML, record) AS [record]
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%'
        ) AS x
    ) AS y
ORDER BY record_id DESC;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.