Thursday, November 17, 2016

SQL Server 2016 SP1 Release - Thoughts and Comments!!!

SQL Server 2016 SP1 has released last night and most of the features that were previously part of enterprise edition have been made available in standard edition now!!!! By far, the biggest change any service pack brought about in any version of SQL Server!!!

List of items provided below which moved from enterprise to standard provided below. Read about the announcement over here

  • Change Data Capture
  • Database Snapshot
  • Columnstore Index
  • In-Memory OLTP
  • Row Level Security
  • Dynamic Data Masking
  • Always Encrypted
  • Fine Grained Auditing
  • Data Compression
  • Multiple FILESTREAM Containers
  • Partitioning
  • Polybase
That's quite a change and I am super excited looking at the list of items. Quick comments few of those items.

Column store Index:

A game changer technology especially in data warehouse applications now moves to standard. But, the concern is, it is a memory driven concept known to consume significant amount of memory. The memory limit of 128 GB still holds good on standard edition. To add to that, SQL Server 2016 SP1 sets a hard limit of 25% of maximum memory set for SQL Server for the  memory used by column store index. For example if 48 GB is allocated to SQL Server, column store can use maximum of 12 GB. This implies, theoretically column store index can maximum take only  32 GB of RAM. However, the memory used by Columnstore is not counted within buffer pool's quota of memory.  So it implies, if 48 GB of memory is set for SQL Server, then SQL Server's total memory usage can go up to 60 GB ( 12 GB for column store + 48 GB of buffer pool )

Though the extended memory usage provided for Columnstore does sound useful, it may not be so effective as column store index is at its best only when it operates on large volume of data with sizable amount of memory.

In-Memory OLTP:

In my opinion, one of the best moves of SP1. Reason is even though "In memory" is ideally suitable for systems with few hundred GBs of RAM, it can prove handy for some of the mid size ones too. 25% of max memory hard limit also applies for "In memory" tables (similar to column store). For ex, lets say we have a 64 GB of RAM for SQL Server, and then 16 GB would be available for "In memory" tables.

1) Though the memory available may sound to be small, it can still be useful to place the most important and most accessed tables take advantage of "In memory OLTP". Please note that most important tables of the application may not be the largest table by size.

2) "In memory OLTP" can help applications get rid of locking and latch contentions almost completely.

3) Remember, "In memory" is not just about placing tables in the memory. SQL Server reads and writes to them differently enhancing the overall performance of the application

So, this is an enhancement which I will be pushing my application teams to take advantage of for sure.

Security n Encryption Related Features: - Row Level Security / Dynamic Data Masking / Always Encrypted / Fine Grained Auditing

It is a excellent move to place security related features in standard and other editions. My personal take has always been that security related features should be available at all editions of SQL Server. The reason why security related features needs to be available for all is application developed may not have high performance requirements demanding enterprise edition but would need key security features of SQL Server. For example, a small shopping cart application which just needs SQLExpress, deals with sensitive data ( credit card numbers, account balance etc..) requires  features like "Always Encrypted" or "Row Level security" for developing secure applications.


Another welcome change as Standard edition does deal with terabytes of data and partitioning does make lot of their lives easier

Change Data Capture / Database Snapshot :

Commonly used features in development or staging environments and making them available across all editions makes it better for environments which use different editions for Development and Production.

What remains in Enterprise:

High Availability feature like "Always ON Availability Groups" ( barring basic AG which was made available in standard since SQL Server 2016 RTM ) and "Online Index Rebuild" remain in enterprise.  Capacity limitations of lesser editions ( max 128 GB, 24 core processor limit on standard etc.. ) still remain. This makes absolute sense, as these features are primarily required by applications with extreme performance requirements and they should ideally be on Enterprise editions ( without capacity limits ).

Also notice that the features on enterprise only are more of "Infrastructure" related instead of "Development" related. This also is a move in right direction as one needs to have the same editions at development environment and production environment to make application development effective.

Overall, indeed, one of the best announcements in the entire history of SQL Server. No doubt, it is going to make several applications move to SQL Server 2016 soonest and makes my job of convincing application teams easier :)

No comments: