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
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
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
Great presentation! I look forward to re-viewing the recording and testing in my lab.
@Carnegie
Thanks for attending!
Wow wow wow!
Great presentation!
Please come to Israel to speak at http://www.sqlsaturday.com/623/eventhome.aspx
@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
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
@Jeff
I don’t think you can, today. Good case for a Connect item!
–Adam
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
@Naveen
Really glad you enjoyed it!
–Adam
Awesome…Thanks so much Adam for sharing the hidden gems in sql 2016! Much Appreciated!
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.