SQL 2016 – It Just Runs Faster: Multiple Log Writer Workers

  SQL Server 2016 introduces multiple log writer workers to the engine.    For many years the log device was limited by the capabilities of spinning media and hardware caches.    The advancement of hardware caches and fast storage types (SSD, flash, …) on large installations can saturate a single log write worker.   SQL Server 2016…

4

SQL 2016 – It Just Runs Faster: Column Store Uses Vector Instructions (SSE/AVX)

SQL Server’s Column Store feature is designed to accommodate large amounts of data and associated compression capabilities.  Microsoft studied numerous data layouts associated with column store and the CPU instructions required to process that data.   Various algorithms have been optimized throughout the column store code base.   A specific, SQL Server 2016  enhancement is the…

2

SQL 2016 – It Just Runs Faster – BULK INSERT Uses Vector Instructions (SSE/AVX)

SQL Server 2016 enhanced the bulk insert activities (BULK INSERT), leveraging the CPUs vector instructions.   The vector based approach allows the bulk data to be processed faster.   For example, when inserting data from a text file the integer conversion leverages SSE instructions when the character length is more than 4 characters and trace flag…

0

SQL 2016 – It Just Runs Faster: AlwaysOn Log Transport Reduced Context Switches

The AlwaysOn log transport uses a SQL Broker based design to send and receive messages between the primary and secondary replicas.    Studying the transport behavior revealed various improvement opportunities.   Many of you have read the various materials indicating that log transportation between the primary and secondary is commonly 1/3 that of file copy speed.  …

0

SQL 2016 – It Just Runs Faster: AlwaysOn Parallel Compression / Improved Algorithms

SQL Server 2016 introduces two distinct changes in the AlwaysOn transport, compression design.   Improved compression algorithms Parallel compression of log block data   Compression can be performed faster, using less resource overhead and maintains compression ratios.   SQL Server 2016 also introduces parallel compression operations.  The following chart outlines the performance and resource gains…

2

SQL 2016 – It Just Runs Faster – AlwaysOn AES-NI Encryption

The SQL Server 2016, AlwaysOn, log transport takes advantage of hardware based encryption to significantly improve scale and performance.   SQL Server 2016 development efforts continued focusing on the AlwaysOn log shipping transport.  Testing revealed that software based encryption, while fundamentally sound, could be improved using hardware based capabilities.   The exchange of information between…

0

SQL 2016 – It Just Runs Faster: In-Memory Optimized Database Worker Pool

SQL Server leverages a worker pool for the In-Memory Optimized Database capabilities.  Prior to SQL Server 2016, increasing the size of the pool required you to enable a startup trace flag.   SQL Server 2016 removed the trace flag and updated the design to dynamically adjust the size of the In-Memory Optimized Database worker pool…

0

SQL 2016 – Leverages On Demand MSDTC Startup

SQL Server leverages MSDTC for distributed transactions (begin distributed transaction, remote proc trans, etc.)   Prior to SQL Server 2016 the MSDTC service must be running (started) prior to any SQL Server, DTC based transaction activity.   SQL Server 2016 enhances SQL distributed transaction capabilities leveraging MSDTC, On Demand startup.  The On Demand startup of MSDTC…

0

SQL 2016 – It Just Runs Faster: XEvent Linq Reader

SQL Server 2016 improves the XEvent Linq reader scalability and performance.    The XEvent UI in SQL Server Management Studio uses the XEvent Linq reader to process the events for display.   Careful study of the XEvent Linq reader revealed opportunities for scalability and performance improvements.     Note:  The XEvent Linq reader is .NET based and…

0