This article describes a list of performance improvements and configuration options that are available for SQL Server 2017 and SQL Server 2016.
Original product version: SQL Server 2017, SQL Server 2016
Original KB number: 4465518
This article describes the performance improvements and changes that are available for Microsoft SQL Server 2017 and SQL Server 2016 through various product updates and configuration options.
We recommend that you consider applying these updates to improve the performance of SQL Server instances. The degree of improvement will depend on various factors, including workload pattern, contention points, processor layout (number of processor groups, sockets, NUMA nodes, and cores in a NUMA node), and the amount of available memory in the system.
The SQL Server support team has used these updates and configuration changes to achieve reasonable performance gains for customer workloads that use hardware systems that included several NUMA nodes and lots of processors. The support team will continue to update this article with other updates in the future.
Apply recommended updates and improve SQL Server performanceDefinition: High-end systems
A "high-end system" typically has multiple sockets, eight cores or more per socket, and a half terabyte or more of memory.
These recommendations for improving the performance of SQL Server 2017 and SQL Server 2016 are grouped into five tables, as follows:
Important
If you enabled the trace flags, make sure that you review the information in that article after you run the migration to SQL Server 2017 or SQL Server 2016. Many of the trace flags and configuration options that are listed in that article became default options in SQL Server 2017 and SQL Server 2016.
Table 1. Important updates and trace flags for high-end systemsReview the following table, and enable the trace flags in the Trace flag column after you make sure that your instance of SQL Server meets the requirements in the Applicable version and build ranges column.
Note
Important
When you enable fixes that have trace flags in Always On environments, be aware that you have to enable the fix and trace flags on all the replicas that are part of the Availability Group.
Table 2. General considerations and best practices for improving the performance of your SQL Server instanceReview the content in the Knowledge Base article or Books Online Resource column, and consider implementing the guidance in the Recommended actions column.
Knowledge Base article or Books Online resource Recommended actions Configure the max degree of parallelism Server Configuration Option Use thesp_configure stored procedure to make configuration changes to Configure the max degree of parallelism Server Configuration Option for your instance of SQL Server per the Knowledge Base article. Compute capacity limits by edition The license core limitation for SQL Server 2012 Enterprise Edition that has Server and Client Access License (CAL) licensing is limited to 20 cores per SQL Server instance.
There are no limits under the Core-based Server Licensing model.
Consider upgrading your edition of SQL Server to the appropriate SKU to use all hardware resources.
Slow performance on Windows Server when using the Balanced power plan Review the article, and work together with your Windows administrator to implement one of the solutions that are listed in the Resolution section of the article. optimize for ad hoc workloads Server Configuration Option Entries in the plan cache are evicted because of growth in other caches or memory clerks. You might also encounter plan cache eviction when the cache reaches its maximum number of entries. In addition to trace flag 8032 discussed above, consider the optimize for ad hoc workloads server option and also the FORCED PARAMETERIZATION database option. How to reduce paging of buffer pool memory in SQL ServerMemory configuration and sizing considerations in SQL Server 2012 and later versions
Assign the Lock Pages in Memory Option (Windows) user right to the SQL service Startup account.Set maximum server memory to approximately 90 percent of total physical memory. Make sure that the Server memory configuration options setting accounts for memory from only the nodes that are configured to use affinity mask settings.
SQL Server and Large Pages Explained... Consider enabling Trace Flag 834 if you have a server that has much memory, particularly for an analytical or data warehousing workload. Keep in mind that Interoperability of Columnstore indexes with large page memory model in SQL Server. Query Performance issues associated with a large sized security cacheDBCC TRACEON - Trace Flags (Transact-SQL)
If the security cache grows to a large size and you encounter performance problems and spinlock contention, consider enabling trace flag T4610 and T4618 to reduce the maximum size of TokenAndPermuserStore. ALTER WORKLOAD GROUP KB3107401 - New query memory grant options are available (min_grant_percent and max_grant_percent) in SQL Server 2012 If you have many queries that are exhausting large memory grants, reducerequest_max_memory_grant_percent for the default workload group in the resource governor configuration from the default 25 percent to a lower value. New query memory grant options are available (min_grant_percent and max_grant_percent) in SQL Server. SQL 2016 - It Just Runs Faster: Automatic TEMPDB Configuration Add multiple data files of equal size for the tempdb database if this is an upgraded server. For new installs, setup automatically does this. TEMPDB - Files and Trace Flags and Updates Use tempdb optimizations and improve scalability by avoiding or reducing DDL on temp objects. Instant File initialization Work together with your Windows administrator to grant the SQL Server service account the Perform Volume Maintenance Tasks user rights per the information in the Books Online topic. Considerations for the "autogrow" and "autoshrink" settings in SQL Server Check the current settings of your database, and make sure that they're configured per the recommendations in the Knowledge Base article. Indirect Checkpoints Consider enabling indirect checkpoints on user databases to optimize I/O behavior in SQL Server 2014 and 2012. SQL Server : large RAM and DB Checkpointing Consider enabling indirect checkpoints on user databases to optimize I/O behavior in SQL Server 2014 and 2012. Review the required adjustments for tempdb in the reference Indirect Checkpoint and tempdb - the good, the bad and the non-yielding scheduler. KB3009974 - FIX: Slow synchronization when disks have different sector sizes for primary and secondary replica log files in SQL Server AG and Logshipping environments If you have an Availability Group in which the transaction log on the primary replica is on a disk that has a 512-byte sector size and the secondary replica transaction log is on a drive that has a 4-K sector size, you may experience slow synchronization. In this situation, enabling TF 1800 should correct the issue. For more information, see Trace Flag 1800. Query Profiling Infrastructure
If your SQL Server isn't already CPU bound and a 1.5 percent to 2 percent overhead is negligible for your workloads, we recommend you enable TF 7412 as a startup trace flag. This flag enables lightweight profiling in SQL Server 2014 SP2 or later. This lets you do live query troubleshooting in production environments. Identify plan choice regressions using Query Store Activate the best query plan Use the query store feature to identify queries that regressed or are performing poorly. If the query performance issues occur because of cardinality estimation, select the appropriate CE version: database-scoped option LEGACY_CARDINALITY_ESTIMATION, query hint LEGACY_CARDINALITY_ESTIMATION, database compatibility level, or trace flag 9481. Join containment assumption in the New Cardinality Estimator degrades query performance Evaluate the queries that use joins and filters to understand the effect of simple and base containment. Use trace flag 9476 for simple containment instead of base containment when you use the default cardinality estimator. Improvements in compatibility level 130 Improvements in compatibility level 140 Use database compatibility level 130 or later to benefit from the following improvements:
Apply the fix in KB4340759 - FIX: Slow performance of SQL Server 2016 when Query Store is enabled if you experience query store spinlock contention under heavy workloads.
SQL Server 2016/2017: Availability group secondary replica redo model and performance If you experience too many waits (PARALLEL_REDO_TRAN_TURN, DPT_ENTRY_LOCK, or DIRTY_PAGE_TABLE_LOCK), review this blog to take corrective actions (apply applicable fix, evaluate appropriate use of redo model). KB2634571 - Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option
A faster CHECKDB - Part IV (SQL CLR UDTs)
If you run DBCC CHECK commands on large databases (multiple TB sizes), consider using trace flags T2562, T2549, and T2566.Several checks are now located under the EXTENDED_LOGICAL_CHECK option in SQL Server 2016.
Protect SQL Server from attacks on Spectre and Meltdown side-channel vulnerabilities Carefully evaluate the performance of Kernel Virtual Address Shadowing (KVAS), Kernel Page Table Indirection (KPTI), and Indirect Branch Prediction mitigation (IBP) on various SQL Server workloads in your environment. Table 3. Important changes that are introduced in SQL Server 2017 and SQL Server 2016SQL Server 2017 and SQL Server 2016 contains several improvements in the areas of scalability and performance. Various configuration changes and trace flags that are required in SQL Server 2014 and SQL Server 2012 became the default behavior in SQL Server 2017 and 2016. This table provides an overview of all changes that are implemented in SQL Server 2017 and SQL Server 2016.
Area Summary of the change More information and references SQL Engine Frequently used trace flags that are either retired or no longer needed in SQL Server 2016 and the later versions of SQL Server: 8048, 8079, 9024, 1236, 1118, 1117, 6498, 8075, 3449, 6532, 6533, 6534. SQL Server Trace Flags Database Engine What's new in Database Engine - SQL Server 2017 Database Engine Breaking Changes to Database Engine Features in SQL Server 2016 Database Engine Breaking Changes to Database Engine Features in SQL Server 2017 Query Processing datatype conversions SQL Server 2016 (13.x) includes improvements in some data types conversions and some (mostly uncommon) operations. For more information, see SQL Server 2016 improvements in handling some data types and uncommon operations. Availability Group For secondary database, initialization automatic seeding uses the database mirroring endpoints to stream the database content to the secondary and apply them. SQLSweet16!, Episode 2: Availability Groups Automatic Seeding Availability Group SQL Server 2016 uses less context switches when it transports log blocks from primary to secondary. SQL 2016 - It Just Runs Faster: Always On Log Transport Reduced Context Switches SQL Server 2016 - It Just Runs Faster: Always On Availability Groups Turbocharged Availability Group SQL Server 2016 uses improved compression algorithms and parallel compression of log block data. SQL 2016 - It Just Runs Faster: Always On Parallel Compression / Improved Algorithms Availability Group SQL Server 2016 takes advantage of hardware that is based AES-NI encryption capabilities to improve Always On log shipping scalability and performance by a significant factor. SQL 2016 - It Just Runs Faster - Always On AES-NI Encryption Performance SQL Server 2016 detects the CPU capabilities for AVX or SSE and uses the hardware-based vector capabilities to improve scalability and performance when compressing, building dictionaries, and processing columnstore data. SQL 2016 - It Just Runs Faster: Column Store Uses Vector Instructions (SSE/AVX) Performance SQL Server 2016 takes advantage of CPU vector instructions to improve bulk insert performance. SQL 2016 - It Just Runs Faster - BULK INSERT Uses Vector Instructions (SSE/AVX) Performance SQL Server 2016 enables an INSERT ... SELECT statement to operate by using parallelism significantly reducing the data loading time. SQLSweet16!, Episode 3: Parallel INSERT ... SELECT Performance SQL Server 2016 enables TRUNCATE operation on individual partitions of a table for archiving older partitions. SQLSweet16!, Episode 5: TRUNCATE Selected Partitions Performance SQL Server 2016 dynamically adjusts the size of the In-Memory Optimized Database worker pool as needed. SQL 2016 - It Just Runs Faster: In-Memory Optimized Database Worker Pool Tempdb Allocations are tempdb and user databases uses uniform full extents. File growth in tempdb happens for all files at the same time. SQL 2016 - It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases Tempdb Database Engine setup automatically calculates the number of tempdb data files. SQL 2016 - It Just Runs Faster: Automatic TEMPDB Configuration Storage Database engine uses 0xC0 stamp instead of 0x00 for transaction log file initialization. SQL 2016 - It Just Runs Faster: LDF Stamped Storage For large memory servers and heavy write environments, indirect checkpoint performs better. SQL 2016 - It Just Runs Faster: Indirect Checkpoint Default Storage High rates of transactions can benefit from multiple logwriters flushing log cache to transaction log. SQL 2016 - It Just Runs Faster: Multiple Log Writer Workers Backup and Restore Backups can be compressed for database encrypted by using TDE if you specify MAXTRANSFERSIZE greater than 65536. SQLSweet16!, Episode 1: Backup Compression for TDE-enabled Databases SQL OS Dynamically partition memory objects to reduce memory object contention. SQL 2016 - It Just Runs Faster: Dynamic Memory Object (CMemThread) Partitioning SQL OS SQL Server 2016 monitors the quantum usage patterns of workers allowing all workers to get fair treatment and improve scalability. SQL 2016 - It Just Runs Faster: Updated Scheduling Algorithms SQL OS 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 for improved scalability and performance. SQL 2016 - It Just Runs Faster: Automatic Soft NUMA DBCC CHECK Specify MAXDOP to manage resources that are consumed by the DBCC CHECK command. SQLSweet16!, Episode 6: DBCC CHECKDB with MAXDOP DBCC CHECK DBCC CHECK uses an improved page scanning algorithm that has less contention and advanced read-ahead capabilities. SQL 2016 - It Just Runs Faster: DBCC Scales 7x Better DBCC CHECK DBCC CHECK commands take a long time when SQL Server evaluates special data types and indexes. These checks moved under EXTENDED_LOGICAL_CHECKS option. SQL 2016 - It Just Runs Faster: DBCC Extended Checks Code page BULK INSERT or bcp utilities improved to load UTF-8 data into a table in SQL Server. SQLSweet16!, Episode 10: "I can eat glass ...", but can I load it into a database? Spatial SQL Server 2016 removes the PInvoke and PUnInvoke activities during T-SQL execution for many of the spatial methods. SQL 2016 - It Just Runs Faster: Native Spatial Implementation(s) Spatial SQL Server 2016 improves the scalability of TVP that uses spatial data by using native spatial validations. SQL 2016 - It Just Runs Faster: TVPs with Spatial Column(s) Spatial The native and TVP spatial improvements enable SQL Server to optimize index creation and tessellation of spatial data. SQL 2016 - It Just Runs Faster: Spatial Index Builds Faster MSDTC SQL Server 2016 dynamically starts MSDTC as needed allowing resources to be used for other activities until required. SQL 2016 - Leverages On Demand MSDTC Startup XEvent Various changes are made to the XEvent Linq provider logic to reduce context switching, memory allocations, and other aspects for faster rendering of events. SQL 2016 - It Just Runs Faster: XEvent Linq Reader Table 4. Important fixes that are included in a CUReview the description in the Symptoms column and apply the required updates (preferably the latest update that contains the specific fix) in the Required update column in applicable environments. You can review the Knowledge Base article for more information about the respective issues. These recommendations don't require you to enable other trace flags as startup parameters unless it's explicitly called out in the article or in this table. Just applying the latest CU or Service Pack that includes these fixes is enough to get the benefit.
Note The CU name in the Required update column provides the first CU of SQL Server that resolves this issue. A cumulative update contains all the hotfixes and all the updates that were included together with the previous SQL Server update release. As noted in the updates to the SQL Server Incremental Servicing Model, we recommend that you install the latest cumulative update in an ongoing proactive cadence to resolve or prevent the issues that are described. Also note that starting in SQL Server 2017, the Modern Servicing Model for SQL Server was introduced so that service packs are no longer made available.
Applicable version Area or component Description of issue addressed Recommended update SQL Server 2016, SQL Server 2017 Backup & Restore Log backup of a TDE-enabled database fails and returns error 33111 intermittently when looking for an older copy of certificate that was used to encrypt the DEK in the past if non-default MAXTRANSFERSIZE is used FIX: Errors 33111 and 3013 when backing up TDE-encrypted database in SQL ServerThis table is a compilation of all key improvements, recommendations, and code changes that were released in cumulative updates after SQL Server 2017 was released. Review the description in the Symptoms column, and apply the required updates (preferably the latest update that contains the specific fix) in the Required update column in applicable environments. You can review the listed Knowledge Base article for more information about the respective issues.
These recommendations don't require you to enable other trace flags as startup parameters unless it's explicitly called out in the article or in this table. Just applying the latest cumulative update or service pack that includes these fixes is enough to get the benefit. If you're using Always On Availability Group in SQL Server on Linux, upgrade SQL Server 2017 to Cumulative Update 8 or higher since several improvements were delivered in this update. Note The CU name in the Required update column provides the first cumulative update of SQL Server that resolves this issue. A cumulative update contains all the hotfixes and all the updates that were included with the previous SQL Server update release. As noted in the updates to the SQL Server Incremental Servicing Model, we now recommend that you install the latest cumulative update in an ongoing proactive cadence to resolve or prevent the issues that are described. Also note that starting with SQL Server 2017, the Modern Servicing Model for SQL Server was introduced so that service packs are no longer made available.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4