Wednesday, August 10, 2016

SQL Server 2016 - Its Just Faster

SQL Server 2016 is fully loaded with loaded with several new amazing features, including more speed, more security and more value. The SQL Server development team focused investment in performance and scale, simply upgrading to SQL 2016 could bring 25% performance improvement. SQL 2016 supports 3X more physical memory than previous versions.
In this section, we will learn that why SQL Server 2016 is become faster than previous versions - 
  1. DBCC Scales 7x Better - For DBCC operations, all previous version of SQL Server are based on MultiObjectScanner but SQL Server 2016 changes the internal design to (CheckScanner), applying no lock semantics. It supports a design similar to those used with In-Memory Optimized objects, allowing DBCC operations to scale far better than previous releases. 
  2. DBCC Extended Checks -This feature greatly reduces the duration of CHECKDB against databases containing these objects. In older versions of SQL server, running consistency checks (DBCC CHECKDB) on a database containing these (Filtered indexes, Persisted Computed columns, UDT columns and UDT columns based on CLR assemblies) can take significantly longer. Starting with SQL Server 2016, additional checks on filtered indexes, persisted computed columns, and UDT columns will not be run by default to avoid the expensive expression evaluation(s). However, the physical consistency checks of these objects is always completed.
  3. Native Spatial Implementation - To fully appreciate the ‘native implementation‘ gains in SQL Server 2016 it is helpful to understand the architecture of SQL Server Spatial data types. Spatial data is among, if not the fastest, growing storage types for the SQL Server database. Customers are tracking vehicles, delivery locations, drilling positions and much more, leveraging the information to efficiently run their business. The exposure of the Microsoft.SqlServer.Types .NET component provides applications with the rich set of spatial data types and methods.  The same methods leveraged by the SQL Server engine itself. SQL Server 2016 invokes the native implementation of the methods, avoiding the Unmanaged to Managed to Unmanaged transitions, improving performance.
  4. Spatial Index Builds Faster - 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. Testing reveals that building a spatial index on SQL Server 2016, with the improved design, can be more than 2 times faster than SQL Server 2012 or 2014 on the same data and hardware. 
  5. Automatic Soft NUMA- 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 activities and improve performance and scalability. During startup, SQL Server 2016 interrogates the hardware layout and automatically configures Soft NUMA on systems reporting 8 or more CPUs per NUMA node. The partitioning triggers various adjustments throughout the database engine improving scalability and performance. The Automatic Soft NUMA logic considers logical CPU ratios, total CPU counts and other factors, attempting to create soft, logical nodes containing  8 or fewer CPUs each.
  6. Dynamic Memory Object Partitioning - Dynamic Memory Object (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 be partitioned globally, by node or by CPU.  Taking a page from the latch design concepts, CMemThread is designed in SQL Server 2016 to dynamically promote a contended CMemThread object. SQL Server 2016 detects contention points on a specific CMemThread object and promotes the object to a per node or per CPU based implementation. Once promoted the object remains promoted until the SQL Server instance is restarted. SQL Server 2016 dynamically partitions a CMemThread encountering contention, increasing scalability of the instance.
The automatic, soft NUMA behavior is Hyperthread (HT/logical processor) aware. When determining the optimal node layout the logical CPU information is queried and used to prevent groupings of logical only and physical only nodes which could lead to performance variations across the nodes.
Its Just FasterOut of the box SQL Server 2016 DBCC provides you better performance, scale while shrinking your maintenance window(s.)


No comments:

Post a Comment