Transcript Checkpoint - SQL Saturday

SQL Saturday #512
Boas Vindas
Freddie Santos
Senior Support Engineer - Microsoft
Speaker Bio
Frederico Santos ou Freddie, atua como Senior
Support Engineer na Microsoft.
Trabalha a mais de 15 anos com Tecnologia da
Informação, dos quais a quase 9 dedicados a
sistemas de banco de dados em especial SQL
Server, desde versões 7.0 até 2016.
Frederico Guimarães
Atualmente provê suporte ao Microsoft Analytics
Platform System (PDW) para empresas Forbes
500 clients da Microsoft.
Além disso adora passar seu tempo livre
jogando video game, ou brincando com seus
dois filhos Nathanael e Estevão.
Não tenho, o que é isso?
https://www.linkedin.com/in/fredguis
[email protected]
Não tenho, mas é uma boa ideia;
Patrocinadores
Links
 http://facebook.com/devSQL
 [email protected]
 SQL Server Discovery Day
 http://www.eventbrite.com/e/sql-discovery-daytickets-25185568714
 Utilize nossa hashtag no twitter: #SqlSatRJ
SQL Server 2016: It Just Runs Faster
 The release of SQL Server 2016 focus on some new
features: Query Store, Temporal Tables, Live Query Debug,
etc)
 But additionally we introduced some improvements in
existing features, and database engine.
SQL Server 2016: It Just Runs Faster
 Prior to SQL Server 2016, most workloads require some
tuning effort:
 Activate Instant File Initialization
 Adjust TempDB configuration
 Turn on trace flags
 Change configuration parameters
Recommended updates and configuration options for SQL
Server 2012 and SQL Server 2014 with high-performance
workloads (2964518)
SQL Server 2016: It Just Runs Faster
 Trace flag 4199 hotfixes made to previous releases will be
enabled under compatibility level 130
 Trace flag 4199 will be used to release any future hotfixes
for databases under compatibility level 130
SQL Server query optimizer hotfix trace flag 4199 servicing
model (974006)
SQL Server 2016: It Just Runs Faster
SQL Server 2016: It Just Runs Faster
 Scales the creation and expansion of data files
 Requires ‘Manage Volume Privilege’ option,
which is off by default
SQL Server 2016: It Just Runs Faster
SQL Data File
0000
SQL Server 2016: It Just Runs Faster
SQL Data File
SQL Server 2016: It Just Runs Faster
 SQL Server 2016 setup provides the option to enable
‘Perform Volume Maintenance Task’
SQL Server 2016: It Just Runs Faster
 Lots of papers, KBs and blogs outline the need
to reconfigure tempdb for scalability
 Dedicated drive
 Multiple data files, same size and auto-growth
settings
 Trace flag 1117 (all data files grow at the same time)
 Trace flag 1118 (no mixed extents)
SQL Server 2016: It Just Runs Faster
 SQL Server 2016 setup provides a new tab for tempdb
configuration
 Multiple files and directories
 Same auto-growth settings
 TFs 1117 and 1118 on by default
SQL Server 2016: It Just Runs Faster
 Trace flag 1118 on by default, replaced by a new ALTER
DATABASE setting
 ALTER DATABASE AdventureWorks SET
MIXED_PAGE_ALLOCATION ON
 Trace flag 1117 off by default, can be set at the FILEGROUP
level
 ALTER DATABASE <dbname> MODIFY FILEGROUP
<filegroup> { AUTOGROW_ALL_FILES |
AUTOGROW_SINGLE_FILE }
SQL Server 2016: It Just Runs Faster
 Initial size is now 8 MB, auto-growth for data and log files is
now 64 MB (same for tempdb)
 Optional configuration values that affect the code at the
database level (Database Scoped Configurations)
 ALTER DATABASE SCOPED CONFIGURATION
SQL Server 2016: It Just Runs Faster





Clear the procedure cache
Configure MAXDOP
Set the cardinality estimation model
Enable or disable parameter sniffing
Enable or disable query optimization hotfixes
 It was released on Azure Database (SQL Azure – First)
SQL Server 2016: It Just Runs Faster
ALTER DATABASE SCOPED CONFIGURATION
{
{ [ FOR SECONDARY] SET <set_options> }
}
| CLEAR PROCEDURE_CACHE
[;]
< set_options > ::=
{
MAXDOP = { <value> | PRIMARY}
| LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}
| PARAMETER_SNIFFING = { ON | OFF | PRIMARY}
| QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}
}
SQL Server 2016: It Just Runs Faster
 Since SQL Server 7.0, log blocks are sector aligned and
stamped with all zeroes (0x00)
 Many HW implementations detect patterns of 0x00’s and
reclaim these blocks
 Hardware reclamation can force new transaction log records
to repeat acquisition of space
 SQL Server 2016 changes the stamp to 0xC0
SQL Server 2016: It Just Runs Faster
SQL Data File
0000
Additional IO!!!
SQL Server 2016: It Just Runs Faster
 CMEMTHREAD waits can be a point of contention as
machine sizes advances
 TF 8048 only forces CPU partitioning, each time a
contended CMEMTHREAD is found, a hotfix is required to
partition it
 In SQL Server 2016, contended CMEMTHREAD objects are
automatically partitioned per node, and then by CPU
SQL Server 2016: It Just Runs Faster
SQL Server 2016: It Just Runs Faster
SQL Server 2016: It Just Runs Faster
SQL Server 2016: It Just Runs Faster
 A large and a short CPU quantum worker can
receive unbalanced access to scheduling
resources
 Previous SQL Server versions rely only on load
factor and NUMA nodes
 SQL Server 2016 monitors the quantum usage
patterns allowing workers to get fair treatment
SQL Server 2016: It Just Runs Faster
Thread:
Scheduler:
CPU0
CPU1
CPU2
CPU3
SQL Server 2016: It Just Runs Faster
SQL Server 2016: It Just Runs Faster
SQL Server 2016: It Just Runs Faster
 During SQL Server 2000 / 2005 development, 8 CPUs per
NUMA node was a high-end system
 At startup, SQL Server 2016 configures Soft-NUMA on
systems with 8 or more CPUs per NUMA node
 Benefits: More IOCP Threads, Lazy Writer Threads.
SQL Server 2016: It Just Runs Faster
SQL Server 2016: It Just Runs Faster
SQL Server 2016: It Just Runs Faster
Soft NUMA
Soft NUMA
Soft NUMA
Soft NUMA
SQL Server 2016: It Just Runs Faster





Auto-update Stats
In-Memory Optimized Database Worker Pool
Use up to 4 sockets
Scaling storage (multiple offline checkpoint threads)
Parallelism
SQL Server 2016: It Just Runs Faster
~ 1GB
Offline Checkpoint Thread
Log
100-200
201-300
Memory-optimized data Filegroup
301-400
SQL Server 2016: It Just Runs Faster
~ 1GB
Offline Checkpoint Thread
Log
Offline Checkpoint Thread
One Thread per container
100-200
201-300
Offline Checkpoint Thread
SQL Server 2016
Memory-optimized data Filegroup
301-400
SQL Server 2016: It Just Runs Faster
 Low CPU consumption, and much better performance
 Very high transaction rates with fast hardware (i.e. PCI-E
Flash storage)
 Fast performance on Redo Thread.
 The bar we’re using is not % better than SQL2014. The bar
is how close we are to the perf of a standalone server.
SQL Server 2016: It Just Runs Faster
Changes on recompilation treshold of statistics.
 Up to SQL Server 2014:
 Existing index statistics are automatically updated at query compile
time if the modification counter is greater than the recompilation
threshold (20% R + 500R)
 SQL Server 2016
 Recompilation Treshold changed, the new behavior now is:
RT to min(default RT,
𝑅 × 1000) where R=table rows
SQL Server 2016: It Just Runs Faster
• SQL Server 2014 (X64 installations) increase the number of
contiguous, 8K pages from 16 to 32 (256K) for write
operations.
• Storage subsystems on “old” systems are good once disk
blocks has 64k to 128k.
SQL Server 2016: It Just Runs Faster
• SQL Server 2016 (X64 installations) increase the number of
contiguous, 8K pages from 32 to 128 (1MB) for following
operations:
• Lazy Writer
• Checkpoint
• Create Index
• Bulk Insert
These write operations encompass 95%+ of the write operations for data file
SQL Server 2016: It Just Runs Faster
• Default will pace
by I/O responsiveness
based on heuristics
Checkpoint
– SQL 7 to 2014
for spinning media (DAS) response times
• SQL Server 2008 16 dirty pages, whenever possible, by page
id and flushed them in a single write.
• SQL Server 2012/14 gather 32 dirty pages, whenever
possible.
• For SQL 2016 that value has been increased to 128 pages.
SQL Server 2016: It Just Runs Faster
Checkpoint – SQL 2016
• Indirect checkpoint is the recommended configuration,
especially on systems with large memory footprints and
default for databases created in SQL Server 2016.
SQL Server 2016: It Just Runs Faster
250 are positive
SQL Server 2016: It Just Runs Faster
 In SQL Server 2016, DBCC CHECK* uses a new page
scanning coordinator design (CheckScanner)
 This new design scales and performs better than the old
design (MultiObjectScanner), shrinking maintenance
windows
 MAXDOP option has been added to CHECKTABLE,
CHECKDB, and CHECKFILEGROUP
SQL Server 2016: It Just Runs Faster
SQL Server 2016: It Just Runs Faster
 Starting with SQL Server 2016, only physical checks will be
run against the following objects:
 Filtered indexes
 Persisted computed columns
 UDT columns
 Extended checks only with the
EXTENDED_LOGICAL_CHECKS option
 This change greatly reduces the duration of CHECKDB
when these objects are present
SQL Server 2016: It Just Runs Faster
 Stay tuned!
SQL 2016 – It Just Runs Faster Announcement
Questions
Patrocinadores
Thank you!
Gracias!
Obrigado!
Links
 http://facebook.com/devSQL
 [email protected]
 SQL Server Discovery Day
 http://www.eventbrite.com/e/sql-discovery-daytickets-25185568714
 (personalize com seus links)
 Utilize nossa hashtag no twitter: #SqlSatRJ