I have found the term DTC is used in many ways so for the purposes of the discussion here are a few terms. When talking DTC, I am talking about the feature set, MSDTC as the OLE-TX implementation and XA for the X/Open XA implementation. DTC Distributed Transaction Coordination – Generic term MSDTC Microsoft specific […]
Read MoreBob Ward and I worked with our SQL Server Tool developers (thanks David) to enable ‘Quick XE Trace’ capabilities. The feature is available in the latest SQL Server Management Studio (SSMS) release. Despite the deprecation of SQL Profiler several years ago, as well as various documents and blogs pointing out the older trace facilities shortcomings […]
Read MoreBack in June, Bob Dorr gave you the thinking behind why SQL Server 2016 It Just Runs Faster due to changes in our SQL Server and NUMA configurations. Bob blogged about a new SQL Server 2016 feature called Auto Soft NUMA. As I’ve hit the road and talked more about SQL Server 2016: It Just […]
Read MoreNote: We received feedback that there was some confusion on us calling this functionality “tail of the log caching” because our documentation and prior history has referred to the tail of the log as the portion of the hardened log that has not been backed up. This feature is actually officially called Persisted Log Buffer […]
Read MoreBack in July, I told you about a new installation experience for SQL Server. One of our goals from the outset was to ensure we could iterate and enhance this experience as we obtained feedback from the community. Today, we are proud to announce Version 2 of the new SQL Server Installer. Here is the […]
Read MoreWhen we released Always On Availability Groups in SQL Server 2012 as a new and powerful way to achieve high availability, hardware environments included NUMA machines with low-end multi-core processors and SATA and SAN drives for storage (some SSDs). Performance issues surrounding Availability Groups typically were related to disk I/O or network speeds. As we […]
Read MoreSQL Server 2016 ‘It Just Runs Faster’ A bold statement that any SQL Server professional can stand behind with confidence. My development collogues and I are starting a regular blog series, outlining the vast range of scalability improvements, allowing SQL Server 2016 to run across a wide array of hardware configurations, faster and better […]
Read MoreMany of you have experienced (MULTI_OBJECT_SCANNER* based) waits while running DBCC CHECKS*(checkdb, checktable, …) Internally DBCC CHECK* uses a page scanning coordinator design (MultiObjectScanner.) SQL Server 2016 changes the internal design to (CheckScanner), applying no lock semantics and a design similar to those used with In-Memory Optimized (Hekaton) objects, allowing DBCC operations to scale […]
Read MoreLast week’s post (SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better) talked about several improvements to DBCC CHECKDB to make it run faster. In today’s post, we will talk about additional improvements to extended logical checks. When checking database consistency using DBCC CHECKDB, in addition to the amount of data or number […]
Read MoreSpatial data is among, if not the fastest, growing storage types for the SQL Server database. It is common place for customers to have 3,000,000 or more rows in a database. Customers are tracking vehicles, delivery locations, drilling positions and much more, leveraging the information to efficiently run their business. To fully appreciate the […]
Read MoreTable Valued Parameters (TVPs) containing spatial columns can be used as input parameter(s) to stored procedures. SQL Server 2016 improves the scalability, using native spatial validation(s), increasing performance by 15 times or more. TVP Before the Fix: 8000 rows/sec TVP After the Fix: 120,000 rows/sec ‘It Just Runs Faster’ – Apply SQL Server […]
Read MoreIndex 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 […]
Read MoreConfiguration 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 and 2012 for optimal scalability and performance. A SQL Server 2016 primary goal was ‘It Just Works.’ Out of the box a customer should not have to engage in […]
Read MoreVarious 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 of your SQL Server. In an effort to simplify the tempdb configuration experience, SQL Server 2016 setup has been extended to configure […]
Read MoreWhen 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 the original, 2K, Sybase database page design to sector aligned log blocks stamped with all zeros (0x00). Creation or […]
Read MoreDatabase 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. The ‘Manage Volume Privilege’ option is off by default preventing many SQL Server installations from taking advantage of the feature. SQL Server 2016 Setup provides the option to […]
Read MoreAs 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. An example of how SQL Server leverages partitioning is the CMemThread object. For thread safety various synchronization […]
Read MoreSQL Server 2016 gets a scalability boost from scheduling updates. Testing uncovered issues with the percentile scheduling based algorithms in SQL Server 2012 and 2014. A large, CPU quantum worker and a short, CPU quantum worker can receive unbalanced access to the scheduling resources. Take the following example. Worker 1 is a large, read […]
Read MoreThe 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. The vast majority of CMemThread objects leverage global partitioning. Trace flag -T8048 only forces […]
Read MoreThe 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. Studying the SQL Server 2012 and 2014 implementation of the SOS_RWLock we found the core acquisition and wait list could be optimized. SQL […]
Read MoreThere 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) checkpoint. This post outlines some historical aspects of checkpoint and provides the recommendation to leverage Indirect Checkpoint. Before SQL Server 7.0 The database […]
Read MoreSQL 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 dirty resulting in a single WriteFileGather operation. Is BUF[1] Dirty – Yes – Write with gather near Do hash […]
Read MoreSQL 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 […]
Read MoreSQL 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 […]
Read MoreSQL 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 […]
Read MoreThe 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. […]
Read MoreSQL 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 […]
Read MoreThe 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 […]
Read MoreSQL 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 […]
Read MoreSQL 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 […]
Read MoreSQL 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 […]
Read More