March 11, 2015 at 7:32 am
Hello Masters,
One my SQL Server (SQL 2008 R2) throwing High CPU utilization alerts. I checked and found that SQL server consuming almost 89% to 90% of CPU usage.
Current Service pack of this old server is SP2(SQL Server 2008 R2). One of my team-mate suggest to me to upgrade latest service pack, according to him it will also bring down the CPU usage !!:unsure:
Is it so ? Would Upgrading Latest service pack, bring down the CPU usage to normal ?
Regards,
Jitendra
March 11, 2015 at 7:43 am
Patching a server is always a good idea, but unless you've run into a bug that's causing the high CPU usage and is fixed in that service pack, it's not going to reduce CPU usage.
Chapter 3: http://www.red-gate.com/community/books/accidental-dba
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
March 11, 2015 at 8:06 am
It's probably the load on the server, the queries, the database design, the statistics, the indexes, etc., that are leading to high CPU use.
I'm with Gail, patch the server,yes. But you're going to want to look at a lot more information other than just the CPU to understand what's happening on the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2015 at 1:57 am
jitendra.padhiyar (3/11/2015)
Hello Masters,One my SQL Server (SQL 2008 R2) throwing High CPU utilization alerts. I checked and found that SQL server consuming almost 89% to 90% of CPU usage.
Current Service pack of this old server is SP2(SQL Server 2008 R2). One of my team-mate suggest to me to upgrade latest service pack, according to him it will also bring down the CPU usage !!:unsure:
Is it so ? Would Upgrading Latest service pack, bring down the CPU usage to normal ?
Regards,
Jitendra
It it a 'constant' 89%-90% or is there fluctuation and that's your average estimate?
A constant value seems to suggestion something is running continuously within SQL. (e.g. sql agent job)
I had ran into that particular scenario before.
Though based on what you are stating it does not seem to constant - thought I'd dbl check .. ask.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
March 13, 2015 at 3:30 am
You might get better CPU usage if you upgrade to 2014 due to the enhanced Statistics estimator.
But a patch most of the time won't improve performance.
High CPU occurs mostly when sql is doing parallel processing which mostly is set off with a scan on large tables.
First check should be to find most expensive procs (sys.dm_exec_procedure_stats) and see why they are using high CPU.
Check to make sure the code is written in the best way to use indexes.
IF code is written in best way, i.e. where clause and joins are sargable, THEN
Look for highest benefit missing indexes.
Don't just put them in.
Group them up and compare them with existing indexes and see if you can get the best common factor index, or assess if you could add a clustered index instead as the missing indexes are only non clustered index suggestions, test in staging or QA.
I am not sure if there is value in looking at wait types since if sql server is waiting, then the cpu would be low.
Also, check your query cost threshold for parallelism.
If the query cost threshold for parallelism is too low, then sql will parallelize a plan and use many cores (increased cpu) when a single core would complete the task in the same amount of time or less (CXPacket waits will cost some time).
Server defaults are set to 5.
March 16, 2015 at 1:28 am
Thanks all of you for your feedback and information.
I agree with all of you and will try to investigate more on "What causes more CPU usage to my SQL Server ?".
Also would try to make understand my Senior SQL DBA that each time upgrading SP is not the only solution. 🙂
March 16, 2015 at 3:14 am
You are being generous and tactful when you say that updating MSSQL is not the only solution.
I seriously doubt that it is ever the solution unless SQL is broken.
In my experience, high CPU is almost always a query which is doing scans, either because it has a certain type of Built in SQL function operating on a column which breaks Sargability and which can result in parallelism, or no index exists for the case where cardinality is high enough to use an index.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply