The Right Database Monitoring Tools Make All the Difference
Tuesday, June 26, 2018 - click here to learn more and to register
Service Pack 1 for SQL Server 2016 was released today, and if you are running Standard or Express Edition, you are running out of excuses to upgrade. As I explained in a blog post today, Microsoft has announced at the Connect(); // 2016 conference (and on the SQL Server Team Blog) that they have opened up many Enterprise features to Standard, Web, Express, and even LocalDB. But what new features have been added?
I'm going to touch on several new features that are available within all editions of Service Pack 1, because there are some valuable things here, and they may get lost in all the fanfare about so many Enterprise features being enabled for everyone.
CREATE OR ALTER
In SQL Server 2016, they delivered new
DROP IF EXISTS syntax, which
allows you to run a
DROP command without checking for the existence
of the object first. In SP1, we get
CREATE OR ALTER for modules - this
is much more useful, as it allows you to deploy the script for a view, procedure,
function, or trigger without needing to know if it already exists, and without worrying
about losing permissions or dependencies if your currents logic issues a
and then a
CREATE. This batch will be able to run multiple times without
error (go ahead, hit F5 as much as you like):
CREATE OR ALTER PROCEDURE dbo.TestOne AS BEGIN PRINT N'hi'; END GO CREATE OR ALTER FUNCTION dbo.TestTwo(@i int) RETURNS int AS BEGIN RETURN (@i+1); END GO CREATE OR ALTER FUNCTION dbo.TestTwo(@i int) RETURNS int WITH SCHEMABINDING -- can also be used to add/remove SCHEMABINDING AS BEGIN RETURN (@i+1); END GO
You can't use this approach to change a function type to a different type, for
example from a scalar function to an inline table-valued function, just like you
couldn't with a regular old
ALTER. If you try to change that scalar
function to a table-valued function:
CREATE OR ALTER FUNCTION dbo.TestTwo(@i int) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT x = @i+1); GO
You get this error:
Cannot perform alter on 'dbo.TestTwo' because it is an incompatible object type.
Note that if you have an indexed view, a subsequent
CREATE OR ALTER
behaves just like a normal
ALTER would - it modifies the schema-bound
view, as you would expect, which doesn't affect permissions, but does drop indexes.
A new USE HINT query option
This looks like the beginning of a replacement for
which requires sysadmin privileges.
OPTION (USE HINT('')) does not
require sysadmin, and also doesn't make you memorize trace flag numbers (instead,
you get to memorize verbose strings :-)). Here is a quick example:
SELECT [column] FROM dbo.[table] WHERE x = 1 OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));
Currently there are 9 hints supported (you can see them listed in
and these are the trace flags they emulate:
|Trace Flag||USE HINT|
|4137 / 9471||ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES|
As an easier way to create an empty database with statistics, this functionality
SQL Server 2014 SP2 (see posts on this by
Melissa Connors and
Erin Stellato). There is a new property for
isClone, and the clone includes CLR, Filestream, FileTable,
In-Memory OLTP, and Query Store objects. There are options for
NO_QUERYSTORE, which are self-explanatory; these options do not
exist in SQL Server 2014 SP2.
Are Lock Pages in Memory and Instant File Initialization enabled?
We've used all kinds of ways to determine if the current instance's service account has the ability to use Lock Pages in Memory and Instant File Initialization; now there are DMV columns to make it much easier.
SELECT sql_memory_model, sql_memory_model_desc FROM sys.dm_os_sys_info; /* possible values: ---- ------------ 1 CONVENTIONAL 2 LOCK_PAGES 3 LARGE_PAGES */ SELECT instant_file_initialization_enabled FROM sys.dm_server_services; /* possible values: ---- Y N */
Manual Change Tracking Cleanup
In some cases, the automatic background cleanup of Change Tracking does not work
fast enough, and the growth of the side tables becomes unmanageable. There is a
new stored procedure,
can be called manually or on a schedule. This procedure was already added to SQL
Server 2014 SP2 (see
and you should expect that KB article to be updated to reflect that the procedure
is now in SQL Server 2016 SP1.
Quieter In-Memory OLTP
SQL Server 2016 RTM started logging additional information to the SQL error log to help support troubleshooting In-Memory OLTP; however, in some cases this was flooding the log. This has been dialed back by default in Service Pack 1, so you should see the log revert to SQL Server 2014 behavior.
Enhanced tempdb visibility
On service start, a new message gets written to the error log, indicating when the data files for tempdb are not configured with the same size and autogrow settings. The message also indicates the number of files, so that issue is easy to spot too. Here is an example:
The tempdb database has 5 data file(s).
This is described for SQL Server 2014 SP2 in KB #3170020.
Several ShowPlan additions
There have a bunch of enhancements to the information included in ShowPlan output, which you might imagine will eventually be consumed by Management Studio (in fact some already are), and 3rd party tools like Plan Explorer. Many of these were recently added to SQL Server 2014 SP2:
- There is a new
EstimatedRowsReadattribute to highlight issues with residual pushdown predicates (see Rob Farley's Connect item and KB #3107397). Example:
<RelOp ... EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" ...>
- Information about enabled trace flags is now included (see
#3170115). This is all
worthy of a separate post, but a quick example of the trace flag information:
<TraceFlags IsCompileTime="true"> <TraceFlag Value="9130" Scope="Session" /> </TraceFlags> <TraceFlags IsCompileTime="false"> <TraceFlag Value="8649" Scope="Global" /> </TraceFlags>
- Memory grant warnings are added to actual showplan output. From
#3172997: "When SQL Server detects a scenario in which memory grant usage
is inefficient, the
MemoryGrantWarningelement is added to actual Showplan XML. This element contains information about initial request, granted memory, and maximum used grant." Also see KB #3170116.
- Some additional memory information (see
#3170112) and query-level runtime information can be seen in the following
ShowPlan XML output:
<MemoryGrantInfo ... MaxQueryMemory="660568" /> <OptimizerHardwareDependentProperties ... MaxCompileMemory="2626808" /> <QueryTimeStats CpuTime="0" ElapsedTime="0" />
- New information about parameter data types is now included in the output,
<ParameterList> <ColumnReference Column="@a" ParameterDataType="int" ... <ColumnReference Column="@b" ParameterDataType="decimal(12,2)" ... </ParameterList>
- There is improved information about tempdb spills in actual ShowPlan output, including information about threads, amount of memory, and number of writes to tempdb. For more information, see KB #3107400.
Lighter query profiling
SQL Server 2014 added a DMV,
you to monitor the progress and metrics of each operator in a running query, as
long as one of the
SET SHOWPLAN options or
SET STATISTICS PROFILE
were enabled. This could not be enabled globally because of the overhead involved
and was recommended for isolated tuning and troubleshooting efforts.
You can now get per-operator query execution metrics without full showplan overhead.
If you enable trace flag 7412 globally, or have an Extended Events session running
query_thread_profile, you can use
view live query statistics, and use the new
DMF. The DMF takes a session ID and lets you see a query someone else is currently
running; it works with both this new lightweight method, and also if the query is
using the heavier collection method.
Other improved diagnostics
- Extended Events and PerfMon expanded to help better troubleshoot Availability
Group latency (see
#3173156 for the 2014 SP2 story).
sys.dm_db_incremental_stats_propertiesDMF to expose per-partition stats details - see KB #3170114. (Erin Stellato shows a workaround here.)
- New BIGINT columns to help better correlate queries (by query_hash/query_plan_hash)
between Extended Events (which use UINT64) and DMVs (which use varbinary). You
can see the full set by running the following query in SQL Server 2016 SP1:
SELECT [object_name], [name], [type_name] FROM sys.dm_xe_object_columns WHERE [name] like N'%plan_hash%';
object_name name type_name query_parameterization_data query_plan_hash binary_data query_trace_column_values query_plan_hash uint64 query_trace_column_values query_plan_hash_signed int64 query_memory_grant_blocking blocking_query_plan_hash uint64 query_memory_grant_wait_begin query_plan_hash uint64 query_memory_grant_wait_begin query_plan_hash_signed int64 query_memory_grant_wait_end query_plan_hash uint64 query_memory_grant_wait_end query_plan_hash_signed int64
Those are quite a few appealing new features being added to SQL Server 2016 through its first service pack (and if you fish around, you might see a couple more things). Between these new features, and so many features now being available in all editions, very few arguments remain for staying on older versions of SQL Server.
Follow these links for downloads, tips, and more information:
Last Update: 2016-11-16
About the author
View all my tips