Gustav Brock
Gustav at cactus.dk
Thu Nov 4 05:43:25 CDT 2010
Hi John I meant running the same job. But then you should be able to split it somehow. /gustav >>> jwcolby at colbyconsulting.com 04-11-2010 11:07 >>> Gustav, AFAIK I could run two instances. John W. Colby www.ColbyConsulting.com On 11/4/2010 2:38 AM, Gustav Brock wrote: > 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. > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com