Low Performance - High CPU Problem and others...

Question

Low Performance - High CPU Problem and others...

Viewing 12 posts - 31 through 41 (of 41 total)

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

Query Shepherd SSCrazy Points: 2988 More actions · Answer 1

Got a lot of SOS_SCHEDULER_YIELDS-waits and a very high signal time. Could it also be possible, that we just need more CPUs?

Greetz
Query Shepherd

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

http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ Chapter 3.

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

GilaMonster (1/30/2013)
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/
No magic bullets.
And no, adding the pk column to NC indexes is not going to fix lookups, and is probably a waste of time.

I've had some success with adding the PK column to the index itself. It allows me to make the index UNIQUE and it seems that the optimizer likes that a whole lot in many cases.

--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)

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

SQL Pizza (2/27/2013)
Got a lot of SOS_SCHEDULER_YIELDS-waits and a very high signal time. Could it also be possible, that we just need more CPUs?

As Gail has already said and the others have inferred, "there's no magic bullet". There's usually (but not always)some improvement after adding extra hardware capability but not like the kind of improvements you can get out of either a thoughtful tweak to the code or a full redaction.

For example, low performance/high CPU on a query is, many times, indicative of a many-to-many join that people try to overcome with (usually) DISTINCT. These accidental cross-joins are typical in code where many tables exist in the FROM clause. If you can find the tables that are causing the many-to-many join, isolate that part of a query in a Temp Table, and then join to the Temp Table, you can make (for example) 45 minute year end queries that virtually stop a server from all other activity to an 8 second "burp" that won't even show up on the Task Manager chart.

You just can't get that kind of improvement out of additional or even new hardware unless your current server is an old "trash-80".

--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)

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 5

Perfmon counters can really help as well.

They can give you a high level view of your server, and can provide evidence to help determine if things are improving or getting worse.

For example, last year I started a job at a company that was having performance issues similar to what you described. CPU would creep from 40% in the day to 100% during the evening peak and stay there for a couple hours. Queries would time out and the website would be almost unusable.

So I tuned and tuned and tuned and cpu still went to 100%. However, I knew I was making progress because the volume of queries sql was handling was increasing. And I had data to prove it.

Eventually, a combination of indexing, application changes, and server config got the daily peak down to 65-70% cpu.

Load kept going up, so we bought new HW.

Tuning can take you far (typically much further than you'd expect) but it does have limits.

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 6

Jeff Moden (2/27/2013)
SQL Pizza (2/27/2013)
Got a lot of SOS_SCHEDULER_YIELDS-waits and a very high signal time. Could it also be possible, that we just need more CPUs?
As Gail has already said and the others have inferred, "there's no magic bullet". There's usually (but not always)some improvement after adding extra hardware capability but not like the kind of improvements you can get out of either a thoughtful tweak to the code or a full redaction.
For example, low performance/high CPU on a query is, many times, indicative of a many-to-many join that people try to overcome with (usually) DISTINCT. These accidental cross-joins are typical in code where many tables exist in the FROM clause. If you can find the tables that are causing the many-to-many join, isolate that part of a query in a Temp Table, and then join to the Temp Table, you can make (for example) 45 minute year end queries that virtually stop a server from all other activity to an 8 second "burp" that won't even show up on the Task Manager chart.
You just can't get that kind of improvement out of additional or even new hardware unless your current server is an old "trash-80".

If the server is 4 years old, then it's fairly easy to get 3x or 4x the cpu capacity using the new Intel sandy bridge processors.

Query Shepherd SSCrazy Points: 2988 More actions · Answer 7

It's an Intel XEON CPU E5450 Quadcore 3 GHz. Good enough?

This is also my opinion. It's a combination of several things that bring me out of trouble :

- Reducing the workload (f.e. usage of queuing, workover the queries)

- A good indexing strategy

- Upgrading the hardware (f.e. a new cpu), because we want to go live and we will get 100 of customers more than at the moment

I just asked myself, if there is a point, where you can't optimize queries and indexing anymore, cause it's just to much workload for the cpu to handle it.

Thanks for the advice!

Greetz
Query Shepherd

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

SQL Pizza (2/28/2013)
I just asked myself, if there is a point, where you can't optimize queries and indexing anymore, cause it's just to much workload for the cpu to handle it.

Sure, but a well-tuned SQL database typically is not CPU bound. Typically you hit the capacity on the IO subsystem far earlier. It's very hard to max out a modern processor with well tuned queries.

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Jeff Moden (2/27/2013)
GilaMonster (1/30/2013)
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/
No magic bullets.
And no, adding the pk column to NC indexes is not going to fix lookups, and is probably a waste of time.
I've had some success with adding the PK column to the index itself. It allows me to make the index UNIQUE and it seems that the optimizer likes that a whole lot in many cases.

+1. Not often, usually all you end up with is extra index overhead, but occasionally it's just the ticket. It still seems "wrong" but I'll live with that.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 10

That cpu benchmarks at about 40% what a new e5-2690 does. 2.5x the cpu can kick the can down the road quite a bit.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

SpringTownDBA (2/27/2013)
Jeff Moden (2/27/2013)
SQL Pizza (2/27/2013)
Got a lot of SOS_SCHEDULER_YIELDS-waits and a very high signal time. Could it also be possible, that we just need more CPUs?
As Gail has already said and the others have inferred, "there's no magic bullet". There's usually (but not always)some improvement after adding extra hardware capability but not like the kind of improvements you can get out of either a thoughtful tweak to the code or a full redaction.
For example, low performance/high CPU on a query is, many times, indicative of a many-to-many join that people try to overcome with (usually) DISTINCT. These accidental cross-joins are typical in code where many tables exist in the FROM clause. If you can find the tables that are causing the many-to-many join, isolate that part of a query in a Temp Table, and then join to the Temp Table, you can make (for example) 45 minute year end queries that virtually stop a server from all other activity to an 8 second "burp" that won't even show up on the Task Manager chart.
You just can't get that kind of improvement out of additional or even new hardware unless your current server is an old "trash-80".
If the server is 4 years old, then it's fairly easy to get 3x or 4x the cpu capacity using the new Intel sandy bridge processors.

Yeah but I'm not talking about 3x or 4x. I'm talking hundreds and, sometimes, thousands x improvement. For example, I rewrote one job that was taking 24 hours on a great server so that it only took 11 minutes to do 50% more work. Including that extra work, that's 196 x. Where are you going to get such an improvement from hardware without buying a Cray?

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

GilaMonster (1/31/2013)
There isn't a magic bullet. You need to sit down and tune the queries. If you have no idea where to start, consider hiring someone who does.

This thread has been going on for a month now, and that is by far the most important and useful post I have seen. Get a professional tuner/mentor on board and it is a win for you company because queries will IMMEDIATELY start being made faster. It is also a win for you because you will get direct training on YOUR application/system about how to analyze and tune performance on SQL Server. Trust me, that is WAY BETTER than taking a class that uses AdventureWorks or some other database. During this time you would also avoid doing some of the HORRIBLY BAD things you COULD attempt on your own in an attempt to make things better.

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