10 New Features in SQL Server 2017 Database Engine
SQL Server 2017 is major release that are coming with lots of new features. The most debated new feature in SQL Server 2017 is its support for Linux based operating systems. Yes, now you can install and run SQL Server 2017 on Linux based operating system. Here i will discuss top 10 SQL Serve 2017 new features in this article.
SQL Server 2017 New Features in Database Engine
Let’s discuss SQL Server 2017 features. Below is the list of SQL Server 2017 new features introduced in database engine services.
SQL Server on Linux
SQL Server is not only Windows based RDBMS anymore now you can run it on different flavor of Linux operating systems. Now you can develop applications with SQL Server on Linux, Windows, Ubuntu, or Docker and deploy them as well on these platforms. SQL Server can now compete more directly with other RDBMS like Oracle which are more popular on Linux. We can say that SQL Server on Linux is one of the most popular SQL Server 2017 features. You can read below articles if you want to install SQL Server on Linux based operating systems.
- 21 Missing Features in SQL Server on Linux
- Install SQL Server 2017 on Redhat Linux – Offline Installation
- Install SQL Server 2017 on Redhat Linux – Online Installation
- How to install SQL Server on Ubuntu – Offline Installation
- How to install SQL Server on Ubuntu – Online Installation
Adaptive Query Processing
SQL Server 2017 introduce a new generation of query processing improvements that will adapt optimization strategies to your application workload’s runtime conditions. During optimization, the cardinality estimation process is responsible for estimating the number of rows processed at each step in an execution plan. When estimates are inaccurate, the query optimizer may make poor decisions regarding algorithm selection and order of operations. This is also one of the most awaited SQL 2017 new features.
Prior to SQL Server 2017, if we make poor assumptions due to bad cardinality estimates, we do not change our query plan execution strategy during execution. Read attached article Adaptive Query Processing to learn more about it.
Resumable Online Index Rebuild
Resumable Online Index Rebuild is another SQL 2017 feature. Now we can pause and resume online index rebuild operation. This feature will be very helpful. Resumable Online Index Rebuild allows you to resume an online index rebuild operation from where it stopped after a failure. For example, you might need to temporarily free up systems resources in order to execute a high priority task or complete the index rebuild in another maintenance window if the available maintenance windows is too short for a large table. Read attached articles to understand in depth information about this feature.
Automatic Database Tuning
Automatic tuning is a database feature that provides insight into potential query performance problems, recommend solutions, and automatically fix identified problems. Automatic tuning in SQL Server, notifies you whenever a potential performance issue is detected, and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems.
Graph Database Capabilities
SQL Server offers graph database capabilities to model many-to-many relationships. The graph relationships are integrated into Transact-SQL and receive the benefits of using SQL Server as the foundational database management system.
A graph database is a collection of nodes (or vertices) and edges (or relationships). A node represents an entity (for example, a person or an organization) and an edge represents a relationship between the two nodes that it connects (for example, likes or friends). Both nodes and edges may have properties associated with them.
Cluster less Availability Groups
Now we can configure AOAG\Alwayson Availability group without windows cluster in SQL Server 2017. This feature is added because of another new feature that is to configure AOAG between windows and Linux based SQL Servers. Now you don’t need a windows cluster in place if you want to configure AOAG between windows and Linux based SQL Servers.
Availability Groups can now work across Windows-Linux to enable cross-OS migrations and testing. Now we can configure AOAG between windows based SQL Server and Linux based SQL Server. That is why, Microsoft has also given an option for clusterless AOAG.
There are few new T-SQL functions added to SQL Server 2017. Below is the list of those new functions. You can click on hyperlinks to get more about them. These functions are very useful for SQL developers.
There are many new DMVs have been introduced as SQL 2017 new features. Below is the list of such DMVs that have been introduced in SQL Server 2017. You can click on hyperlinks to get more about them.
- sys.dm_tran_version_store_space_usage – Introduced to track version store usage per database.
- sys.dm_db_stats_histogram (Transact-SQL) is added for examining statistics.
- sys.dm_os_host_info is added to provide operating system information for both Windows and Linux.
- sys.dm_os_sys_info has three new columns added: socket_count, cores_per_socket, numa_node_count.
- A new column modified_extent_page_count, is introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database.
Below is the list of new DMFs introduced in SQL Server 2017. You can click on hyperlinks to get more about them.
- This DMF sys.dm_db_log_info is introduced to expose the VLF information similar to DBCC LOGINFO to monitor, alert, and avert potential transaction log issues caused due to number of VLFs, VLF size or shrinkfile issues experienced by customers.
- Another DMF sys.dm_db_log_stats, is introduced to expose summary level attributes and information on transaction log files. This is very useful for monitoring the health of the transaction log.
- SQL Server Update Statistics Best Practices - April 15, 2020
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018