SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Increasing CPUs hurts Perf


Increasing CPUs hurts Perf

Author
Message
as2higpark
as2higpark
Right there with Babe
Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)

Group: General Forum Members
Points: 755 Visits: 150
Hello everyone,

I have an issue that I have been pounding my head against a wall for days on.

We have a large client DB(400 GB) that we recently moved from a good box with a dual quad (8 total CPUs) Xeon 3.00GHz box to another box with Dual 12 core (24 total CPUs) Opteron 2.1GHz procs. The performance has decreased by close to 50%. Average request time (Web application) has doubled.

The RAM, and SQL install are identical (32GB of RAM per box, allocated from 8GB to 20GB, changed during troubleshooting). SQL 2008 10.0.4000 web edition both. The only differences that I can see is a new version of windows (2003 to 2008 R2) and the CPU change.

We have been trying everything we can think of to bring performance back in line to no avail. We have tried rebuilding all existing indexes, adding suggested ones, updating stats, changing the parallelism settings and nothing has seemed to work. (Max Parrallelism currently set to 0, we have tried 1,4,8,10, and 20)

I am hoping that someone may have stumbled upon some quirk with either this version of Windows and/or increasing CPUs.

Any thoughts, I am at a loss and looking for things to try.

Thanks for any help.
Mike John
Mike John
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6872 Visits: 6011
You mention many things such as CPU, memory etc, but what disk configuration did you have before, and what do you have now?

Mike



Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)

Group: General Forum Members
Points: 173751 Visits: 14641
I am interested to know the disk subsystem differences too.

Also, with MAXDOP now set to 0 what do your wait stats look like? And what was MAXDOP on the old server?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
as2higpark
as2higpark
Right there with Babe
Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)

Group: General Forum Members
Points: 755 Visits: 150
The disk subsystem is presented storage from a Dell EQ array. Both, the previous and current setups were on this, and seek times are under 10ms, so we have more or less ruled out Disk as root cause.

For MAXDOP, all our queries use default and system also does too.

WAIT stats do not look terribly bad, but I don't have a good way of seeing them over time currently? can someone suggest one?

We see sporatic times where CXPACKET times show up, but usually only for a second or two. Other times we see random PAGEIOLATCH_SH waits as well, but not enough to really see a root cause. The vast majority of queries don't show wait times and are runnable or running.

Thanks again for any pointers.
as2higpark
as2higpark
Right there with Babe
Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)

Group: General Forum Members
Points: 755 Visits: 150
Also wanted to add that we are seeing the CPUs (average of all 24) spike to around 50 - 70% sporatically throughout the work day. The spikes generally last 5 - 15 seconds.
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (274K reputation)SSC Guru (274K reputation)SSC Guru (274K reputation)SSC Guru (274K reputation)SSC Guru (274K reputation)SSC Guru (274K reputation)SSC Guru (274K reputation)SSC Guru (274K reputation)

Group: General Forum Members
Points: 274437 Visits: 9673
Since no other suggestions are comming through and you seem to have covered all the basics, I'd go through this.

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/


When filtering the results go by CPU and you should find the top cpu hogs in the system. Hopefully that'll give you an idea of where to start digging your way out of this one.
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)SSC Guru (173K reputation)

Group: General Forum Members
Points: 173751 Visits: 14641
as2higpark (7/2/2011)
Also wanted to add that we are seeing the CPUs (average of all 24) spike to around 50 - 70% sporatically throughout the work day. The spikes generally last 5 - 15 seconds.

What is your max memory setting in the new instance? I have seen people forget to set that on a new instance (including me) and have CPU problems when SQL starts fighting with Windows over memory during peak usage times.

Ignoring the new hardware scenario some random thoughts:

> the articles Ninja's posted are a good place to look
> it could also be a few bad plans might in play, or maybe some bad queries in general (scans or implicit data type conversions)
> Parallelism could be set too high...although you said there are not a lot of CXPACKET waits...you have to capture those wait stats periodically and do a diff for them to be meaningful. You can clear them after you capture them too. They also get reset when the instance restarts.
> Has any new code gone in since you've been on the new hardware?
> Have you done any stats or index fragmentation maintenance since you got onto the new hardware?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (934K reputation)SSC Guru (934K reputation)SSC Guru (934K reputation)SSC Guru (934K reputation)SSC Guru (934K reputation)SSC Guru (934K reputation)SSC Guru (934K reputation)SSC Guru (934K reputation)

Group: General Forum Members
Points: 934971 Visits: 49062
as2higpark (7/2/2011)
Also wanted to add that we are seeing the CPUs (average of all 24) spike to around 50 - 70% sporatically throughout the work day. The spikes generally last 5 - 15 seconds.


Do you have a virus scanner on the system?

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
as2higpark
as2higpark
Right there with Babe
Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)

Group: General Forum Members
Points: 755 Visits: 150
Thanks Guys,

I will try to setup the some traces on Tuesday, during business hours (only time we really see bad perf).

As far as the memory settings, we were initially running at 8GB allocated, but we have since bumped up to 20GB for that instance. Virus scanning is a no on this machine.

As far as capturing wait states periodically, I use a "What's running now" type of query to see a snapshot of current usage, but does anyone have a good one to use to capture wait states over time?
Eddie Wuerch
Eddie Wuerch
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11428 Visits: 3387
check the wait type of the running statements when things are spiking in CPU... if you seed a lot of CMEMETHREAD waits then you could be hitting a bug in SQL2008.... there's an issue when you get past 16 CPUs with the way plan get inserted into the plan cache. It's been fixed by a recent hot fix as of March.

If you're seeing plenty of CMEMTHREAD waits (you can see them in Activity Monitor or sys.dm_exec_requests), then you'll need to get yourself to the latest CU to resolve the issue, or drop the server to 16 CPUs or less.

Eddie Wuerch
MCM: SQL

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search