SQL 2017 Is On The Way!!

–By Lori Brown  @SQLSupahStah

SQL Server 2017 is rolling our way like a big ole train. And, with the new features that are going to be available there may be quite a few shops that want to jump onboard.

As of this writing SQL Server 2017 CTP 2.1 is available for download and testing. (https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2017-ctp/ ) I managed to recently attend a presentation by Denzil Ribeiro who is a manager with the SQLCAT team on some of the new features in SQL 2017. I tend to pay attention to the things that get the guys on the inside excited so here are some of the highlights with links that I could find and my notes on SQL 2017.

Resumable Online Index Rebuild

https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/20/resumable-online-index-rebuild-is-in-public-preview-for-sql-server-2017-ctp-2-0/

Index rebuilds can be paused and restarted. Since index must be created with ONLINE = ON then it is likely that this is an Enterprise Edition feature.

ALTER INDEX IDX_MyInx ON SomeTable

REBUILD WITH (RESUMABLE = ON, ONLINE = ON, MAX_DURATION = 1)

— Pause

ALTER INDEX IDX_MyInx ON SomeTable PAUSE

Use sys.index_resumable_operations to view the status of resumable index rebuilds. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-index-resumable-operations

Wait stats for per query executions

Available by default. Statistics info is now in query plans. Use sys.query_store_wait_stats to see wait info for a query plan. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql

Automatic Query Tuning

https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning

Recognizes plan regressions and will automatically force a good plan to be used. Database must be in 140 (SQL2017) compatibility. Must enable the Query Store for the database. Can then use sys.dm_db_tuning_recommendations (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-tuning-recommendations-transact-sql ) to get info on plans and recommendations that were used to fix regressions. Won’t force a good plan unless AUTOMATIC_TUNING = ON.

ALTER DATABASE current

SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Adaptive Query Processing

Interleaved Execution

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-interleaved-execution-for-multi-statement-table-valued-functions/

Available by default. Adjust plans by testing plan and then redesigning plan based on better row estimates. All while a query is executing.

Batch Mode Memory Grant Feedback

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/29/introducing-batch-mode-adaptive-memory-grant-feedback/

Adjust plan in cache if memory grant is not good enough (either too much or not enough).

Batch Mode Adaptive Joins

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-batch-mode-adaptive-joins/

After first join in a plan, better decision made to choose Hash Join or Nested Loop Join. If number of rows small then likely Nested Loop Join will be used

Graph Data Processing

https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/20/graph-data-processing-with-sql-server-2017/

Nodes and edges stored as tables. Many – many modeling. New MATCH clause in queries. https://docs.microsoft.com/en-us/sql/t-sql/statements/match-sql-graph

SELECT Person2.Name

FROM Person Person1, Friends, Person Person2

WHERE MATCH(Person1 – (Friends) -> Person2)

AND Person1.Name = ‘John’;

Python

https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/19/python-in-sql-server-2017-enhanced-in-database-machine-learning/

Python is now integrated into SQL just like R Services and is used for advanced analytics. I have to admit that Python is way beyond my skillset right now but wanted to mention it.

Conclusion

A lot of these new features for SQL 2017 are evolving and more will come out I am sure. I am in the process of getting a VM with Linux installed on it so that I can check out installing and running SQL Server on Linux so expect a post on that soon.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!