SQL 2000 and multi-core technology

Question

SQL 2000 and multi-core technology

Carl Rimmer

SSCarpal Tunnel

Points: 4068
More actions
May 4, 2007 at 7:51 am

#63065

All,
Does SQL 2000 actually support multi-core processors? We have single processor servers but are looking at buying a multi-core server.
Regards
Carl

Viewing 8 posts - 1 through 7 (of 7 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736544 More actions · Answer 1

Yes. The cores don't matter to SQL Server, it just sees what the OS presents. And licensing is by sockets, not cores, so you license by the CPU sockets. 2 quad cores is a 2 CPU license.

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 2

Thanks Steve.

Another question.

If we use multi-core processors on SQL 2000 or SQL 2005 do we need to set some settings in the queries we write to ensure the query will use all 4 processors if we have a double dual-core server? Or is the O/S DB Engine clever enough to use as many processors as it can?

Regards

Carl

Antares686 SSC Guru Points: 125444 More actions · Answer 3

You only need to tell it when not to use all (MAX DOP) if you find a query being affected negatively by parallel processing. Otherwise the Server will try to take advantage of all logical processors present, unless you have configured the servers settings to only use fewer processors specifically, right click server in Enterprise Manager and select Properties and look at the Processors tab after you upgrade to verify.

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 4

Thanks for your help.

I have been asked to develop a batch type system in very short notice and haven't had any experience using SQL 2005. The system will heavily use DTS/SSIS depending on what version of SQL we use.

Do you think the benefits of using SQL 2005 outweigh the risks/potential problems of developing the system using SQL 2005 SSIS?

Regards

Carl

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Not sure I can answer that without a lot more information about your application. If I had an option of 2005 over 2000 I would go 2005 for sure and SSIS. But not sure how your current system could be impacted by going to 2005 without knowing what your app does and what it will need to do afterwards.

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 6

I haven't actually developed the system yet. But the thinking would be to develop the system using SQL 2000 DTS as I am experienced in DTS and when we have a stable system to migrate to SQL 2005 SSIS.

Would SQL 2005 provide a performance benefit compared to SQL 2000? I know that SQL 2005 benefits from DB Mirroring and limited Clustering using Standard Edition. Another question. Would I benefit from buying Enterprise Edition as the system will be an OLAP Database?

Antares686 SSC Guru Points: 125444 More actions · Answer 7

I would do a technical design of the system and develope it out on paper first, cannot tell you which although there are bennefits of going straight to SQL 2005 if you plan to anyway long term. But to know if 2005 is a must if you are already on 2000 for other things I cannot answer without know all you want to do for sure. Wouldn't want to sell you on the idea to change your infrastructure at a cost if you can meet with your current in place one without a really good reason.