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. …
SQL 2016 – It Just Runs Faster: TVPs with Spatial Column(s)
Moved from: bobsql.com Table 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 performa …
SQL 2016 – It Just Runs Faster: Native Spatial Implementation(s)
Moved from: bobsql.com Spatial 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 t …
SQL 2016 – It Just Runs Faster: DBCC Extended Checks
Moved from: bobsql.com Last 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 improvement …
SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better
Moved from: bobsql.com Many 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.) …
How It Works: SQL Server 2016 SSE/AVX Support
Moved from: bobsql.com My recent posts https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-column-store-uses-vector-instructions-sseavx/ https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster- …
How It Works: How SQL Server Determines Logical and Physical Processors
Moved from: bobsql.com SQL Server, as outlined in the following post (https://blogs.msdn.microsoft.com/psssql/2016/03/30/sql-2016-it-just-runs-faster-automatic-soft-numa) adjusts to various processor configurations. I have received questio …
Unresolved Deadlock vs Scheduler Deadlock
Moved from: bobsql.com These are distinct errors but I have found that many people blend them together. It really takes an entire chapter to explain these conditions so I am going try to do it in a page or two? Unresolved Deadlock A …