Some tips for using SQL Server 2016 file and filegroups
Do not set the autoshrink feature.
Autoshrinking results in some performance degradation, therefore you should shrink the database
manually or create a scheduled task to shrink the database periodically during off-peak times,
rather than set Autoshrink feature to ON.
Place log files on RAID 1+0 (or RAID 1) disks.
By using so, you will have better protection from hardware failure and better write performance.
In general RAID 1+0 will provide better throughput for write-intensive applications. Most common
alternative to RAID 1+0 is RAID 5. Generally, RAID 1+0 provides better write performance than
any other RAID level providing data protection, including RAID 5.
If you create several tempdb data files, make each data file the same size.
In this case, SQL Server 2016 will load data in tempdb database in optimal proportional-fill way.
Set the reasonable sizes for the database and transaction log.
First of all, before database creation, you should estimate how large your database will be.
To estimate the reasonable database size, you should estimate the size of each table individually,
add some additional space (10-20%) and then add the values obtained. The general rule of thumb
for setting the transaction log size is to set it to 20-25 percent of the database size. The
smaller the size of your database, the greater the size of the transaction log should be, and
vice versa. For example, if the estimation database size is equal to 100Mb, you can set the size
of the transaction log to 40-50Mb, but if the estimated database size is over 1000Mb, the 200Mb
may be enough for the size of the transaction log.
Consider dividing tables and indexes into partitions based on value ranges.
The data in partitioned tables and indexes is horizontally divided into units based on value
ranges and can be spread across more than one filegroup in a database. Partitioning can simplify
administering and monitoring the large tables and indexes because maintenance operations can be
performed against single partitions instead of an entire table or index.
For heavily accessed tables, place these tables in one filegroup and place the table’s
indexes in a different filegroup on different physical disk arrays.
This will improve performance, because separate threads will be created to access the tables
and indexes.
Set a reasonable size of the autogrow increment.
Setting a database to automatically grow results in some performance degradation, therefore you
should set a reasonable size for the Autogrow increment to avoid automatically growing too often.
Try to set the initial size of the database, and the size of the Autogrow increment, so that
automatic growth will occur once per week or less. You can set the Autogrow increment in the
FILEGROWTH option in the CREATE DATABASE or ALTER DATABASE statement.
Consider placing the tempdb log files on other physical disk arrays than those with the
data files.
Because logging is usually more write-intensive, it is important that the disk arrays
containing the tempdb log files have sufficient disk I/O performance.
For the TEMPDB database, create 1 data file for each CPU on the host server.
Keep in mind, that dual core counts as 2 CPUs; logical procs (hyperthreading) do not.
Consider using the FILESTREAM feature.
Because FILESTREAM uses the NT system cache for caching file data, the SQL Server buffer pool
is not used; therefore, this memory is available for query processing.
Keep in mind, that FILESTREAM is not automatically enabled when you install or upgrade SQL Server.
To use FILESTREAM, you must enable FILESTREAM by using SQL Server Configuration Manager, then you
must create or modify a FILESTREAM-enabled database and then you must create or modify a table so
that it contains a varbinary(max) column with the FILESTREAM attribute. After all the steps above
completed, you can use Transact-SQL and Win32 to manage the FILESTREAM data.
For heavily accessed table with text/image columns, place the table and text/image columns
in the different filegroups on different physical disks.
You can use the CREATE TABLE statement with the TEXTIMAGE_ON option to place text/image columns
in a different filegroup.
Consider partition very large table and place these partitions on separate filegroups.
Because you can perform backups on individual filegroups, you can independently perform backup
operations on partitions by placing each partition on its own filegroup.
Set the maximum size of the data and log files.
Specify the maximum size to which the files can grow to prevent disk drives from running out
of space.
Consider placing the log files on other physical disk arrays than those with the data files.
Because logging is usually more write-intensive, it’s important that the disk arrays containing
the SQL Server log files have sufficient disk I/O performance.
Do not create many data and log files on the same physical disk array.
Leaving the autogrow feature ON for the data and for the log files can cause fragmentation of
those files if there are many files on the same physical disk array. In most cases, it’s enough
to have 1-2 database files on the same physical disk.
If you have read-only tables, place these tables in different filegroup and use the
ALTER DATABASE statement to make just this filegroup READ ONLY.
This not only increases read performance, it prevents any data changes and allows you to control
permissions to this data.
Consider grouping the user objects with similar maintenance requirements into
the same filegroup.
You can create a user-defined filegroup and create some tables in it to run maintenance tasks
(backups, DBCC, update statistics, and so on) against these tables. For example, you can place
a table in its own filegroup and can backup and restore only this table.
Use the Windows System Monitor to determine the appropriate number for the data and log files
on your server.
In Windows System Monitor, check the “PhysicalDisk: % Disk Time” counter. This counter monitors the
percentage of time that the disk is busy with read/write activity. If the “PhysicalDisk: % Disk Time”
counter is more than 90 percent check the “PhysicalDisk: Current Disk Queue Length” counter to
determine how many system requests are waiting for disk access. If the “PhysicalDisk: Current
Disk Queue Length” counter is more than 2, consider placing some data or log files to an additional
disk or server and continue monitoring once you have made your changes to ensure that your disk I/O
is optimum over the long term.