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.

Advertisements

SQL Server 2016 CTP 2.2

http://blogs.technet.com/b/dataplatforminsider/archive/2015/07/22/sql-server-2016-upgrade-advisor-preview-and-ctp-2-2-now-available.aspx – SQL Server 2016 Upgrade Advisor Preview and CTP 2.2 now available

http://blogs.msdn.com/b/mds/archive/2015/07/23/what-s-new-in-master-data-services-sql2016-ctp2-2-july-release.aspx – What’s New in Master Data Services – SQL2016 CTP2.2(July) Release

http://sqlserver-qa.net/2015/07/23/sql-server-2016-community-technology-preview-2-2/ – CTP2 will also have improvements to the Query Store, Query Execution, DBCC CHECKCB performance, and Temporal Database.

“There is an issue with the SQL Server 2016 CTP 2.2 release that causes downtime during rolling upgrades across an AlwaysOn Availability Group.  If you are not using AlwaysOn, you will not experience the issue. We recommend that AlwaysOn users not upgrade to CTP 2.2 at this time.  A replacement build of SQL Server 2016 CTP 2.2 is in process and will be available.”

http://go.microsoft.com/fwlink/?LinkID=398124 –  CTP 2.2 release notes

soon. For more information,