CPU Usage is so high

Question

CPU Usage is so high

GA_SQL

Hall of Fame

Points: 3166
More actions
February 25, 2016 at 6:20 am

#316260

Totally 8 CPU Cores in that first 4 CPU Cores uses more CPU > 50%..after applying trace and tuning advisor steps now it reduced to 35%..
please suggest me reduce it further

Viewing 5 posts - 1 through 4 (of 4 total)

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

rvsc48 SSCertifiable Points: 7686 More actions · Answer 1

There are a lot of things to start looking at, one area you could start is Memory. How much memory does the server have and what is the MAXMEM setting on the sql instance set to (rt click sql instance>properties>memory)? Sometimes the server can experience high CPU if the server/or sql is feeling memory pressure.

GA_SQL Hall of Fame Points: 3166 More actions · Answer 2

Size of the Db is around 32 GB and memory(RAM) used is 15GB..

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

1) Why do you think it should be reduced further? CPUs do BILLIONS of things per second. I LOVE it when they are actually doing work for my client's queries (once things are tuned obviously)!!

2) You mention "tuning advisor". Assuming you meant Database Tuning Advisor THIS IS A BAD BAD BAD THING TO USE IF YOU DON'T KNOW WHAT YOU ARE DOING!!! You will wind up with MASSIVELY BAD INDEXING!!

3) The best, quickest and most efficient way to improve your database application BY FAR is to hire a performance tuning consultant for a targeted review.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

ganapathy.arvindan (2/25/2016)
Totally 8 CPU Cores in that first 4 CPU Cores uses more CPU > 50%..after applying trace and tuning advisor steps now it reduced to 35%..
please suggest me reduce it further

Continue the search for "crap code" and remember that indexing is NOT a panacea of performance. You WILL have to fix code to fix many of the problems before an index can even be used in many cases. You can start by right clicking on the instance in the Object Explorer in SSMS and selecting "Reports". Follow your nose from there to the performance related reports. You'll be amazed.

Also, be real careful about using the DTA (Tuning Advisor). It can and will make a train wreck of your indexes. Use it as an advisory tool and not the gospel. For example, it will frequently recommend an index with a dozen or more "INCLUDE" columns. Resist the temptation to just use such ridiculous covering indexes, which represent a gross duplication of data, extra loads for maintenance, backups, and restores, and can actually cause serious performance problems with INSERTs and UPDATEs.

"Must look eye"! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)