| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-10-30 : 02:53:47
|
| Using Task Manager : CPU Performance Graph I see 8 CPUs showing on my server. I expect its 2 sockets x 4 cores. The graph only shows activity on the first 4 CPUs. I always assumed it was something to do with the fact that its a virtual machine and the second CPU wasn't "turned on"However, I just ran some hefty Zipping tasks and noticed that CPU's 5-8 were busy - I then set Affinity for the two instances of WinZip to CPU's 5-8 and it seemed happy, and the CPU performance graph looked right (CPUs 5-8 very busy, and CPU 1-4 about 20-30%)So why aren't CPUs 5-8 being used by SQL?SQL Server Properties shows that CPU affinity is set for all CPUs. Could it be a licence issue?Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft CorporationWeb Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-10-30 : 09:27:11
|
| Don't know if this is the reason (I gave up trying to keep up with editions long ago) but checkhttp://www.microsoft.com/sqlserver/2008/en/us/editions.aspxIt shows web edition as utilising a max of 4 cpus==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-30 : 17:41:52
|
| Thanks Nigel, licensing not my department either, but your linked document seems to confirm it.Do you think I would be better setting CPU affinity across two physical processor (2 cores from one, and 2 core from another) rather than letting it have all four cores from just one CPU?The second 4 cores seem to have so little work to do I wonder if O/S is favouring the first 4 cores, and thus setting SQL's Affinity to the second 4 might help.Or spreading it across two physical CPUs might be more effective than using all cores in one CPU. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-30 : 17:50:43
|
| P.S. We only have one Database on this server, so only one SQL Instance - so no opportunity to use some-CPUs for Instance-One, and other-CPUs for Instance-Two |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-30 : 21:21:31
|
| Don't change affinity until you've thoroughly tested. There is rarely a need to override this.If you're running SE, then yeah, 4 CPU max, but I wouldn't change affinity until I was certain there's a good reason to do sohow many physical CPUs do u have btw? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-31 : 02:28:38
|
| "how many physical CPUs do u have btw?"TBH I'm not sure. The machine is virtualised, although perhaps that doesn't make a difference. Task Manager Performance graph shows 8, and I assumed that was 2 sockets, 4 cores each - but it might be 4 Sockets with twin-cores. I'll ask the hardware-team. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-31 : 04:57:05
|
| Perhaps begs the question:Why don't we just use one machine for both IIS and SQL, rather than having dedicated servers - 4 CPUs for each task ... ? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-31 : 11:00:00
|
| Too much resource contention. Both iis and sql expect the undivided attention of the cpu(s) and both hog up memory without releasing it (by design). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-31 : 16:55:34
|
| Yeah, I'm sure you are right. Could run two virtual machines on one physical box - 4CPUs each and as much RAM, for each, as they need. Less rack space ... Is the Licence for physical CPUs / Sockets or for Cores? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-31 : 17:35:06
|
| Sockets. What that means for virtualisation, I don't know. I am not a licensing expert (by choice)--Gail ShawSQL Server MVP |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2010-10-31 : 20:04:24
|
quote:
Originally posted by nigelrivett
Don't know if this is the reason (I gave up trying to keep up with editions long ago) but checkhttp://www.microsoft.com/sqlserver/2008/en/us/editions.aspxIt shows web edition as utilising a max of 4 cpus
Hey man welcome back!Why are you not using your old handle?___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!SQL Server MVP |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-10-31 : 20:36:36
|
| I use this everywhere else so I thought I'd harmonise.Just realised the site works on mobiles - not slow though.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2010-10-31 : 20:55:42
|
maybe we should tell graz to just change the old one to the new one?would be a shame for all those posts go to waste.  ___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!SQL Server MVP |
|
|
|
|
Processor Affinity