T-SQL Tuesday: All Features With Defaults

This month's T-SQL Tuesday comes to us from Andy Yun (@SQLBek) and is themed, "Just Say No to Defaults." I'm sure a lot of entries will come out talking about some of the horrible defaults for an instance of SQL Server - data and log files (location, size, and growth settings), min and max server memory, cost threshold for parallelism, and a host of others. I talked about a few of these in a previous T-SQL Tuesday and, more recently, I've pleaded for better guidance about tempdb during setup (see Connect #1380861).

I'm going to step outside of the SQL Server instance itself and talk about something slightly different; a tempting option in SQL Server Setup. Several versions ago, a new choice appeared during installation, on the Setup Role screen:

This looks like a great time- and brain cell-saving option, doesn't it? However, my inner voice is screaming, sarcastically, that it's a big easy button that suggests to Default All The Things!

Default All The Things!
Is it a good idea to press that easy button? I'm guessing that, in most cases, it is not.

At right is the set of options (all but one) that get selected when you choose All Features with Defaults.

I can't imagine a scenario where you want every single feature available to be running on the same machine; even in the rare case where this is the requirement, it's kind of like my arguments about SELECT *: if you really, really want everything, it should be an intentional choice, and it should be a little bit harder to do.

Assuming, then, that when you press this easy button, at least one checkbox is checked that you wouldn't have chosen manually, let's compare two aspects of installation that are impacted no matter how many of those features you would ultimately use: time and disk space. We'll look at choosing the easy button vs. manually selecting just the engine and client tools (explicitly: Management Tools - Complete). While that is definitely a minimal install that may not be the most common set of options, it serves as a decent baseline, if we're not going to compare every single permutation (which I would have probably done, if only T-SQL Tuesday were on the third Tuesday of every month).

Time

Choosing the easy button option actually doesn't save you any time, except for the checking of the checkboxes to indicate the features you really do want, on that one screen, once. I tried it. You're still prompted for the all of the same settings (like instance configuration and server configuration), but now you're also prompted for configuration of items like Analysis Services, Reporting Services, and Distributed Replay. There are more screens, and it takes longer on each screen (at least in my observations) - presumably to enumerate through all of those services and features to determine service accounts, disk space requirements, and so on.

It wouldn't really be fair to try to time the interactive portion of setup, where you're configuring some of these other options, but it literally felt like it took more time and effort - both because of the additional options and because of the extra time taken (even on the same screens).

For the non-interactive portion of Setup, it took 16 minutes and 53 seconds to install all features, while it only took 9 minutes and 20 seconds to install the engine and client tools:

So, clearly, this can add to the amount of time you're taking, and the extra 20 seconds it would have taken you to manually select the options you wanted would easily have paid for itself. (This also holds true for unattended installs - even though you're not waiting on those, it's still time each system has to spend configuring services and features that may never even be used - and if you're adding a minute per install, and you're deploying 100 or 1,000 instances...)

Disk space

Not surprisingly, installing more features consumes more disk space. It's not quite as bloated as Windows itself has become, but there is a stark difference between a minimal SQL Server install and the "All Features" option. I created a new Windows 10 VM with nothing (aside from the .NET 3.5 feature) installed, took a snapshot, then ran both types of setup after restoring to the snap. Here was the impact to disk space from initial (left) to All Features (middle) and Engine & Client Tools only (right):

So an additional 3.8 GB of space is required to house All Features, even if you're not going to use them. Again, given more time, I could have tested all permutations, to determine the heaviest hitters.

SQL Server 2016

While it isn't on by default, even when you choose All Features, if you check the Polybase option during SQL Server 2016 setup (or click the handy "Select All" button at the bottom of the feature selection window), you will most likely get this error:

And for searchability:

Java is required to support the Polybase feature. If you want to use Polybase, that's fine, but don't be shocked by this prompt to install Java. :-) I saw a lot of reactions to a prompt from SQL Server to install software from Oracle - from bewilderment to humor to sarcasm to utter confusion. This is just one more argument for not blindly selecting all options.

Summary

It's easy enough to add features to SQL Server after the fact; much more cumbersome to remove unneeded features later (or put up with their additional disk space). Please think twice before pressing that easy button!