Hidden Gems In SQL Server 2016 Setup

This past week, Microsoft revealed that SQL Server 2016 will be available in June. Exciting news indeed! With that said, I thought I’d take a few moments to point out some neat new features housed in SQL Server 2016 Setup.
SQL Server 2016 has some new features and I’m sure you’ve heard quite a bit about them already. The thought of R and PolyBase in SQL Server sounds great. SQL Server on Linux sounds even grander; however, I’m much less easy to impress.

SQL Server 2016 Setup:  Hidden Gems

In fact, I feel the two coolest features in SQL Server 2016 setup are ones that every customer will use much more than the latter features mentioned.  They’re just a LOT less hyped.
Many, if not the majority, of customers, neglect to configure TempDB to best practice, and an even larger population does not configure instant file initialization (IFI) aka: “Perform Volume Maintenance Tasks”.
IFI is an important privilege that is often overlooked. Local and domain administrators have this privilege; but, as we all know you should not have your SQL Server service account configured as either of those. This leaves us with having to add the security policy to the service account and this is where it often gets lost in the mix. IFI is important because it allows space to be allocated without first zeroing out all the data that may exist. In some lab scenarios, I’ve seen a database be restored up to 50% faster with IFI granted. The process of zeroing space is expensive and this helps us avoid it altogether. It is now part of the SQL Server 2016 setup!
Here’s an example of IFI in action (4 core, 4 GB RAM, 4 15k rpm disks configured with RAID 10):
The next “new feature” is automatic TempDB configuration. Clicking on the TempDB tab reveals the automatic configuration at work. My development machine has 4 cores and the setup process has configured TempDB with 4 data files; however, you’ll still need to appropriately configure the initial size and growth for the data and log files.
With SQL Server 2016, the future is looking brighter and I can’t wait to see it in production for the first time!