CTS – Your Technology Partner

An Introduction to Microsoft SQL Server 2016 Service Pack 1

Written by Scott Fountain on January 17, 2017

In November 2016, Microsoft released SQL Server 2016’s first service pack (SP1). SP1 includes new functionality along with typical improvements and fixes. With SP1, features exclusive to SQL Server’s Enterprise Edition are now available to lower editions (Standard, Web, and Express).

In this post, we will discuss the following SP1 highlights:

  • Enterprise-level features available to lower editions.
  • The CREATE OR ALTER language statement.
  • The Database Cloning DBCC command.

Expanded Functionality Across Varying Editions

The diagram provides a visual comparison of SQL Server 2016 RTM (Release to Manufacturing) and shared SQL Server 2016 SP1 (Service Pack 1) functionality.  As can be seen, SQL Server 2016 SP1 provides many Enterprise-level features to lower editions.

sql-sp1-grapic-1

When licensing costs are considered, SP1 provides tremendous benefits for small to mid-size businesses to develop more viable database solutions.  Some of the benefits are listed below.

  • With table partitioning and columnstore indexing, lower editions can maintain more efficient data warehouses and data marts.
  • With expanded auditing features, lower editions can better track data changes and schema changes.
  • With expanded security features, lower editions can better ensure appropriate access to data.
  • With PolyBase, lower editions can communicate with either the Azure Blob Storage or a Hadoop File System.

The following pricing chart compares the licensing costs for different editions.

sql-sp1-graphic-2
**Editions sold in the per-core licensing model are sold as 2 core packs.
***Pricing represents open no level (NL) estimated retail price. For your specific pricing, contact your Microsoft reseller.
****Client access licenses (CALs) are required for every user or device accessing a server in the Server + CAL licensing model. See the product use rights for details.
From <https://www.microsoft.com/en-us/sql-server/sql-server-2016-pricing>

It is important to note that SP1 has not changed SQL Server 2016’s scale and high availability limits for lower editions.  To review SQL Server 2016’s scale and availability limits, please refer to the link below.

In summary, with SP1 offering more functionality to lower editions, businesses with leaner IT budgets can implement enterprise functionality at no additional cost.

Development and Performance

SP1 added two features that are creating a lot of buzz: the CREATE OR ALTER language statement and the Database Cloning DBCC command.

New DDL Statement: CREATE OR ALTER

Consider the two options currently available to recompile views, triggers, functions, and stored procedures: recompile via an ALTER statement or recompile via a “Drop and Create” approach.

With SP1, Microsoft has added the CREATE OR ALTER statement to the language library.  At the time of compilation, SQL Server will use this statement to validate the object’s existence.  If the object exists, then compilation will use the ALTER statement.

CREATE OR ALTER Usage

sql-sp1-graphic-3

“Drop and Create” Approach

sql-sp1-graphic-4

Two bigger takeaways:

    • The CREATE OR ALTER implementation will maintain both the object’s permissions and metadata.
    • The respective MSDN pages implement the CREATE OR ALTER statement.

sql-sp1-graphic-5

From <https://msdn.microsoft.com/en-us/library/ms187926.aspx>

New DBCC Command: Database Cloning

SQL Server 2014 SP2 (July 2016) originally introduced Database Cloning.  However, Database Cloning was not included with SQL Server 2016 RTM.  With SP1, Database Cloning is functional through SQL Server 2016.

The new DBCC command allows users to clone a production database’s metadata, schema, and table statistics without the application data.  With this functionality, users can troubleshoot a variety of issues without interfering with production systems.

Since the database clone does not contain application data, the following use cases are possible:

  • Performance investigations can take place without interfering with production. This is possible as the database clone preserves table statistics and query plans.
  • If the application data is sensitive, the database clone can allow developers, not authorized to view application data, to research performance issues.

Note the use of the DBCC command.  In this case, SQL Server will clone WideWorldImportersDW and name the database clone WideWorldImportersDW_CLONE.

sql-sp1-graphic-6

In Object Explorer, the database clone is present and is in a read-only state.

sql-sp1-graphic-7

The screenshot below shows that the size of the database clone is smaller than the original.  This implies the database clone does not contain application data.  However, take note of the database clone’s index size measurement.  The database clone’s index space allocation exceeds the database size.  This seems odd and unexpected.

sql-sp1-graphic-8

The database clone’s Fact.Sale row count leads to another oddity.  As expected, the row count is zero.  Remember, database clones should not contain application data.

sql-sp1-graphic-9

However, when evaluating the table’s properties, the database clone is suggesting that Fact.Sale consumes 48 MB and contains approximately 228K rows.

sql-sp1-graphic-10

Explaining these oddities is simple.  Remember, database cloning preserves metadata.  The table property metrics shown above (i.e. the metadata) are stored in the system views.

With metadata and table statistics preserved, the database clone can generate query plans similar to those generated in production.

When comparing the database clone’s query plan against the original’s query plan, the plans use the same operators and both return the same estimated row count.

Query plan from WideWorldImportersDW_CLONE

sql-sp1-graphic-11

Query plan from WideWorldImportersDW

sql-sp1-graphic-12

For more information on DBCC CLONEDATABASE, please refer to the link below.

Recap
In conclusion, this post has three takeaways to consider:

    • Users now have access to Enterprise functionality via lower priced editions of SQL Server 2016.
    • The CREATE OR ALTER statement can help mitigate deployment issues associated with the reimplementation of permissions and preserving the database object’s metadata.
    • Database cloning allows stakeholders to research schema issues and performance issues within a database not containing application data.

To review additional SP1 functionality, visit the links below.

Finally, a personal “thank you” to both Radha Mukkai and Ethan Miller for reviewing the post and making very welcomed recommendations.

Comments

comments