[dba-SQLServer] New system building an order

Today I received another 16 GB ram and put in the SQL server.  I now have a single CPU / 8 cores and 
32 gigs of memory.  I assigned 27 Gigs to SQL Server and left 5 to the system.

I decided to test the new server building a moderately complex order.  This order pulls about 5 
million OrderData records from a series of views.

_DataAllAdults.tblAllAdultsNameAddr table has a clustered index on the PKID and an NameAddress cover 
index which covers all of the name / address fields as well as the gender and AddressValid flag.

vAllAdults selects records from that table which have specific codes in the ValidAddr vield.

vAllFemales selects records from vAllAdults where the gender is 'F'.

_DataHSID holds the database from hell, the tblHSID with ~50 million records with ~600 fields.  Ut 
has a variety of cover indexes on it which cover specific groups of fields.

vHSIDOrderCriteria joins vAllFemales and the DataHSID.tblHSID on the PKHSID, and uses a where clause 
which selects specific values from specific fields.

Under most circumstances I just edit and save vHSIDOrderCriteria to select the correct HSID fields / 
values to pull the desired records.

I then run an external C# program which dynamically builds the tblOrderData using the field list 
from vHSIDOrderCriteria (which obviously changes from order to order) and then populates that temp 
table with the data pulled using vHSIDOrderCriteria.

In this case, ~5 million records were selected and stored in tblOrderData.

This is the first time ever that I have had more than 2 cores and about 12 gigs of memory to use to 
run the order.  I do not have any timing information for past runs.  However what I observed is that 
processing the order caused all 6 cores assigned to SQL Server to almost max out for the first part 
of the order process.  It wasn't flat line at the top but it was in the 90+ % utilized for all 6 
cores, for part of the time - perhaps 40% of the duration.  The rest of the time it maxed out a 
single core. and partly used another.

And it used 29+ gigs of memory during use.

This order process is the first step in processing an order and takes awhile to complete.  I now 
have _DataAllAdults and _DataHSID on SSD.  This is the first time I have ever seen all six cores 
close to max.

No promises, but just out of curiosity I am going to try and "recreate" the old system, with a copy 
of _DataAllAdults and _DataHSID on rotating media, assign 12 gigs of memory as in the "olden days" 
and then do a simple stopwatch timing of the two.  It really won't be even close to the old system 
because the old CPU was a quad core at 3 GHZ and this one is 8 cores at 2 GHZ.  In the old system I 
assigned 2 cores to SQL Server, and it would often max out both cores.  This one I am assigning 6 
cores and it runs about 9% of all six cores at times.

None the less it should give a feeling for the relative speed with all the changes.  Because of the 
way I layer the views it should be fairly easy to build a copy database and then modify two specific 
views to point to the copy of data on rotating media.  After that it is really just a matter of 
running it twice, once for each order database copy.

-- 
John W. Colby
www.ColbyConsulting.com