Enabling IFI on Setup in SQL Server 2016

SQL Server 2016 has added a couple of nice new options to the setup experience. First they added the ability to have multiple tempdb files on install, a nice time saver for later. And now, with CTP 3.0 they have added the ability to enable IFI on install.

What is IFI, and how do I get it?

IFI stands for instant file initialization, and if you are not aware, enabling this allows SQL Server to grow data files almost instantaneously. Without this enabled the data file space has to be claimed and then filled with zeroes, something that is a long and arduous tasks, especially on slower spinning media type storage.

This is only relevant to data files, for security and integrity reasons the log files still need to be zeroed out.

Enabling IFI is actually quite a simple task, you just need to add the SQL service account to the Perform Volume Maintenance Tasks section of the Local Security Policy and then restart the SQL Server service.

What does SQL 2016 do differently?

Prior to SQL Server 2016 (CTP 3.0) you would need to manually add the SQL account to the Perform Volume Maintenance Tasks (PVMT) section of the Local Security Policy (secpol). Now you can have the installer take care of this for you. That really helps with not forgetting to do it later, which can cause some serious performance problems down the road.

Continue reading

BUG with Availability Groups and sys.master_files

I recently came across a bug with SQL Server and Availability Groups whereby catalog view data is incorrectly reported on all secondary replicas.

This bug has the potential for putting the availability of your environment at risk as reporting around capacity could be calculated incorrectly.

Continue reading

BCP and Code Page Fun

Quick, what’s the fastest and easiest way to get data out of a SQL Server table?

Time’s up. It’s BCP.

If you aren’t familiar with BCP it is a command line utility to bulk copy data out of, and in to tables. It has been around for donkeys years (technical term), and while it doesn’t have the frills of SSIS it is fast, and lightweight. DBAs have been using it for years, but it may not be something that you’ve heard of.

Recently, while BCPing some data between servers I ran into an interesting problem which caused the data imported to be different than that exported. How does that happen? Code pages…

Continue reading

© 2015 SirSQL

Theme by Anders NorenUp ↑