SQL 2016 – Automatic TempDB Configuration

★★★★★

★★★★

★★★

★★

★

Various KBs, whitepapers and blogs have outlined the need for the creation of multiple, TEMPDB files, same sized files, trace flags and the like.  All of these configuration options increase the scalability of your SQL Server.

In an effort to simplify the tempdb configuration experience, SQL Server 2016 setup has been extended to configure various properties for tempdb for multi-processor environments. 

1. A new tab dedicated to tempdb has been added to the Database Engine Configuration step of setup workflow.
2. Configuration options:

Data Files

1. Number of files – this will default to the lower value of 8 or number of logical cores as detected by setup. The value can be increased as required for a specific workload. The file names for secondary data files will follow the tempdb_mssql_#.ndf naming convention where # represents the file ordinal for each additional file.
2. Initial size  - is specified in MB and applies to each tempdb data file. This makes it easier to configure all files of same size. Total initial size is the cumulative tempdb data file size (Number of files * Initial Size)  that will be created.
3. Autogrowth – is specified in MB (fixed growth is preferred as opposed to a non-linear percentage based growth) and applies to each file. The default value of 64MB was chosen to cover one PFS interval. Since TF 1117 is enabled by default for tempdb, all files will grow at the same time by the specified size. Total autogrowth reflects the cumulative size the database will grow each time an autogrow is triggered.

Note: If you specify a very large initial size or autogrowth value, ensure that Instant File Initialization is enabled for SQL Server service startup account.

4. Data directories – specify multiple folders/drives to spread the data files across several volumes. Each file will be placed in a round-robin manner. For example: if you have specified 8 data files and 3 volumes – files 1,4,7 will go to vol 1; files 2,5,8 will go to vol 2; files 3,6 will go to vol 3.

Log File

1. Initial size  - is specified in MB and log file with that size is created.
2. Autogrowth – is specified in MB (fixed growth is preferred as opposed to a non-linear percentage based growth)

A default value of 64MB is provided to so that the number of Virtual Log Files (VLFs) during initial creation is a small and manageable number and with appropriate size so that the unused log space can be reclaimed easily.

Enabling all these configuration settings as part of the standard setup experience allows one to deploy and run at peak scalability levels.

The following is a screen capture from SQL Server 2016 CTP3 setup showing the various TEMPDB options and defaults setup allows.

Data Driven Event

On March 10th Microsoft did host an event in NY called "Data Driven".

The first keynote was done by the CEO Satya Nadella

There were a lot of exciting announcements. I'd like to share some of the key moments

SQL is the leader

SQL Server on Linux

The demos were done using SQL on Linux

Going after the competitors

* Software Agreement

SQL Server 2016

Here is the link to the recording

https:/en-us/server-cloud/dat/www.microsoft.com/a-driven.aspx


Exiting times are coming for the SQL lovers

Announcing SQL Server on Linux

Today Microsoft anounced that SQL Server now runs on Linux. I've been working with SQL since 6.0 and never believed this could happen. Microsoft is really moving foward in the Open Source Arena. I think great times are coming for SQL lovers. Enjoy !!

Link to the original post

https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/


Apply for the preview

https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx?utm_content=buffereb0d2&utm_medium=social&utm_source=twitter.com&utm_campaign=buffer

SQL Server 2016 Release Candidate (RC0)

What's New in SQL Server 2016 Release Candidate (RC0)

Updated: March 3, 2016

Applies To: SQL Server 2016 Preview

This topic summarizes the new features in the current monthly build of SQL Server 2016 Release Candidate (RC0). The topic also contains links to more detailed component level what's new information for the current month and previous monthly builds.

Download

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

 For the current release notes, see SQL Server 2016 Release Notes.

Database Engine - new this month

Support for UTF-8 – bcp Utility, BULK INSERT, and OPENROWSET now support the UTF-8 code page. For more information, see those topics andCreate a Format File (SQL Server).

New string functions – STRING_SPLIT (Transact-SQL) and STRING_ESCAPE (Transact-SQL) are added.

New JSON_MODIFY function. The built-in support for JSON also now includes the JSON_MODIFY (Transact-SQL) function. Use theJSON_MODIFY function to update the value of a property in a JSON string and return the updated JSON string.

New or improved in Stretch Database

• New preview version on Azure. A preview of a new version of Stretch Database on Azure that supports one petabyte of data is now available. To register for preview access, go to SQL Server Stretch Database preview.
• Azure Active Directory authentication. The option to use a federated service account for SQL Server to communicate with the remote Azure SQL Database server when certain conditions are true no longer requires a trace flag. For more info, see Enable Stretch Database for a database.

Parallel scan of nonclustered indexes on MEMORY_OPTIMIZED tables: Now all indexes on MEMORY_OPTIMIZED tables support parallel scan. This speeds the performance of analytical queries that scan large sets of data.

For more detailed what's new information and information on previous months, see What's New in Database Engine.

New values for the model database and default values for new databases (which are based on model). The initial size of the data and log files is now 8 MB. The default auto-growth of data and log files is now 64MB.

Autogrow options: Trace flag 1117 is replaced by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE, and trace flag 1117 has no affect. For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL) and the new is_autogrow_all_files column of sys.filegroups (Transact-SQL).

Allocation of mixed extents: Trace flag 1118 is replaced with the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, and trace flag 1118 has no affect. For more information, see ALTER DATABASE SET Options (Transact-SQL), and the new is_mixed_page_allocation_on column ofsys.databases (Transact-SQL).

SQL Server Management Tools: SQL Server Management Tools is no longer installed from the main feature tree; for details see Install SQL Server Management Tools.

For more detailed what's new information and information on previous months, see What's New in Database Engine.

Analysis Services - new this month

PowerShell for Tabular models at 1200 compatibility level

RC0 completes the PowerShell enhancements for Tabular models created at the SQL Server 2016 version. You can use all of the applicable cmdlets, plus two new cmdlets specific to Tabular mode: Invoke-ProcessASDatabase and Invoke-ProcessTable.

SSIS Tasks for Tabular models at 1200 compatibility level

SSIS tasks and destinations against a SQL Server 2016 tabular model use tabular object representations instead of multidimensional. For example, in the latest SSDT, when you select objects to process, the Processing Task will automatically detect it is a Tabular model and show Tabular objects instead of measuregroups and dimensions.

Microsoft.AnalysisServices.Tabular namespace for Tabular 1200 programmability in AMO

Analysis Services Management objects is updated to include a new Tabular namespace for managing a Tabular instance of SQL Server 2016 Analysis Services (at compatibility level 1200), as well as provide the data definition language for creating or modifying Tabular models programmatically. Visit Microsoft.AnalysisServices.Tabular to read up on the API.

Display folders for Tabular models at 1200 compatibility level

Defined in SQL Server Data Tools and rendered in client applications like Excel or Power BI Desktop, display folders help you organize large numbers of measures into individual folders, adding a visual hierarchy that is easier for report users to navigate.

Integration Services - new this month

SSIS Designer creates and maintains packages for SQL Server 2016, 2014, or 2012. You can now use SSIS Designer in SQL Server Data Tools (SSDT) for Visual Studio 2015 to create and maintain packages that target SQL Server 2016, SQL Server 2014, or SQL Server 2012. In the property pages for an Integration Services project, on the General tab of Configuration Properties, select the TargetServerVersion property and choose SQL Server 2016, SQL Server 2014, or SQL Server 2012. To get SSDT, see Download Latest SQL Server Data Tools.

Support for server-wide default logging level. In SQL Server Server Properties, under the Server logging level property, you can now select a default server-wide logging level. You can pick from one of the built-in logging levels - basic, none, verbose, performance, or runtime lineage - or you can pick an existing customized logging level. The selected logging level applies to all packages deployed to the SSIS Catalog. It also applies by default to a SQL Agent job step that runs an SSIS package.

HDFS File Destination now supports ORC file format. The HDFS File Destination now supports the ORC file format in addition to Text and Avro. (The HDFS File Source supports only Text and Avro.) For more info about this component, see HDFS File Destination.

For more detailed what's new information and information on previous months, see What's New in Integration Services.

Master Data Services - new this month

Derived Hierarchy Management Experience Improved

The derived hierarchy management experience in MDS has been improved. For more information about this feature, see Create a Derived Hierarchy (Master Data Services).

For more detailed what's information on previous months, see What's New in Master Data Services (MDS).

Reporting Services - new this month

For more detailed what's new information and information on previous months, see What's New in Reporting Services (SSRS).

SQL Server R Services

For more detailed what's new information and information on previous months, see What's New in SQL Server R Services.

Did this Article Help You? We’re Listening

What information are you looking for, and did you find it? We’re listening to your feedback to improve the content. Please submit your comments to sqlfeedback@microsoft.com

More information about SQL Server 2016

For more information about SQL Server 2016 Release Candidate (RC0), or to download the SQL Server 2016 Release Candidate (RC0) data sheet, see SQL Server 2016 Preview.

https://msdn.microsoft.com/en-us/library/bb500435.aspx