Home Uncategorized SQL Server 2016 “Hidden Gems” Resources

SQL Server 2016 “Hidden Gems” Resources

11

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.

File Attachment: machanic_sql_2016_hidden_gems.zip

SHARE
Previous articleThe SQL Genie (T-SQL Tuesday #086)
Next articleSQL Server 2016 “Hidden Gems” – Now on a YouTube Near You
Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

11 COMMENTS

  1. @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

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

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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here