[dba-SQLServer] Comparative timings

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