SQL Saturday 512 Boas Vindas Freddie Santos Senior

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. 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. Frederico Guimarães Não tenho, o que é isso? https: //www. linkedin. com/in/fredguis@live. com Não tenho, mas é uma boa ideia;

Patrocinadores

Links § http: //facebook. com/dev. SQL § devsql-subscribe@yahoogrupos. com. br § SQL Server Discovery Day § http: //www. eventbrite. com/e/sql-discovery-daytickets-25185568714 § Utilize nossa hashtag no twitter: #Sql. Sat. RJ

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 Temp. DB 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 Adventure. Works 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 (0 x 00) § Many HW implementations detect patterns of 0 x 00’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 0 x. C 0

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 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: CPU 0 CPU 1 CPU 2 CPU 3

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

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 ~ 1 GB Offline Checkpoint Thread Log 100 -200 201 -300 Memory-optimized data Filegroup 301 -400

SQL Server 2016: It Just Runs Faster ~ 1 GB 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 SQL 2014. The bar is how close we are to the perf of a standalone server.

SQL Server 2016: It Just Runs Faster §

SQL Server 2016: It Just Runs Faster • SQL Server 2014 (X 64 installations) increase the number of contiguous, 8 K pages from 16 to 32 (256 K) for write operations. • Storage subsystems on “old” systems are good once disk blocks has 64 k to 128 k.

SQL Server 2016: It Just Runs Faster • SQL Server 2016 (X 64 installations) increase the number of contiguous, 8 K pages from 32 to 128 (1 MB) 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 for Checkpoint – SQL 7 to 2014 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 (Check. Scanner) § This new design scales and performs better than the old design (Multi. Object. Scanner), 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/dev. SQL § devsql-subscribe@yahoogrupos. com. br § SQL Server Discovery Day § http: //www. eventbrite. com/e/sql-discovery-daytickets-25185568714 § (personalize com seus links) § Utilize nossa hashtag no twitter: #Sql. Sat. RJ