dual six core processor TSQL

Question

dual six core processor TSQL

clement.store

Old Hand

Points: 309
More actions
April 7, 2011 at 3:24 am

#238154

Dear all,
I wonder if I m working on some heavy calculation with TSQL. Would SQL server be automatically leverage all the 12 cores in the server? Or some changes need to be done...? Has anyone written TSQL on a 12 core SQL server?
Config:
Windows SQL server 2008 Standard 64bit.
Windows 2008R2 64 bit
CPU:
Xeon : X5650 / E5649 /E5645
thanks
Clement

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it depends on the work to be done; SQL would evaluate the command and make a decision/plan as to whether pieces of it can be processed in multiple cores; it potentially could use all processors available to it, but there's no way to predetermine how many it might use;

to know for sure you need to run the sql and include the execution plan.

AFAIK, there's no way to force SQL to use more cores, but the opposite is true: you can use the MAXDOP command to limit the number of cores used.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

clement.store (4/7/2011)
Dear all,
I wonder if I m working on some heavy calculation with TSQL. Would SQL server be automatically leverage all the 12 cores in the server? Or some changes need to be done...? Has anyone written TSQL on a 12 core SQL server?
Config:
Windows SQL server 2008 Standard 64bit.
Windows 2008R2 64 bit
CPU:
Xeon : X5650 / E5649 /E5645
thanks
Clement

There are times when such parallelism actually makes things slower. Seriously. The time to split the "stream" and reassemble it for final output can sometimes be quite high.

What I've found is that such "heavy calculations" are, many times, just poorly written code and simply need to be tuned using "Divide'n'Conquer" methods and a whole bunch of best practices including making sure that predicates are SARGable, etc, etc.

I've taken many such "heavy calculations" that would pin 4 CPU's to the wall for 45 minutes and did some relatively minor "Divide'n'Conquer" methods on them. Now, they use a single CPU and run between 3 and 8 seconds.

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

sturner SSC-Insane Points: 22411 More actions · Answer 3

Jeff Moden (4/7/2011)

There are times when such parallelism actually makes things slower. Seriously. The time to split the "stream" and reassemble it for final output can sometimes be quite high.
What I've found is that such "heavy calculations" are, many times, just poorly written code and simply need to be tuned using "Divide'n'Conquer" methods and a whole bunch of best practices including making sure that predicates are SARGable, etc, etc.
I've taken many such "heavy calculations" that would pin 4 CPU's to the wall for 45 minutes and did some relatively minor "Divide'n'Conquer" methods on them. Now, they use a single CPU and run between 3 and 8 seconds.

I am in complete agreement with your statements. While SQL server makes a quite an effort to leverage multiple CPU's in a query plan many times it is not optimal. I have found, for example, that by using table variables I can force single threaded execution in parts of a complex procedure resulting in 10-fold reduction in execution times.

If you look at the Activity Monitor and see a lot of waiting processes during execution of a query or procedure, you have a good candidate for optimization using the techniques Jeff mentions. The query optimizer is an impressive piece of logic, but in my opinion it is still not a match for an experience SQL database designer/programmer.

The probability of survival is inversely proportional to the angle of arrival.

clement.store Old Hand Points: 309 More actions · Answer 4

Thanks. experts. But I just wonder if the six-core would fit SQL server 2008...? Not sure if multiple cores from quad CPUs rather than 6 core CPUs would make any difference...could not find doc. to support this...

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

sturner (4/7/2011)
The query optimizer is an impressive piece of logic, but in my opinion it is still not a match for an experience SQL database designer/programmer.

I couldn't have said that better. Mind if I borrow that with full credits? 🙂

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

sturner SSC-Insane Points: 22411 More actions · Answer 6

Jeff Moden (4/7/2011)
sturner (4/7/2011)
The query optimizer is an impressive piece of logic, but in my opinion it is still not a match for an experience SQL database designer/programmer.
I couldn't have said that better. Mind if I borrow that with full credits? 🙂

Most certainly Sir.

The probability of survival is inversely proportional to the angle of arrival.

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

Many thanks. Would you mind if I knew your first name? "Sturner said..." just sounds so base. 🙂

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

sturner SSC-Insane Points: 22411 More actions · Answer 8

Sure Jeff, my name is Scott. I've been a participant here for a while so no need for obscurity anymore.

I have appreciated many of your excellent posts over that time as well as those of many others.

- Scott Turner

The probability of survival is inversely proportional to the angle of arrival.

davis.gerald Valued Member Points: 64 More actions · Answer 9

clement.store (4/7/2011)
Thanks. experts. But I just wonder if the six-core would fit SQL server 2008...? Not sure if multiple cores from quad CPUs rather than 6 core CPUs would make any difference...could not find doc. to support this...

There are no docs because SQL Server doesn't care. 4 cores, 6 cores, 8 cores, 12 cores likely someday 48 cores. SQL Server just sees them as computational power.

Our analysis server has 24 cores (4 socket, 6 cores). MAX DOP is set to 8. So no single query uses more than 8 cores but having 24 cores makes the server "wider". It can execute more computational heavy queries at the same time.

Back to your original question. SQL Server can handle as many cores as you can afford. Some setups have over 100 cores. Now if YOUR server running YOUR code will benefit from more cores nobody can answer that but you. Some code is not well suited for parallelization. You could see higher throughput with higher clocked CPU that has less cores.

Still in my experience CPU is rarely the bottleneck on most servers unless you have horribly inefficient code. Disk then memory then CPU.

dandenson Mr or Mrs. 500 Points: 566 More actions · Answer 10

this is true for virtualization and I suspect also quite true for sql.

The use of multiple cores and the resulting performance depends on these factors.

1) how can the execution be split and how many cycles does that logic eat. very very simple, quick code will benefit much less than complex code because the calculation to split the simple code may take as long as the code itself to complete.

2) the physical/bus layout of the CPU/SMP matters immensly. For example,

a 2 socket, 8 core system where the 2 CPUs are composed of 2 cores on a die, and two dies on a package, then two sockets.

C# = Core, o = interconnect/latency

c1c2oc3c4 ooooo c5c6oc7c8

so c1 and c2 are VERY fast in the connection, they share the same die. there is an added latency between c1c2 and c3c4 and there is a MUCH larger latency between the c1-4 and c5-8.

If your multithreading is aware of the physical layout, it can split jobs into two threads on c1 and c2 and another job on c3 and c4 etc intelegently. If it is not (most all software is not) then you will have jobs split between many cores with various amounts of latency between them. If a process is told it can run on all 8 cores, it can move threads between CPUs and then the much higher latency between c1 and c5 manifests in a HUGE drop in performance as threads migrate. Better to have executed the job on a single core or at least on a single die.

This latency is one of the two big reasons why a 3Ghz CPU is most always faster running two identical tasks that a dual core 1.5Ghz. Despite the equity in total Mhz and the fact that a process can live on each core as equally as two process can live on a single, faster core, process/thread migration causes a penalty. Also why a dual core on a single die > dual core on two dies (P4) > two socks with single core CPUs

When virtualizing servers, I set CPU affinity on the VMs to line up with known CPU architecture. I run quad socket x quad CPU systems (Dell PE5xx systems w/ Xeon) with true quad cores so I can give a VM up to 4 cores, any more and the performance penalty is so great that it takes 5 cores on two CPUs to equal 4 cores on a single CPU in many workloads.

from what I have read, MSSQL relies on the Windows Server process scheduler which is not well aware of the underlying CPU packaging so it considers all execution threads equal. This is also when I suspect hyper-threading is a waste of time on SQL2008.

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

sturner (4/7/2011)
Sure Jeff, my name is Scott. I've been a participant here for a while so no need for obscurity anymore.
I have appreciated many of your excellent posts over that time as well as those of many others.
- Scott Turner

Thanks for the introduction, Scott.... and the kudo. :blush: You've not been doing so bad yourself. 🙂

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