Sql-server – High usage of CPU Without use

performanceperformance-tuningsql-server-2012

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?

Best Answer

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

Related Solutions

Mysql – High MySQL CPU usage, queries seem fine, not running ntp

You can have a high load from lots of short queries as well. Try setting your long_query_time to 0, and force all clients to reconnect. Let that run for a few minutes and then set it back to your default. You can peruse the results by hand or use a tool like pt-query-digest.

In either case, you should be able to figure out if there is in fact a bunch going on, just very quickly.

Sql-server – FT BATCH CMPLETE in the cmd column for sysprocesses

When I queried sysprocesses to see what the spid was doing, it had FT BATCH CMPLETE in the cmd column. Does anyone know what this command is?

It is related to Full text catalog.

You can find out more info using sys.dm_fts_outstanding_batches

e.g. from Pro Full-Text Search 2008 book, Below will give you the Number of Full-Text Index Population Batches in progress

SELECT
 OBJECT_NAME(ob.table_id) AS table_name, 
 fc.name AS catalog_name,
 COUNT(*) AS outstanding_batches
FROM sys.dm_fts_outstanding_batches ob
INNER JOIN sys.fulltext_catalogs fc
 ON ob.catalog_id = fc.fulltext_catalog_id
INNER JOIN sys.databases d
 ON ob.database_id = d.database_id
GROUP BY
 ob.table_id,
 fc.name;

Also, you can use sys.dm_fts_index_population to get details about full-text index populations currently in progress.

I can't figure out what the process is doing, even tracing it comes up with nothing.

You can use Adam Machanic's - sp_WhoIsActive to get a more detailed view.

As a side note, sysprocesses is deprecated and is not accurate as per KB - Information on CPU and Physical_io Columns of Sysprocesses

sysprocesses.cpu is accurate but only updated at certain points. For lengthy operations that do not return any data to the client, it may not be updated for long intervals of time. Some of these operations may include DBCC CHECKDB, CREATE INDEX, database recovery, long-running joins, and so on. From a practical standpoint, it may often appear to be updated only at the end of each command.

If high CPU utilization is a frequent symptom on your server due to FullText, then highly suggest you to look into Improve the Performance of Full-Text Indexes

A good practice would be to baseline your server instance.

Best Answer

Related Solutions

Mysql – High MySQL CPU usage, queries seem fine, not running ntp

Sql-server – FT BATCH CMPLETE in the cmd column for sysprocesses

Related Question