High CPU usage by sql server

Question

Post reply

High CPU usage by sql server

Joy Smith San

SSC-Insane

Points: 24877
More actions
August 25, 2015 at 11:40 pm

#311107

Experts,
One of our server is consistently showing high cpu by sql server. It is dedicated db box too.
When check for high cpu usage queries found one of the query, which runs every sec from a service using most cpu.
However this query doesn't return any record nor it takes any time when tried from ssms.
Attached the actual execution plan snapshot of that query. Can someone pls guide me on how to troubleshoot this ? Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You probably get a different plan in SSMS compared to the application.

See this article for more info:

http://www.sommarskog.se/query-plan-mysteries.html

-- Gianluca Sartori

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Thanks.

Basically CPU and memory usage is consistently high in sql server.

Was trying to figure out whats wrong.

Any scripts to check step by step for CPU/memory utilization ?

Thanks again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

High memory usage is by design. SQL uses memory, as much as you allow it to use, this is not a problem or something that needs to be fixed.

For high CPU, identify the queries using lots of CPU and tune them.

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

Thanks Gila.

Had checked those links earlier, need to brush up again.

I read, parallel query processing may badly impact cpu usage and performance of the system in OLTP system where small queries are run frequently and from multiple sessions.

From the attached plan, do you think that particualr query going for parallel processing ?.

That query runs every sec from a service set up by app team. If ask them to add the hint maxdop(1) with query will it improve cpu usage by any chance ?

I understand that it cannot be concluded just like that, however any suggestions for further investigation from that point of view ?

Thanks again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Joy Smith San (8/26/2015)
however any suggestions for further investigation from that point of view ?

Yes. Don't try things at random.

Do your analysis. Identify the heaviest overall CPU-using queries. Tune them.

If Cost Threshold for parallelism is still at its default, you probably want to change it (higher). Hints are last resorts and shouldn't in general be used unless nothing else works.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101090 More actions · Answer 6

Can't troubleshoot that query without more details on it.

One specific thing to check, though, is that you have limited SQL's use of RAM via the 'max memory' setting, and not left it at the default of unlimited. Performance can become horrible if SQL uses so much memory that you get into memory thrashing between SQL and Windows.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".