[dba-SQLServer] Comparative timings

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.
>