Blog Post

T-SQL Tuesday 79 - It's SQL Server 2016 - stupid


TSQL79Well, today is a busy T-SQL Tuesday no. 79, (it's also Flag Day in the U.S, and also a clerical day for school, so kids are home, but I digress!) and this month's host is Michael J. Swart, aka Database Whisperer.  With SQL 2016 recently released to GA (general availability) this month on the first of June, 2016, it is most appropos, and great timing, that the topic would be "It's SQL Server 2016" - stupid (ok I added the stupid part)!  A great open topic with so much to talk and tweet about.  So, today class, our assignment is to write about SQL Server 2016!  (of course schools here are closed). Anyway, let's do the 2016 SQL dance. Do we have a dance yet for this?

OK, what's so great about SQL Server 2016?  Everything!  And as Microsoft's new mantra about our beloved new SQL 2016 says, it has EVERYTHING BUILT IN!  You can click the hyperlink for a nice neat little info graphic on its new product, published by Gartner. It displays praise and ways that SQL 2016 is the industry leader in mission critical OLTP, the most secure database, highest performing data warehouse, features end-to-end mobile BI on any device, and has advanced analytics - say it with me - ALL BUILT IN, meaning no extra charge or license fees in addition to the core engine. This version will give it's customers in-memory across all workloads, and a consistent experience from on-premises to the cloud.  {Pause} 

Another piece of great timing, was our recent SQL2016 launch event in NYC, where myself and fellow MVPs talked about all aspects of the new evolution in data - SQL Server 2016.  We recently posted our slide decks, links in my post-event blog here. My presentation gave several reasons to upgrade, and covered top 10 features that we all need to know about. While I won't go into all them here, a few notable newbie additions (for the DBA) are worth mentioning! New functionality, features and innovation built-in is what makes SQL Server 2016 stand out among other data platforms.

Query Store, one of my favorite and most useful performance tuning features, captures query history, execution plans and run time statistics which we can review for performance. Before SQL Server 2016 the current execution plans were only available in plan cache, via DMVs, but now historical execution plan will also be available for review, allowing you to easily find performance variations caused by query plan changes and improve them.

Temporal Tables, also called System Versioned tables, which automatically keeps history of data in the table. This awesome feature is useful for auditing, versioning, monitoring and recovering modified data from history. Temporal tables lets you to query records which have been updated or deleted while the base table will return current data only.  Move over, 3rd party auditing and recovery software, we now have this functionality - built-in!

Multiple TempDB - While this is not a most exciting feature, this is a very well done addition for DBAs installing and configuring SQL Server. Every time SQL Server is

restarted, tempdb is re-created where temporary objects are automatically dropped. The default tempdb, 8 mb, was never enough for modern OLTP systems, and required the DBA to configure additional tempdb files and size them in accordance with the number of cores. Now, this is a built-in configuration option! In SQL Server 2016 you can setup the tempdb data

files at Database Engine Configuration step during installation which

automatically creates default number of files with respect to number of logical

processors on the server up to a maximum of 8 tempdb files on servers having 8 or more cores.

Trace flag 1117 and 1118 are no longer necessary to reduce page allocation contention on the tempdb, and performance is increased with reduced page allocation

contention, and temporary tables and variables caching

allows drop and create activity to be very fast. Also, all tempdb

files will auto grow at the same time.

So, there you have it, I talked about SQL Server 2016.  There is so, so, much more that will have us talking, blogging, and presenting, AND hopefully working, for the next year, as folks begin planning for upgrades and migration to SQL 2016.

OK, that's my entry to this terrific T-SQL Tuesday tete-a-tete.  Go forth, and talk about SQL 2016, before it's too late!  Follow these simple rules:

  • The post must go live on your blog between 00:00 GMT Tuesday, June 14, 2016 and 00:00 GMT Wednesday, June 15, 2016.
  • Your post has to link back to Michael's post, and the link must be anchored

    from the logo which must also appear at the top of your entry.

  • Go on Twitter, and tweet about your post, using hashtag #TSQL2sday
  • Wave a flag, and get a haircut <<<  OK, I made that one up, but if you're so inclined, go for it!

Thanks again to Adam Machanic, the cook and creator of the longest SQL blog party on the planet, TSQL Tuesday!  Thanks, and hats off to Michael J. Swart for hosting this month!  For those of you who are avid followers, bloggers, and participants, Steve Jones has compiled an awesome T-SQL Topic list!

And of

course, if anyone is interested in learning more about my book Healthy SQL – A

Comprehensive Guide to Healthy SQL Server Performance, published by Apress, you can go to the url:


  You can also

get the book on Amazon:

  For all

things SQL, news, events, jobs, info, and other fun tweets, follow me on

twitter @Pearlknows and join the #HealthySQL campaign to

keep your SQL Servers healthy!




You rated this post out of 5. Change rating




You rated this post out of 5. Change rating