Home Uncategorized SQL Server 2016 “Hidden Gems” Resources

    SQL Server 2016 “Hidden Gems” Resources

    1362
    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

    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.      

    Comments are closed.