SQL Server and CPUs

Intel CPUS

Intel ARK is the place to get information on Intel CPUS http://ark.intel.com/products/family/78583/Intel-Xeon-Processor-E5-v3-Family#@All

Most cores/cache I can see is 18 cores,45MB Cache –

http://ark.intel.com/products/85766/Intel-Xeon-Processor-E5-4669-v3-45M-Cache-2_10-GHz

Before than 15 core with Last Level Cache (LLC) co-located per core for a total of 37.5MB shared L3 cache

http://www.theregister.co.uk/2014/02/18/intel_releases_mission_critical_two_four_and_eight_socket_xeon_e7_v2_line/

Intel did release 60 core CPUS with place like Cambridge University getting early access – http://www.cnet.com/news/intels-60-core-chip-ships-elites-like-hawking-get-it-first/

SQL Server

http://exadat.co.uk/2015/05/25/under-the-hood-of-the-batch-engine-numa-support-part-2/

– Data gets to the columnstore via memcpu from the bufferpool!

– The column store object pool stores BLOB pages contiguously

http://exadat.co.uk/2015/04/17/sql-server-and-hyperthreading/

Intel i7 has NEW hyperthreading – up to 30% performance improvement

http://exadat.co.uk/2015/06/11/writelog-at-scale-going-beyond-you-need-faster-disks/

SQL Server has a finite number of log writes it can queue prior to being flushed to disk, 32 for SQL Server 2008/2008R2 and 212 for SQL Server 2012 onward.

When switching to a new VLF it has to be “formatted” via an 8K SYNC write, whilst this happens transactions are blocked.

http://exadat.co.uk/2015/07/26/running-sql-server-on-a-big-box-quantifying-the-effect-of-numa-on-oltp-workloads/

Both the log buffer and the cache entry associated with LOGCACHE_ACCESS spinlock need to travel from core 0 on CPU socket 0 to the local CPU cache of the core that the thread generating logging information is running on, in order that the thread can use the log buffer. The thread will write to both the LOGCACHE_ACCESS spinlock and the log buffer before it is returned to the CPU core that the log writer is running on.