jwcolby
jwcolby at colbyconsulting.com
Thu Nov 4 05:49:09 CDT 2010
Well, the thing to remember is that this server could be very busy as it is. I have written about my 4 stage process for address validation, so I could have all kinds of processes running simultaneously. I believe that if I had processes exporting from SQL Server to CSV for address validation, other processes importing completed validation back in, as well as order processing, I would be keeping all cores very busy. John W. Colby www.ColbyConsulting.com On 11/4/2010 6:43 AM, Gustav Brock wrote: > 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 > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >