Multi Core Processor needs different SQL License? 1

[GrandMauler]

Here's an interesting question:

My DBA installed MSSQL 2k5 on a multi-core processor server.

a.) does it need a differenct license?
b.) will a multi core license make it operate any faster?

We have discovered that SQL 2k5 doesn't always optimize in an multi-core environment. I'm wondering if the licensing configuration could be the cause.

 

[ptheriault]

It depends on which license type you purchased.

http://www.microsoft.com/sql/howtobuy/default.mspx

will a multi core license make it operate any faster?

No, It will use what ever processors you have configured it for. If you haven't purchases a license then it will run without a license.

We have discovered that SQL 2k5 doesn't always optimize in an multi-core environment.

Can you be more specific about what you have noticed?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[onpnt]

As of my latest viewing of the license agreements Microsoft does NOT view multi-core processors as separate CPU licensing requirements.

SQL Server 2005 utilizes threading more than any other version of SQL Server and explicitly does so on its own (without you being able to). This gains exceptional optimization and will undoubtedly be better than a single core processor.

There is no configuration that would directly affect performance of the database server. It is more likely a bad configuration in from the start.

Like Paul said you will need to be much more detailed in why you posted those hard/fast comments

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[GrandMauler]

Only that when I run an sql script that takes 2 hour on a single core system it takes 2 hours where as it takes 3 hours on a quad core system. On the single core system, it uses 100% of the processor, where as in a quad core, it used 25% of each core.


The scrip is extremely I/O intensive as it generates a rather large file.

The single core system has a speed of 3 ghz, whereas the quad cores have a speed of 2ghz

 

[onpnt]

Is memory and disk configuration the same on these two systems also?

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[ptheriault]

The single core system has a speed of 3 ghz, whereas the quad cores have a speed of 2ghz

Is the quad core system older? What's the bus speed? Did you change the MAXDOP on the quad core and re-run it? What were the results.

*MAXDOP is the max degree of parallelism. You can tell a query to use only one processor or you can change it at the server level for all queries.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[GrandMauler]

Is memory and disk configuration the same on these two systems also?

No. The single core has 3 gigs of memory. The quad has 16 gigs of memory. I think the quad has 8 gigs allocated to SQL. I don't know what that is for the single core.

Is the quad core system older? What's the bus speed? Did you change the MAXDOP on the quad core and re-run it? What were the results.

The quad is brand new. Dunno what about the other stuff as I'm not the Sys Admin. He's the one managing it. I just order stuff. But I will pass this along .

 

[SQLDenis]

first thing to do is create 4 files for temptb (1 for each logical CPU)
This should speed up things dramatically

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[GrandMauler]

first thing to do is create 4 files for temptb (1 for each logical CPU)
This should speed up things dramatically

My Sys Admin's next question is: How do you attach a temptdb to a core?

 

[ptheriault]

Right click on the Tempdb and select properties -> files and add a datafile. By default SQL Server will use one for each CPU.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

you don't attach anything to a core just make sure you have 4 files, here is some more info

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

look at # 7 and #8



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[GrandMauler]

Denis, our Sys Admin says your suggestion has merit and will explore fully.

As such, I've given you a star.


Cheers!

 

[mrdenny]

It sounds like your sysadmin needs to read through some of the forums and FAQs on this site. There are many related to proper system configuration as it relates to the disks, memory as well as licensing of the SQL Server in a multi-core system.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[GrandMauler]

And I will eventually make those available to him. I have suggested he visit Tek-Tip Forum.

But, as the old saying goes, "you can lead a sys admin to Tek-Tip Forum, but you can't make him read it... unless, of course, you have a gun pointed at him, in which case he'll probably read it... maybe...."


At least, that's what old grand dad used to say...