SQL 2016 – It Just Runs Faster: AlwaysOn Log Transport Reduced Context Switches
Moved from: bobsql.com 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 opportu …
SQL 2016 – It Just Runs Faster – BULK INSERT Uses Vector Instructions (SSE/AVX)
Moved from: bobsql.com 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 i …
SQL 2016 – It Just Runs Faster: Column Store Uses Vector Instructions (SSE/AVX)
Moved from: bobsql.com 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 …
SQL 2016 – It Just Runs Faster: Multiple Log Writer Workers
Moved from: bobsql.com 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 a …
SQL 2016 – It Just Runs Faster: Larger Data File Writes
Moved from: bobsql.com SQL Server uses WriteFileGather for the vast majority of data file write requests. The logic is to consolidate dirty pages into a single I/O request. For example page 1:13 and 1:12 are dir …
SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default
Moved from: bobsql.com There are two(2) distinct checkpoint paths provided starting with SQL Server 2014, referred to as Automatic and Indirect. The vast majority of documentation today highlights the behavior of automatic (classic) …
SQL 2016 – It Just Runs Faster: SOS_RWLock Redesign
Moved from: bobsql.com The SOS_RWLock is a synchronization primitive used in various places throughout the SQL Server code base. As the name implies the code can have multiple shared (readers) or single (writer) ownership. …
SQL 2016 – It Just Runs Faster: Dynamic Memory Object (CMemThread) Partitioning
Moved from: bobsql.com The CMemThread waits (PWAIT_MEMTHREAD) can be a point of contention as machine sizes advance. The CMemThread object type is utilized in 100s of objects throughout the SQL Server code base and can …
SQL 2016 – It Just Runs Faster: Automatic Soft NUMA
Moved from: bobsql.com As hardware continues to expand and evolve SQL Server testing and customer reports have highlighted the need to partition activities for optimal scaling. Partitioning based designs are common ways to localize ac …
Introducing new and improved updates to SQL tools on Azure
In this post, we’ll recap the updates to SQL tools over the past few months, including the recent releases of Azure Data Studio and SQL Server Management Studio (SSMS). Additionally, we’ll highlight the Data-Tier Application Framework (DacFx) 160 gener …
SQL 2016 – It Just Runs Faster: Instant File Initialization
Moved from bobsql.com Database Instant File Initialization was added several SQL Server releases ago. The instant file initialization feature scales the creation and expansion (growth) of database, DATA files. …
SQL 2016 – It Just Runs Faster: LDF Stamped
Moved from: bobsql.com When creating or growing the database log file (LDF) a byte pattern is stamped. The pattern establishes the proper log block layout with end of log marker(s.) SQL Server 7.0 changed the LDF format from th …
SQL 2016 – It Just Runs Faster: Automatic TEMPDB Configuration
Moved from: bobsql.com Various KBs, whitepapers and blogs have outlined the need for the creation of multiple, TEMPDB files, same sized files, trace flags and the like. All of these configuration options increase the scalability …
SQL 2016 – It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases
Moved from: bobsql.com Configuration of TEMPDB is often critical to scalability and throughput of SQL Server applications. The following link (https://support.microsoft.com/en-us/kb/2964518) outlines how to configure SQL Server 2014 a …
SQL 2016 – It Just Runs Faster: Spatial Index Builds Faster
Moved from: bobsql.com Index creation and tessellation are often intensive, spatial activities. Along with the native and TVP, spatial enhancements additional work to optimize index creation and tessellation was completed. …