The SQL Server 2017 edition is out, with several improvements and updates; this article explains the Database Engine and its features for this edition.
MS SQL Server had already been providing users with features that were quite fulfilling for its potential. Up until now the application was not only allowing users to store data in a secure and easy to retrieve manner, but also for easy transfers, cloud storage, and a lot more. With the coming of SQL Server 2017, its potential and effectiveness have only increased. A lot of updates have been implemented in the already existing features and services in the application, today we will be limiting ourselves to discussing what is new in the Database engine of SQL Server 2017.
Improvements and Enhancements in SQL Server 2017 Database Engine
- Resumable Online Index Rebuild – This Database Engine feature in the latest edition of SQL Server, allows the user to resume the online index rebuilding operation from exactly where it was hindered due to failure. This includes failures like less disk space, failover to replica, or simple pauses in the operation.
- Automatic Database Tuning – This feature allows you to take pre-emptive action for potential query troubles. It gives you insights to query performance troubles, along with suggesting solutions for the same. It can also fix problems once they have been identified.
- Graph Database Capabilities – SQL Server 2017 provides Graph Database Capabilities for modeling many – to many relationships. They have been integrated with T- SQL and also allow for easy querying and storing of graph data.
- Availability Groups – The improvements introduced in Availability Groups in this edition include cluster less support, Availability group setting for Minimum Replica Commit, along with allowing for migrations and testing with Linux.
- Compatibility Level – The users of this edition will now get access to Compatibility_Level 140, which has been introduced with this edition. The users who will be running on this level will get access to latest language features, along with behaviors for optimizing query.
- Improved Backup Performance – For all the small databases that are running on high end servers, the backup process is optimized in a way that several iterations of the buffer pool are avoided, this leads to improved performance of backups for small as well as medium databases. As the size of your database keeps on increasing, your gains in terms of performance keep on decreasing.
- Query Store Improvements – The query store in SQL Server 2017 can now give you wait stats summary, it tracks all the wait stats categories for all queries in the Query store. This allows for enhanced troubleshooting experience which gives better insights of workload performance and bottlenecks.
- Several enhancements have been made in terms of language and performance of Memory-Optimized objects, these are:
- Sp_spaceused has now been made available for all memory – optimized tables.
- CASE expressions have now been made available for all the T-SQL modules that are natively compiled.
- ALTER TABLE has been made a lot faster for all the memory-optimized tables.
- The earlier imposed limitation of just 8 indexes for the memory-optimized tables has also been done away with.
Apart from the features discussed above, there are several other improvements and enhancements that have been introduced, these are the basic and the primary ones. However despite all its advances, even the SQL Server 2017 edition is prone to SQL errors and one should always keep a SQL Server fix tool handy to deal with contingencies.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupted Access and sql recovery software products. For more information visit https://www.datanumen.com/