What's New for SQL Server 2014 Standard Edition
Improved Cardinality Estimator
Joe Sack from SQLSkills wrote a nice white paper that he linked to on his SQLSkills blog called Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.
Parallel SELECT * INTO...
Another Joe Sack post on the SQLPerformance site called Exploring SQL Server 2014 SELECT INTO Parallelism discusses this new feature and the benefits.
TempDB Eager Write Fix
This this short blog post by Bob Dorr from the PSSQL team quickly explains how just by upgrading to SQL 2014 you could be seeing significant performance benefits by the improvements to how tempdb write operations are being managed.
Buffer Pool Extension
This feature is great for those of us forced to run SQL on a limited resource server or a highly dense VM infrastructure. Brent Ozar has a nice write-up called SQL Server 2014 Buffer Pool Extensions for this feature on his blog.
Delayed Durability
Aaron Bertrand discusses this feature on his own blog in a post called Delayed Durability in SQL Server 2014. While this feature can grant you some performance improvements, you will introduce some level of potential data loss if a problem occurs.
Inline Index Declarations
Here's a quick write-up by Junaith Haja on SQLCast.com called Inline Specification of Indexes explaining how to create indexes at table creation time.
sys.dm_exec_query_profiles
This is a new DMV that shows query plan progress. Show profile must be on in order to use it. There is a visualization
tool for this in 2016 (mentioned below). This blog post by David Barbarin on dbi-services.com called SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature explains this new feature in detail.
Native Backup Encryption
Native encryption for SQL Server backups is huge! Nicolas Souqet explains this feature in great detail on his msdn post called SQL Server 2014 Backup Encryption.
What's New for SQL Server 2016 Standard Edition
Wider Indexes
They're going to be making similar indexing changes as they did for Azure in SQL 2014. You'll be able to have 32
columns instead of 16 and, at least for nonclustered indexes, 1700
bytes instead of 900. I couldn't find any post on this feature for SQL 2016, but here's a post on sqlmag.com called Indexes in Azure SQL Database v12 explaining how they work for SQL 2014 in Azure.
Stretch Database
This feature will let you store partial data as read only in the cloud. It is similar to a
distributed partitioned view. This article called SQL Server 2016: Stretch Database on infoq has more details including the limitations.
Configure TempDB On Setup
Aaron Bertrand from SQLSentry has a very detailed write-up on this new feature in his post called SQL Server 2016 : Getting tempdb a little more right.
Smarter Maintenance Plans
Aaron didn't really go into detail on this during his talk and I couldn't find much online regarding the improvements. I did find one Power Point Presentation by Kat Long from xtivia.com where she bullets a few of the improvements:
- Perform Checksum
- Continue on Error
- Block Size
- Max Transfer Size
If anyone can find more information please let me know.
SSMS Automatic Check for Updates
In order to keep up with the fast paced cloud offerings, in SQL 2016 SQL Server Management Studio Now Has Its Own Upgrade Path independent of the normal release cycle.
.Net 3.5 Dependency (Might Be) Going Away
That's about all that we know. Aaron didn't seem too sure about this one either but it's one to keep an eye out for.
New Extended Events for Backup / Restore Operations
Aaron Bertrand discusses New Backup and Restore Extended Events in this SQLPerformance.com post. They give you a much more granular view of the backup / restore operations to help troubleshoot problems.
New Query Hints
Aaron mentioned that some new query hints called min_grant_request and max_grant_request will be available f
SQL Query Store
It's possible this feature will remain enterprise only. This is a very helpful feature that keeps track of query plans and shows
regressions. Benjamin Navarez posted an article called The SQL Server Query Store on sqlperformance.com which discusses this new feature in detail.
JSON Support
If you're like me and many other DBA's, then you know that experience with NoSQL databases like MongoDB are an inevitable part of the future career path of many database administrators and with that will come JSON. SQL Server 2016 now natively supports JSON in a similar way to how it supports XML. Aaron Bertrand wrote an entire article on his SQLSentry blog called SQL Server 2016 : JSON Support that goes into detail about the feature.
Additional Online Index Support
Aaron glazed over this item quickly during the talk. I couldn't find anything after doing some Google searches. We'll just have to keep an eye out for this.
Temporal Table Support
This is a cool feature that could possibly be used in place of Change Data Capture. This feature allows for any logical record to have a current version and zero or more historical versions that can be queries using some newly available syntax. Additionally, this feature is ANSI standard. Jonathan Allen posted a nice article on infoq called SQL Server 2016: Temporal Tables that explains this feature in more detail.
Live Query Stats
Another cool feature is that SSMS 2016 adds visual support to the sys.dm_exec_query_profiles DMV mentioned above. Jonathan Allen also posted an article about this feature on infoq called SQL Server 2016: Live Query Statistics that explains this in more detail.
sys.dm_exec_function_stats
Having trouble with developers adding functions to your stored procedure? Well, now you can show them exactly how detrimental that function is to their procedure with this new DMV that's available. Aaron Bertrand has a nice write-up of this feature on sqlperformance.com called SQL Server 2016 : sys.dm_exec_function_stats.
New Diagnostic Columns in sys.dm_exec_query_stats
This troubleshootingsql.com article called SQL Server 2016 Public Preview (CTP2) – sys.dm_exec_query_stats lists out the 24 new columns available to the sys.dm_exec_query_stats DMV and what they do.
sys.dm_exec_session_wait_stats
This is a new
dmv for breaking out wait stats by session. A Google search yielded nothing for me so we'll have to wait and see about this one.
Row Level Security
Previous versions of SQL Server allowed you to secure table data by columns, but new to SQL 2016 is a feature that allows you to secure table data by rows. This msdn article explains the Row Level Security feature and how it works.
Dynamic Data Masking
On the surface, this feature looks awesome. It allows you to mask data from the client, who wouldn't want the ability to do that?! Within a matter of about 20 minutes, after running some custom test queries, Aaron, with the help of the participants at the user group, was able to render this feature minimally useful. There are still some valid use cases, but not as many as I would have liked. Here's an article from Pinal Dave on the sqlauthority site called SQL Server – 2016 – New Feature: Dynamic Data Masking explaining the feature in detail.
What the feature does is allow you to show garbage data for certain users. You can provide a filter function which basically does a character replacement for the most part.
The problem lies in the fact that this is basically a filter applied at the SELECT clause. It doesn't actually change data stored in the database. Here are some ways we found to subvert the masking:
- you can still search for a value within WHERE clauses (so if the value 12345 is masked to 1XXXX, you can still do WHERE myCol = 12345 and the row will be returned)
- a SELECT ... INTO, regardless of whether it's a temporary or persisted table, will contain the unmasked data
Aside from restricting viewing access via the application or during a presentation, I'm not sure how valuable this feature will be.
Always Encrypted
This is another great feature available in 2016. For those of you that need to encrypt data at rest, it may save some trouble with having to use third party tools. The keys are now stored in the front-end, off server which will help satisfy some of those pesky security requirements where TDE failed.
New Password Hashing Functionality
I couldn't find any information online in regards to this feature, but one interesting item that Aaron pointed out during his talk is that a hashed password with check policy on fails (silently) yet is_policy_checked still shows a value of 1. Aaron has a write-up for this issue in his post called Please help me fix a big security loophole. (Connect Issue 1250342 for
more details)
High Availability
High Availability may become available for basic availability groups.
- no listener support
- no readable secondaries
- single database per group
Replication to Azure
This was mentioned as a SQL 2016 feature but it seems to be available in 2014 as well: Use the Add Azure Replica Wizard (SQL Server)
Additional Notes
There were a few takeaways from the meetup that were mentioned but weren't necessarily SQL 2014/2016 specific features.
sys.dm_db_persisted_sku_features
This DMV can be used to check standard edition features that are restricted in the current version of SQL Server.
Development Tricks
There are some nice ways for developers to create a test database that will act like a production database without having all of the production data.
- Create an empty database but add all of the production statistics and histograms called a stats-only database (don't forget to turn off auto update stats). Matteo Lorini posted a nice article on mssqltips.com called How to create a SQL Server Clone Statistics Only Database which goes into detail about how to do this.
- DBCC OPTIMIZER_WHATIF allows you to see what a query plan would look like if the hardware were different. This article on sqlity.net called Optimizer, what if I had more CPUs? has more information
- DBCC SHOWWEIGHTS, SETCPUWEIGHT, and SETIOWEIGHT allow you to see what plans would look like if the CPU and IO costs change. This article by Paul White called Inside the Optimizer: Plan Costing goes into more detail about how the plan is generated and how these undocumented features work.
- UPDATE STATISTICS ... WITH ROWCOUNT will allow you to generate statistics based on false row count information
Summary
There are so many new features being introduced in SQL Server 2016 with a good number of those being released for standard edition. Many of these features attempting to align the feature set with the cloud based Azure offering.
No comments:
Post a Comment