In order for your SQL Server instance to run optimally, there are many SQL Server best practices that you need to follow. The SQL Health Check that you can have us perform on your instances looks at many of these. SQL Server 2016, which at the time of this writing is in a Release Candidate 0 state of readiness, has been changed to handle several of these best practices. This post will look at several of these changes.
Some of the SQL Server Best Practices
Instant File Initialization
The first change appears during the installation of SQL Server 2016 itself. One of the best practice items that we look for in our Health Check is that the SQL Server Database Engine service has the “Perform Volume Maintenance Task” security privilege. SQL Server 2016 now allows the setting of this privilege right in the install screen.
The benefit of having this privilege set is that it allows SQL Server to utilize “Instant File Initialization” (IFI). When IFI is not set, then any growth of a database’s data file will be zero-initialized… that is, the entire growth portion of the file is overwritten with zeros (note that database log files are always zero-initialized… this is needed for the crash recovery process). During the time that the database data file is being zero-initialized, no other activity can occur in the database. This setting not only affects growths of data files, but also affects database restores and adding new files to a filegroup. Kimberly Tripp has an excellent post that explains this in more detail, and the kind of performance impact that can be obtained by having IFI enabled.
However, configuring the instance to utilize IFI has a potential negative security side-effect. If you have just dropped a database, and a growth occurs on another database, it could use the part of the drive that the other database was using. And although it is extremely unlikely, the pages could possibly line up. With advanced techniques, one could then read the data that was on those pages. Obviously, zero-initializing the pages would eliminate this problem. It is also obvious that there are a lot of circumstances that have to line up to allow this potential security issue…but the chance of it occurring will never be zero with IFI enabled.
Because of the performance impact, and extremely low security risk, most SQL Server professionals recommend having this option set.
tempdb
Ahh, tempdb. In SQL Server, that database does so much… it’s where temporary tables and table variables are stored. It’s where row version activity is stored. It’s where running queries will spill out temporary workspace needed for the query. And on, and on, and on. On busy systems, having this database running optimally is of vital importance. And there are several best practices that need to be observed here.
First up is the number of data files needed for tempdb. On a busy system that is creating a lot of temporary objects (specifically temporary tables and table variables), the creation of these objects creates a hot-spot in one of the database file’s internal page types that stores information about the allocation of these objects. However, each database file has these internal pages. Frequently, this contention can be greatly reduced by simply adding additional database files to this database. The specific advice for how many files to have has changed over time, and there is disagreement over the exact number of files to have. What isn’t disagreed over is that additional files are needed. And once again, during the SQL Server 2016 installation, this can be set, as seen in the following screen shot:
This screen shot shows several of the changes that are now available in the installation process. First off is the number of data files. Note that the installation limits the number of data files to the lower of 8 or the number of CPUs presented to SQL Server (one of the methods of determining how many files to have).
The next settings that this screen shot shows is the new default size and autogrowth values. Technically, these changes are made to the model database. However, they apply to all new databases, including tempdb when it is created. 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.
The next setting available in this screen is the directory to place the data and log files in. Specifically, you can have tempdb data files in multiple directories. During the installation, these files are created in the directories in a round-robin fashion.
Trace Flags
There are three specific trace flags that are commonly set in SQL Server. If trace flag (TF) 1117 is enabled, then whenever a data file grows, all of the files in that database will also grow. In SQL Server 2016, this trace flag has been replaced with the AUTOGROW_ALL_FLAGS or AUTOGROW_SINGLE_FILE option of the ALTER DATABASE command, and setting TF 1117 no longer has any effect. Since it is set as part of the ALTER DATABASE command, it can be controlled at the database level, where TF 1117 was a setting across the entire instance. Furthermore, because this setting is so valuable to have set in tempdb, by default tempdb is set with this feature enabled.
TF 1118 controls whether an extent can have pages belonging to multiple objects (known as a mixed extent), or to a single object (known as a dedicated extent). Using mixed extents allows having small tables use the absolute minimum amount of storage space…which isn’t really that much of an issue as it was in SQL 7.0 when it was added. Using dedicated extents can also help in the contention when creating lots of objects really fast. In SQL Server 2016, this trace flag has been replaced with the SET MIXED_PAGE_ALLOCATION option of the ALTER DATABASE command (and like TF 1117, TF1118 no longer has any effect). Furthermore, in the tempdb database, this setting is also enabled by default.
The next trace flag to talk about is TF 4199. This trace flag controls whether changes to how the query optimizer functions are used or not. In prior versions of SQL Server, the changes were not used unless this TF had been enabled. Starting in SQL Server 2016, most of these behaviors are enabled if the database is in the SQL Server 2016 compatibility level.
