SQL server slowdown by increasing processors?

Question

Post reply

SQL server slowdown by increasing processors?

awarnke 39611

Grasshopper

Points: 22
More actions
December 22, 2015 at 9:09 am

#310430

Hello,
We have been having problems with some of our reports timing out. looking at utilization we saw high CPU utilization, so we increased the number of processors. Although the reports are running fine now, they are running about 30% of the speed we were running previously. We also tried increasing cores instead with the same results. CPU utilization is about 50% other stats don't seem out of line. Any suggestions are greatly appreciated.
Regards,
Aaron

Viewing 9 posts - 1 through 9 (of 9 total)

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

Eric M Russell SSC Guru Points: 125596 More actions · Answer 1

Have you confirmed how many CPU are visible to SQL Server? This could be limited by licensing or configuration.

http://www.eraofdata.com/how-many-cpus-can-sql-server-use/

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

awarnke 39611 Grasshopper Points: 22 More actions · Answer 2

Thanks Eric,

We're pretty small, We up'd it from 1 to 2.

select cpu_count from sys.dm_os_sys_info

2

Is there something else I can consider?

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

In addition to what Eric said, it's not altogether uncommon for some queries to exhibit decreased performance after adding processors or cores.

Sometimes SQL Server thinks a parallel plan is a better choice than it did with fewer cores (and is wrong), or the parallel implementation just scales much more poorly with more available cores. I've run into that several times in situations where cores were added to a server as a quick reaction to some performance issues.

In this case, it won't be the parallel implementation scaling more poorly than it did before, since there wouldn't be any parallel plans initially at 1 core :-). It might still be picking a parallel plan where it mistakenly thinks that will be cheaper than a serial plan, though.

If the server is virtualized, you also have to consider potential implications at that layer from adding cores (potential ready state issues, where the VM has to contend more with other machines to get scheduled on physical cores).

Ultimately something like this just needs some standard performance tuning. Check the wait stats for the problematic time periods (you would need to set up something to capture wait stats at intervals) and check execution plans and execution statistics for the poorly performing queries. That will show you where the query is spending its time (maybe it's doing an excessive number of reads, or maybe there are some implicit data type conversions chewing up CPU), and then you can use that and what you see in the execution plan to figure out if the query can be rewritten in a more efficient way.

There's no silver bullet, unfortunately (or fortunately, for the occupation :-)); it takes collection of the appropriate data and some human analysis to address these sorts of issues.

Cheers!

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

You can find out if that's the actual problem. Add OPTION(MAXDOP 1) to the reporting queries to limit to 1 CPU and see what happens.

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

awarnke 39611 Grasshopper Points: 22 More actions · Answer 5

Thank you all for your replies.

Jacob you are correct it is a virtual environment. We tried giving priority to the processors, but not much change. We also tried the OPTION (MAXDOP 1) and didn't get much of an improvement. Note that almost all of the queries are (poorly written) openquery statements to oracle (all created before I started). I'd go back and rewrite the queries, but that would be a huge undertaking for a system we are moving towards obsolescence. We'll continue to dig in, but please chime in if you have any other pointers.

-AW

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

awarnke 39611 (12/22/2015)
I'd go back and rewrite the queries, but that would be a huge undertaking for a system we are moving towards obsolescence. We'll continue to dig in, but please chime in if you have any other pointers.
-AW

Heh... if it were me, I'd try to accelerate the schedule for obsolescence. Now would be the perfect time. 😛

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

Eric M Russell SSC Guru Points: 125596 More actions · Answer 7

awarnke 39611 (12/22/2015)
Thank you all for your replies.
Jacob you are correct it is a virtual environment. We tried giving priority to the processors, but not much change. We also tried the OPTION (MAXDOP 1) and didn't get much of an improvement. Note that almost all of the queries are (poorly written) openquery statements to oracle (all created before I started). I'd go back and rewrite the queries, but that would be a huge undertaking for a system we are moving towards obsolescence. We'll continue to dig in, but please chime in if you have any other pointers.
-AW

Hugh? Now you tell us that these are pass-through queties to Oracle, or perhaps we've only been discussing a specific query. Remote queries, especially against othet RDBMS, are an entirely different issue.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Wayne West SSC-Insane Points: 22586 More actions · Answer 8

Yeah, if you're passing a query to an Oracle box, do you have any way of knowing if the delay is there? Totally different situation than a SQL Server slowing down.

Also, be careful increasing CPUs, you could put yourself in violation of your licensing!

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]