Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am on decision border which processors I shall go for, and I need an information on "how actually works MSSQL 2012 with the CPU cores ?"

basically my question is follow :

  • 1 query which extracting billions of rows from multi-join query will it be executed from a single core, or it will be executed from the available cores ?
share|improve this question

1 Answer 1

I would suggest you read a book about SQL Server - Professional SQL Server 2012 Internals and Troubleshooting by Bolton, Langford, Berry, et al, is a good one.

First of all I want to tell you that you don't want to go core-crazy when designing a SQL 2012 server. Microsoft has changed its licensing model to per-core on SQL Server 2012, which bodes very poorly for AMD processors, which currently favor a higher number of relatively weaker cores, versus Intel processors which often have fewer physical cores but perform much better per core.

You can spend twice as much in licensing cost for a server with more AMD cores, or spend a little more on an Intel server with fewer cores and save a hundred thousand dollars in licensing and end up with a server that performs better.

A huge query with multiple joins like the one you described would generate a complex execution plan, be broken up into tasks by the query optimization process, and executed in parallel. So yes, that sort of query would definitely take advantage of the parallelism provided by multiple processors/cores.

Edit: Current generation Intel processors are just flat better than AMD processors on SQL 2012 workloads. Look at the TPC-E benchmarks on tpc.org. AMD processors simply cannot match the single-threaded performance of Intel processors at this time. AMD processors typically have more cores, but it does not make up the gap. In addition, lots of cores on SQL Server show diminishing returns, regardless of the manufacturer.

Also, read my blog post on this topic here.

share|improve this answer
2  
Adding to that that unless you have tons of ram and do OLAP only, you WILL be limited by IO a lot, too ;) –  TomTom Apr 19 '13 at 15:58
    
We have offer for User CAL licensing, which include unlimited CPU's on the server. That's why i am wondering 2 x 6 Core Xeon, or 2 x 16 core Opteron.. :| –  Yordan Yanakiev Apr 19 '13 at 23:48
    
Current generation Intel processors are just flat BETTER than AMD processors on SQL 2012 workloads. Look at the TPC-E benchmarks on tpc.org. AMD processors simply cannot match the single-threaded performance of Intel processors at this time. AMD processors simply have more cores, but it does not make up the gap. In addition, lots of cores on SQL Server show diminishing returns, regardless of the manufacturer. –  Ryan Ries Apr 19 '13 at 23:55
    
@YordanYanakiev Also, read my blog post on this topic here: myotherpcisacloud.com/post/2013/04/19/… –  Ryan Ries Apr 19 '13 at 23:56
    
hello Ryan Ries. i am a bit still dizzy, since the article is about the outdated Opterons, the current generation seems to be even cheaper and better in performance than the 62xx generation. And you are mentioning about Single threading which fills more oil into the mess. Is MSSQL actually executing each query in a single thread on a single core and wont be any benefit at all in multiply cores, since our current 8 cores is nonstop at 100% usage ?! ( generally the question ). –  Yordan Yanakiev Apr 20 '13 at 19:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.