Gustav Brock
Gustav at cactus.dk
Thu Nov 4 01:38:50 CDT 2010
Hi John So 3 cores 12 GB runs at a speed only ~10% higher than that of 6 cores 24 GB. Interesting. Could you only run two instances ... /gustav >>> jwcolby at colbyconsulting.com 04-11-2010 02:22 >>> I am processing an order for the client that i built this server for. One of the steps is to count the available records. The task, select 640K records from two related tables, 65 million names / addresses joined to 50 million records of demographics (the database from hell). Filter down to (male, high income, young) and (has kids or mail order buyers). The selection views return full name / address plus selection fields. Both tables have a clustered index on integer PK. The tables are joined on PK/FK (the key of the clustered index). Cover indexes on the selection fields. The tables are in separate databases, both databases on the same SSD (2 drive raid 0). The count simply counts the PKID of the selection view. The server was configured with 6 of 8 cores available to SQL Server and 24 gigs of RAM. The count ran consistently around 0:1:55 (one minute 55 secs). The system was only running the 6 cores around 20% -25% of capacity. I cut the processors assigned to 3 of 8 and reran the same count. The three assigned processors ran about 80% of capacity, but several of the processors not assigned to SQL Server also ran something, averaging about 30-40% of capacity. The time to do the count was about 3:36. I then cut the memory assigned to SQL Server to 12 gigs with 3 cores assigned. Again, the processors ran very similar to the last run, the three assigned to SQL Server ran around 60-80% but a couple of the other cores not assigned also did something significant - 30-40%. The time to do the count was 2:06. I then assigned 6 processors but 12 gigs of memory. The 6 processors assigned averaged around 80% for much of the time, but the total time was 1:53. I then jacked the memory back up to 24 gigs / 6 processors. Average core utilization dropped, the total time was 2:01. Just to see if it was an anomaly I dropped back down to 3 procs with 24 gigs of memory. 4:00 to process the count. And finally back to 12 gigs and 3 procs. 2:00 So 12 gigs and 3 cores produced equivalent results to 6 cores and 24 gigs (which I find fascinating and disturbing). 3 cores and 24 gigs put on a very poor show. And of course this test did not have the server doing anything else. I had intended to run a VM on the server though I am changing my mind. In preliminary tests, the vm did not perform as well as on the previous server. I believe it is probably a simple matter of clock speed. This server has 8 cores but they are clocked at 2 gigs. My previous server had only 4 cores but they were clocked at 3.2 gigs. The VM has always shown the best results with a single core and if the core is faster... So I will likely rebuild a server to just hold the vm. -- John W. Colby www.ColbyConsulting.com