THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

SQL Server 2016 “Hidden Gems” Resources

Today I was honored to present my SQL Server 2016 “Hidden Gems” talk for the GroupBy online conference.

 

The slide deck and demos are attached to this post.

 

Following are some links to help you in further exploration of the various features I discussed:

 

STRING_SPLIT

https://msdn.microsoft.com/en-us/library/mt684588.aspx

Aaron Bertrand’s performance tests: https://sqlperformance.com/2016/03/sql-server-2016/string-split

 

Session Context

https://msdn.microsoft.com/en-us/library/mt605113.aspx

 

Time Zone Support

sys.time_zone_info: https://msdn.microsoft.com/en-us/library/mt612790.aspx

AT TIME ZONE: https://msdn.microsoft.com/en-us/library/mt612795.aspx

 

HASHBYTES

https://msdn.microsoft.com/en-us/library/ms174415.aspx

 

Session Wait Stats

https://msdn.microsoft.com/en-us/library/mt282433.aspx

 

Live Query Statistics

https://msdn.microsoft.com/en-us/library/dn831878.aspx

Ability to attach to other sessions' Live Queries!  https://blogs.msdn.microsoft.com/sql_server_team/query-progress-anytime-anywhere/

 

Actual Query Plan Data Enhancements

https://blogs.msdn.microsoft.com/sql_server_team/added-per-operator-level-performance-stats-for-query-processing/

 

INPUTBUFFER DMV

https://msdn.microsoft.com/en-us/library/mt652096.aspx

 

Database Scoped Configuration

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/05/02/database-scoped-configuration/

 

Increased Number of Index Keys

https://msdn.microsoft.com/en-us/library/ms143432.aspx#Engine

 

Increased Index Key Size

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/04/26/increased-nonclustered-index-key-size-with-sql-server-2016/

 

COMPRESS

https://msdn.microsoft.com/en-us/library/mt622775.aspx

 

Columnstore

Too much stuff to list! Go google it.

Published Friday, January 13, 2017 2:30 PM by Adam Machanic

Attachment(s): machanic_sql_2016_hidden_gems.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Carnegie Johnson said:

Great presentation! I look forward to re-viewing the recording and testing in my lab.

January 13, 2017 2:54 PM
 

Adam Machanic said:

@Carnegie

Thanks for attending!

January 13, 2017 3:03 PM
 

Sharon Rimer said:

Wow wow wow!

Great presentation!

Please come to Israel to speak at http://www.sqlsaturday.com/623/eventhome.aspx

January 18, 2017 8:54 AM
 

Adam Machanic said:

@Sharon

Thanks! Really glad you enjoyed it :-)

I've actually already been in touch with Maria Zakourdaev, and I'm actively working on it! Hope it happens, but some other pieces need to fall into place first. Fingers crossed.

--Adam

January 18, 2017 9:59 AM
 

jeff yao said:

Hi Adam,

when I set context_info in a session, this context_info can be seen by OTHER sessions via sys.dm_exec_sessions.

But if I set Session context via sp_set_session_context, how can I check the session context info from an external session?

Thanks,

Jeff yao

January 22, 2017 1:18 AM
 

Adam Machanic said:

@Jeff

I don't think you can, today. Good case for a Connect item!

--Adam

January 22, 2017 10:59 AM
 

Naveen Kumar said:

Hi Adam,

Thanks a lot for sharing your knowledge. Your session was just simply amazing and you showed us some really hidden gem with SQL 2016. Thanks again

Naveen

January 31, 2017 8:12 AM
 

Adam Machanic said:

@Naveen

Really glad you enjoyed it!

--Adam

January 31, 2017 9:46 AM
 

Ranga Narasimhan said:

Awesome...Thanks so much Adam for sharing the hidden gems in sql 2016! Much Appreciated!

February 14, 2017 4:34 PM
 

Vladimir Arzamazov said:

Hi, Adam!

Thank you for this interesting article. I think I will update SQL Server 2014 to SQL 2016 because curent server doesn't have a string_split function. It is a very useful function.      

May 4, 2017 6:52 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Privacy Statement