SQL 2017 Is On The Way!!

By Lori Brown | CTP

May 19

–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.

Blog_20170519_1

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

Blog_20170519_2

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!

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

  • […] SQL 2017 Is On The Way!! Lori Brown goes through the new features available in SQL Server 2017 CTP […]

  • >