February 27, 2013 at 2:10 am
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
February 27, 2013 at 2:27 am
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
February 27, 2013 at 7:42 pm
GilaMonster (1/30/2013)
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/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
February 27, 2013 at 7:49 pm
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
February 27, 2013 at 8:46 pm
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.
February 27, 2013 at 8:52 pm
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.
February 28, 2013 at 12:38 am
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
February 28, 2013 at 1:49 am
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
February 28, 2013 at 2:40 am
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/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.
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
February 28, 2013 at 7:03 pm
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.
February 28, 2013 at 11:55 pm
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
March 1, 2013 at 6:22 am
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
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply