[dba-SQLServer] Comparative timings

John,
  Just curious but did you shut down the instance after each change or clear
the buffers and cache to ensure proper benchmarking?

You can clear the cache by running DBCC DROPCLEANBUFFERS,  Then run DBCC
FREEPROCCACHE, which clears the stored procedure cache.

-Francisco
http://bit.ly/sqlthis   | Tsql and More...




On Wed, Nov 3, 2010 at 6:22 PM, jwcolby <jwcolby at colbyconsulting.com> wrote:

> 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
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>