Chapter 4
SQL Server Configuration
In This Chapter
This chapter on configuring SQL Server in a Linux environment is divided into three main topics: using the mssql-conf utility to configure SQL Server, which is required in Linux environments; using Linux-specific kernel settings and operating system configurations; and using some traditional SQL Server configurations for both Windows and Linux installations.
The first section covers the mssql-conf utility, which includes some of the functionality available with the SQL Server Configuration Manager tool for Windows, including configuring network ports or configuring SQL Server to use specific trace flags. In addition, mssql-conf can be used to configure initialization and setup of SQL Server, set up the system administrator password, set the collation of the system databases, set the edition of SQL Server, and update a configuration setting. The second part of the chapter covers some Linux kernel settings, which can be used to improve the performance of SQL Server, as well as some operating system configurations such as transparent huge pages. The third section of the chapter covers some of the most popular SQL Server configuration settings, and most of the section applies both to Windows and Linux implementations.
NOTE
For additional information on configuring SQL Server for high performance, see my book High Performance SQL Server (Apress, 2016).
The mssql-conf Utility
Chapter 1 briefly covered the mssql-conf utility, where it was used to install and configure SQL Server. This chapter covers this tool in more detail. Let’s start with a scenario in which you create a virtual machine with SQL Server already installed. In this scenario, you are required to perform only two operations: set a system administrator (sa) password and start SQL Server.
Here is the execution and output of the first of these steps on a newly created virtual machine with Red Hat Enterprise Linux:
After you set the system administrator password, start your SQL Server instance by running the following statement:
To run mssql-conf, you must have the proper permissions, either superuser or as a user with membership in the mssql group. If you try running mssql-conf without the right permissions, you’ll get an error:
The available arguments of the mssql-conf utility are shown next:
As suggested in the preceding output, you can use the mssql-conf utility to perform several configuration options, such as initialize and set up SQL Server, set the sa password (as shown earlier), set the collation of the system databases, enable or disable one or more trace flags, set the edition of SQL Server, and assign the value of a setting. This means that the mssql-conf utility can be used to change a configuration setting in two different ways: using an mssql-conf option such as traceflag or set-sa-password, or changing a configuration setting such as memory.memorylimitmb. To list the supported configuration settings, use the following command:
These are the current supported settings:
coredump.captureminiandfull Capture both mini and full core dumps
coredump.coredumptype Core dump type to capture: mini, miniplus, filtered, full
filelocation.defaultbackupdir Default directory for backup files
filelocation.defaultdatadir Default directory for data files
filelocation.defaultdumpdir Default directory for crash dump files
filelocation.defaultlogdir Default directory for log files
hadr.hadrenabled Allow SQL Server to use availability groups for high availability and disaster recovery (availability groups are covered in Chapter 7)
language.lcid Locale identifier for SQL Server to use (such as 1033 for US - English)
memory.memorylimitmb SQL Server memory limit (megabytes)
network.forceencryption Force encryption of incoming client connections
network.ipaddress IP address for incoming connections
network.kerberoskeytabfile Kerberos keytab file location
network.tcpport TCP port for incoming connections
network.tlscert Path to certificate file for encrypting incoming client connections
network.tlsciphers Transport Layer Security (TLS) ciphers allowed for encrypted incoming client connections
network.tlskey Path to private key file for encrypting incoming client connections
network.tlsprotocols TLS protocol versions allowed for encrypted incoming client connections
sqlagent.databasemailprofile SQL Agent Database Mail profile name
sqlagent.errorlogfile SQL Agent log file path
sqlagent.errorlogginglevel SQL Agent logging level bitmask: 1=Errors, 2=Warnings, 4=Info
telemetry.customerfeedback Telemetry status
telemetry.userrequestedlocalauditdirectory Directory for telemetry local audit cache
To set the SQL Server memory limit, for example, you can use the memory.memorylimitmb setting, as shown next:
As with any of the mssql-conf changes, you will need to restart SQL Server to apply the changes:
By default, SQL Server, only when running on Linux, can use up to 80 percent of the physical memory available on the server. But in some cases, such as in a server with a large amount of memory, this default may not be appropriate, leaving some memory unused. Let’s say, for example, that you have 512GB of memory on a dedicated database server. With the default 80 percent allocated to SQL Server, it would leave more than 100GB for the operating system. A large part of that memory could be allocated to SQL Server as well.
In a similar way, to set the default SQL Server data directory, you can use the filelocation.defaultdatadir setting as shown next, assuming the directory exists:
In this specific case, you would also need to grant the proper permissions to the mssql group and user to the data directory:
You can also change the default directory for the transaction log files using the filelocation.defaultbackupdir setting. To set the default directory for backup files in a similar way, you can use the following:
As with the data directory, you need to grant the proper permissions to the mssql group and user, as shown earlier.
To change the port used by SQL Server, you can use the network.tcpport setting, as shown next. The first case shows an error when a port is already in use, followed by a successful execution:
After making such a configuration change, you will have to specify the port number every time you need to connect to the Linux instance, because there is no SQL Server Browser service on Linux to resolve it. The SQL Server Browser service on Windows would run on UDP port 1434, automatically listen for connections intended for instances running on nondefault ports, and provide the correct port to the client. For example, to connect from SQL Server Management Studio or the sqlcmd utility to use port 1435 as defined previously, you could use the following format, in which our server hostname is sqlonlinux. Make sure the TCP/IP ports are properly configured, as indicated in Chapter 1.
Finally, to enable or disable SQL Server trace flags, you can use the traceflag option, as shown next:
As shown, you can enable or disable more than one trace flag at a time if you specify a list separated by spaces.
NOTE
Trace flag 3226 is used to suppress successful backup operation entries in the SQL Server error log and in the system event log. Some other very popular trace flags, such as 2371, 1117, and 1118, which are always recommended, are no longer required in SQL Server 2016 or later because their behavior is now part of the product. These trace flags are explained later in this chapter.
You can disable the trace flags using the off parameter, as shown next:
Similarly, you can unset a value to go back to the default configuration. For example, the change to TCP port 1435 can be reverted back to the default 1433 by running the following mssql-conf command:
As usual, with every configuration change, you will need to restart the SQL Server instance for the changes to take effect.
Finally, to enable availability groups on your SQL Server instance, you can run the following command. Availability groups, high availability, and disaster recovery will be covered in more detail in Chapter 7.
You can see the current configured settings by viewing the contents of the /var/opt/mssql/mssql.conf file, as shown next, which includes only the nondefault values. As such, a setting not included in this file is using the default value:
Here’s an example with a few changes performed earlier:
Using Variables
Another way to change SQL Server configuration settings is to use variables. Following are the current variables available as defined in the SQL Server documentation:
ACCEPT_EULA Accept the SQL Server license agreement when set to any value (for example, Y).
MSSQL_SA_PASSWORD Configure the SA user password.
MSSQL_PID Set the SQL Server edition or product key. Possible values include Evaluation, Developer, Express, Web, Standard, and Enterprise. You can also specify a product key, which must be in the form of #####-#####-#####-#####-#####, where # is a number or letter.
MSSQL_LCID Set the language ID to use for SQL Server. For example, 1036 is French.
MSSQL_COLLATION Set the default collation for SQL Server. This overrides the default mapping of language id (LCID) to collation.
MSSQL_MEMORY_LIMIT_MB Set the maximum amount of memory (in MB) that SQL Server can use. By default it is 80 percent of the total physical memory.
MSSQL_TCP_PORT Configure the TCP port that SQL Server listens on (default 1433).
MSSQL_IP_ADDRESS Set the IP address. Currently, the IP address must be IPv4 style (0.0.0.0).
MSSQL_BACKUP_DIR Set the default backup directory location.
MSSQL_DATA_DIR Change the directory where the new SQL Server database data files (.mdf) are created.
MSSQL_LOG_DIR Change the directory where the new SQL Server database log (.ldf) files are created.
MSSQL_DUMP_DIR Change the directory where SQL Server will deposit the memory dumps and other troubleshooting files by default.
MSSQL_ENABLE_HADR Enable availability groups.
For example, when you configure SQL Server using /opt/mssql/bin/mssql-conf setup, you need to specify at least the SQL Server edition and the system administrator password, and accept the SQL Server license agreement. You can use the following command to perform a similar unattended configuration:
You need to stop SQL Server before running the preceding command or you will get a reminder to “run sudo systemctl stop mssql-server.”
You’ll also use variables when creating SQL Server container images on Docker, as explained in Chapter 1. For example, the following command creates a SQL Server container image passing three environment variables:
In this case, the –e option is used to set any environment variable in the container. The other options used may look obvious by now and were also explained in Chapter 1.
Linux Settings
Database professionals working with some other databases in Unix environments such as Oracle may be aware of specific kernel settings or some operating system configurations required for a database server. Configuring SQL Server on Linux is pretty much like configuring it on Windows, and not much Linux-specific configuration is required.
However, a few configuration settings can help provide better performance for SQL Server in Linux, and this section describes them. Although I cover how to implement these recommendations in the most common scenarios, you may need to look at your specific Linux distribution documentation for more details on their configuration.
Kernel Settings
Microsoft recommends several CPU- and disk-related Linux kernel settings for a high-performance configuration. Table 4-1 shows the recommended CPU settings.
Table 4-1 Recommended Settings for CPU-Related Linux Kernel
Red Hat Enterprise Linux provides predefined performance tuning profiles that can help you configure these and other kernel settings automatically. Predefined performance tuning profiles have been designed to enhance performance for specific use cases, but you can also create your own profiles. For this particular case, I’ll use a predefined profile named throughput-performance to configure the previously listed settings automatically.
I can use the tuned-adm tool to activate any available tuning profile. For example, for my newly create Red Hat Enterprise Linux virtual machine, I can use the active option to see the current active profile:
I can list the available profiles, which also specifies the active profile:
My list shows the following:
balanced General nonspecialized tuned profile
desktop Optimize for the desktop use case
latency-performance Optimize for deterministic performance at the cost of increased power consumption
network-latency Optimize for deterministic performance at the cost of increased power consumption; focused on low latency network performance
network-throughput Optimize for streaming network throughput; generally necessary only on older CPUs or 40G+ networks
powersave Optimize for low power consumption
throughput-performance Broadly applicable tuning that provides excellent performance across a variety of common server workloads
virtual-guest Optimize for running inside a virtual guest
virtual-host Optimize for running KVM guests
Finally, to configure Linux to use the throughput-performance profile, I can use the following command:
I can then validate that it is, in fact, the active profile:
NOTE
For more details about the performance tuning profiles, see the Red Hat Enterprise Linux documentation at https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux or consult your Linux distribution documentation.
Table 4-2 shows the recommended disk settings.
Table 4-2 Recommended Disk Settings for Linux Kernel
The sysctl command enables you to configure kernel parameters at runtime. It basically reads and modifies the attributes of the system kernel. Linux exposes sysctl as a virtual file system at /proc/sys.
To see the value of a kernel attribute or variable, you can use sysctl followed by the variable name, as shown next. These are my defaults on a newly created virtual machine for the disk settings described earlier, typing a command per variable:
As you can see, two values do not follow the Microsoft recommended values. To change a sysctl setting, you can use the –w option, as shown next:
Note that you cannot include a space, which may be allowed with other commands, or you will get the following error messages:
You can display all values of kernel settings currently available in the system by using –a or --all, which will return a very large number of entries. An example is shown in Figure 4-1.
Figure 4-1 Partial sysctl --all output
NOTE
For more details about sysctl, see the man documentation.
You can use the blockdev command to set the disk readahead property. For example, I will start with a report of all the devices in my system:
The RA column in the report is the block device readahead buffer size. You can use the --getra and --setra options to print and set the readahead configuration, respectively. In both cases, it is in 512-byte sectors.
Run the report again:
Finally, two additional settings are recommended by Microsoft and may require a configuration change: the automatic nonuniform memory access (NUMA) balancing for multinode NUMA systems and the virtual address space. The automatic NUMA balancing for multinode NUMA systems is enabled by default on multinode NUMA systems. However, it is recommended that you disable this setting in Linux.
You can validate whether automatic NUMA balancing is enabled by default by using the following command:
You can disable the automatic NUMA balancing for multinode NUMA systems by using the sysctl command, as shown next:
NOTE
Nonuniform memory access (NUMA) is a hardware memory design used in multiprocessing, where memory access time depends on the memory location relative to the processor.
The second setting is the virtual address space; the default value of 64K may not be enough for a SQL Server installation. It is recommended that you change it to 256K, as shown next:
As you can guess, the default value for vm.max_map_count is 65530. A better value for SQL Server is the maximum limit of 256K, or 262144.
Transparent Huge Pages
Linux manages memory in pages of 4096 bytes, which means that just 1MB of memory requires 256 such pages. When you’re managing large amounts of memory in Linux, increasing the page size becomes a very important consideration. Transparent huge pages is an abstraction layer that uses large size pages, such as 2MB and 1GB sizes, and automates most of the aspects of creating and managing these large pages.
Transparent huge pages is enabled by default on most Linux distributions, and it is recommended that you leave it enabled when using SQL Server. Check your Linux documentation to validate whether transparent huge pages is enabled. For example, you can perform such validation on Red Hat Enterprise Linux using the following command:
Your current configuration setting is returned in brackets; in this case, you can see that transparent huge pages is enabled.
NOTE
Check your Linux documentation if you also need to enable transparent huge pages.
Swap File
Similar to Windows, Linux swap space can be used when the available memory is full. Inactive pages in memory are moved to the swap space when no more physical memory is available. As in Windows, swap space is not a replacement for memory because it is usually on disk and is slower than normal memory.
NOTE
See your Linux documentation for more details about configuring swap space.
To get started, you can use the swapon command to enable or disable devices and files for paging and swapping. Use the swapon -s command to display a swap usage summary by device; this is equivalent to looking at the information on /proc/swaps, as shown in the next two statements:
Last Accessed Date/Time
A final operating system recommendation is to disable the last accessed date/time timestamp at the file system level. This timestamp, also known as atime, incurs a performance penalty, because it records when each file was last accessed, basically generating write activity for every read operation. You can disable the updating of this information to improve performance. Keep in mind, however, that the atime attribute may be required by some applications, so consider disabling it only for file systems used by SQL Server.
To disable the last accessed date/time timestamp at the file system level, you can edit the /etc/fstab file—although directly editing a Linux configuration file may seem like a very advanced task for a SQL Server professional. Edit the file to add the noatime attribute to the associated mount options of the required file system. The fstab file contains a line per each file system and includes information such as the device name, the mount directory, the file system type, and the associated mount options.
In the following example, the only defined mount option is defaults:
After editing the file, the mount options now show defaults,noatime:
Finally, you can use the remount option of the mount command to reload the file system information. This option will attempt to remount an already-mounted file system:
SQL Server Configuration
This section offers an introduction to the most important SQL Server configuration settings. Most of what is covered here applies both to Windows and Linux installations. I discussed how to configure SQL Server memory in the mssql-conf section earlier in the chapter. In this section, I will start describing a Linux behavior that you should be aware of while configuring the memory available on your Linux installation.
Linux Out-of-Memory Killer
If you’re a SQL Server administrator familiar with the Windows world, you need to be aware of a Linux kernel behavior: when a server is running low in memory, the kernel starts killing processes to free up memory and stay operational. This behavior, controlled by a mechanism called the Out-of-Memory Killer (OOM Killer), is in principle an optimization designed for the kernel to use the memory allocated by applications in a better way. Because many applications allocate their memory up front and usually do not utilize all the memory allocated, the Linux kernel was designed to make memory usage more efficient by having the ability to overcommit memory, allocating more memory than it actually has physically available.
Under this design, an obvious problem will occur when applications really do use all the memory allocated. For a SQL Server implementation, SQL Server will be the application using most of the memory and is therefore likely to be the killed process.
If the SQL Server process is killed and you know there is the possibility of a memory problem, start by looking at the Linux system logs at /var/log/messages. The OOM Killer algorithm, documented at https://linux-mm.org/OOM_Killer, is designed to consider several factors to estimate what is called an OOM score, which includes such things as trying to kill the minimum amount of processes (hopefully only one), recover a large amount of memory, and lose the minimum amount of work done.
NOTE
For an example of troubleshooting a case in which the SQL Server process was killed by the OOM Killer, see the article at https://blogs.msdn.microsoft.com/psssql/2017/10/17/how-to-safeguard-sql-server-on-linux-from-oom-killer/.
The best way to avoid the SQL Server process being killed by the Linux kernel is to configure the server memory properly, assigning the appropriate amount of memory for SQL Server, the operating system, and possibly any other process running on the same server. You can allocate the required amount of memory to SQL Server by using the mssql-conf tool, as explained earlier in this chapter. In addition, properly configuring a swap file, as indicated earlier, can help. You can view the available memory for SQL Server in the error log at instance startup, as shown next:
tempdb Configuration
Correctly configuring tempdb has been a performance consideration for all the versions of SQL Server for as long as I can remember. SQL Server 2016 brought some improvements such as the ability to create multiple data files automatically during the product setup based on the number of available processors on the system, or the new default tempdb configuration, which integrates the behavior of trace flags 1117 and 1118. Because SQL Server on Linux does not currently have the ability to create multiple tempdb data files during setup automatically, manually configuring this remains an important configuration requirement.
tempdb has been largely related to a classic performance problem: tempdb contention. The creation of a large number of user objects in a short period of time can contribute to latch contention of allocation pages. The main kind of tempdb contention is called DML (Data Modification Language) contention, as it relates to queries that modify data, mostly due to INSERT, UPDATE, and DELETE operations on temporary tables. A second type of contention, DDL (Data Definition Language) contention, although not common, is also possible in some heavy use scenarios. DDL contention is related to queries that create or alter objects that impact the system catalogs, as opposed to user data.
Every time a new object has to be created in tempdb, which is usually a temporary table with at least one row inserted, two new pages must be allocated from a mixed extent and assigned to the new object. One page is an Index Allocation Map (IAM) page and the second is a data page. During this process, SQL Server also has to access and update the very first Page Free Space (PFS) page and the very first Shared Global Allocation Map (SGAM) page in the data file. Only one thread can change an allocation page at a time, requesting a latch on it. When there is high activity and a large number of temporary tables are being created and dropped in tempdb, contention between the PFS and SGAM pages is possible. Remember that this is not an I/O problem, because allocation pages in this case are already in memory. Obviously, this contention impacts the performance of the processes creating those tables because they have to wait, and SQL Server may appear unresponsive for short periods of time. Keep in mind that although user databases have the same allocation pages, they are not likely to have a latch contention problem in allocation pages because not as many objects are created at the same time as they are created in tempdb.
The easiest way to check to determine whether you have a latch contention problem on tempdb allocation pages is to look for PAGELATCH_XX waits on the database activity. (Note that these are not the same as PAGEIOLATCH_XX waits.)
Although there is no perfect solution to the latch contention problem, because the database engine should be able to escalate and work fine as the number of operations increase, there are a few good recommendations to help you solve or minimize the problem. An obvious solution may be to minimize the number of temporary tables created in tempdb, but this may not be easy to implement because it would require code and application changes. Keep in mind that internal objects, such as the ones created by sort and hash operations, are not created explicitly by users and do not require the allocation methods discussed in this section. These internal objects can, however, create a different kind of performance problem.
The workaround to tempdb contention problems has historically been one or both of the following choices, especially prior to SQL Server 2016:
Using multiple data files.
Enable trace flags 1117 and 1118.
Multiple tempdb Data Files
With multiple files for tempdb, allocation bitmaps will be spread across the files, minimizing contention, because SQL Server will balance incoming requests across them. Using multiple files can help with both PFS and SGAM pages, and using trace flag 1118, as discussed in the next section, can greatly minimize the use of SGAM pages as well.
One question that has been debated for years about using multiple data files is what the optimal number of files for tempdb should be. Because applications can use tempdb in many different ways and can have different workloads, it’s difficult to recommend a specific number for every scenario. Fortunately, there is a good recommendation that can work in most use cases: create one data file per logical processor available to the SQL Server instance, up to a maximum of eight files. This recommendation is now a default configuration option for tempdb when you install SQL Server 2016 or later on Windows, but it has to be manually configured for SQL Server on Linux. SQL Server creates a scheduler per each logical processor available to SQL Server, so the number of logical processors is also the maximum number of concurrent threads. Having multiple files means multiple PFS and SGAM pages, which means more allocations can occur at a time, reducing contention as threads are not waiting for one set of allocation pages.
This is a general guideline, however, and there may be cases in which high tempdb usage may still encounter contention problems. In such cases, it may be wise to create additional data files in multiples of four, up to the number of logical processors available in the system. Data files on tempdb also should be created with the same size and have the same autogrowth setting.
You can easily add more files to tempdb using SQL Server Management Studio (SSMS) or Transact-SQL, as shown in Figure 4-2.
Figure 4-2 Configuring multiple data files on tempdb
The related Transact-SQL created by SSMS is shown here:
Trace Flags 1117 and 1118
Because SQL Server uses SGAM pages to look for mixed extents with at least one unused page, a possible workaround to SGAM contention could be to avoid mixed extents altogether and, instead, exclusively use uniform extents. Trace flag 1118 has been available since SQL Server 2000 to do just this.
By disabling most of the single page allocations and, instead, using dedicated extents, you reduce the contention on SGAM pages. A downside with using trace flag 1118, however, is that every object will use a dedicated extent, which requires eight pages. If the object is small enough to fit in only one page, this is effectively wasting space. For example, if the object only requires 8KB of space, it will have to use 64KB, thus wasting 56KB of storage. The other downside is that this configuration takes effect at the instance level, impacting user databases as well. SGAM pages will still be used, as IAM pages will still need to be single page allocations from mixed extents, but SGAM contention most likely can be eliminated or greatly minimized. Trace flag 1117 is related to how SQL Server increases the size of database files by enabling even growth of all files in a file group.
Two additional related recommendations are to create the tempdb data files of equal size and to not rely on the autogrowth database file settings. Let’s review those recommendations briefly. The recommendation to create the data files of equal size relies on the proportional fill algorithm used by SQL Server, which considers the current size of the database files, at least at the file group level. This algorithm determines in which order files are utilized and written to and spreads the allocations through the pages in a round-robin fashion. There is, however, an unfortunate behavior when files are not the same size. Instead of spreading Global Allocation Map (GAM) allocations between all the data files on a database, it would favor the largest file instead. Because of this, it is critical to configure all the data files the same size originally. Trace flag 1117 enables you to avoid this problem by growing all the tempdb files together at the same time by the configured increment. However, as in the case of trace flag 1118, trace flag 1117 applies to the entire SQL Server instance, working at the file group level on user databases. You may need to review whether it could be another issue for file groups on your user databases.
Finally, do not rely on the default autogrowth configuration, but leave it enabled with a proper file growth configuration, so it can be used as a last-resort choice. A production environment most likely will have a dedicated drive or file system for tempdb, if not a dedicated drive for data files and another for the transaction log file. If you are in this situation, simply allocate all the space possible to the tempdb files (maybe just limited by your space-available thresholds warnings so full disk notifications are not being sent). This will help avoid growing the file during production operations, causing unnecessary overhead. At the same time, it will minimize database fragmentation.
Process Affinity
Microsoft recommends that you set the process affinity for all the NUMA nodes and CPUs when SQL Server is running in a Linux operating system. You can accomplish this using the ALTER SERVER CONFIGURATION statement with the SET PROCESS AFFINITY option. You can either use CPU or NUMANODE choices, the latter being the easiest choice. Using the CPU option enables you to specify the CPU or range of CPUs to assign threads to. Using the NUMANODE option enables you to assign threads to all CPUs that belong to the specified NUMA node or range of nodes.
PROCESS AFFINITY enables hardware threads to be associated with CPUs and helps maintain an efficient Linux and SQL Server scheduling behavior. It is recommended that you set process affinity even if your system has a single NUMA node.
The following command, for example, sets the process affinity for all the NUMA nodes on a system with four nodes:
Max Degree of Parallelism
One of the most important settings to configure on a new SQL Server installation, the max degree of parallelism option, defines the number of logical processors employed to run a single statement for parallel execution plans. Although a perfect configuration may depend on the specific workload, Microsoft has, since long ago, published a best practice recommendation that can work on most of the workloads or that can be used as a starting point.
First of all, the hardware on which SQL Server is running must be capable of running parallel queries—which means at least two logical processors are required, and this basically includes almost every server available today, including most typical configurations in virtual machines for production instances. Second, the affinity mask configuration option, which is now deprecated, or the ALTER SERVER CONFIGURATION SET PROCESS AFFINITY statement, covered earlier, must allow the use of multiple processors, which both do by default. Finally, the query processor must decide whether using parallelism can in fact improve the performance of a query, based on its estimated cost.
When a SQL Server installation is using the default max degree of parallelism value, which is 0, the database engine can decide at runtime the number of logical processors in a plan, up to a maximum of 64. Obviously, this does not mean that every parallel query would use the maximum number of processors available all the time. For example, even if your system has 64 logical processors and you are using the default configuration, it is still very possible that a query can use only 8 processors and run with eight threads. This is a decision the query processor makes at execution time.
Microsoft published an article recommending a value for the max degree of parallelism option to be the same as the number of logical processors in the server, up to a maximum of eight. You can read the article at https://support.microsoft.com/en-us/kb/2806535. Because it is very common to have eight processors or more nowadays, a value of eight is a common configuration.
Keep in mind that the listed recommendation is just a guideline, and that the article specifies that the guideline is applicable for typical SQL Server activity. In addition, depending on your workload or application patterns, some other values for this setting may be considered and thoroughly tested as well. As an example, if your SQL Server installation has 16 logical processors and a workload with a small number of queries running at the same time, a max degree of parallelism value of 16 could be your best choice. On the other hand, for a workload with a large number of queries, a value of 4 could be considered as well.
Cost Threshold for Parallelism
As mentioned in the previous section, parallelism is considered only for expensive queries, which again are defined by another configuration option, the cost threshold for parallelism. The cost threshold for parallelism configuration option defines the threshold at which SQL Server considers parallel plans for queries during the query optimization process.
The cost threshold for parallelism, whose default value is 5, means that parallelism would be considered by the query optimizer only if an initially created serial plan has the cost of five or more cost units. Although it is very likely that a parallel plan will be selected, when it does, this does not mean it will always be the case. For example, it is totally possible that the query optimizer initially estimates a cost of 6 for a serial plan, decides to inspect a parallel choice, and after finding a parallel plan of 6.3, decides to stick with the serial choice as the lowest cost choice is selected. But for more expensive queries, most likely a parallel plan would be produced.
It is worth clarifying that when a parallel plan is selected for execution, the execution plan itself does not define the degree of parallelism. This will be defined at runtime by the query processor.
Although the default value of 5 could be a good choice for many workloads, you may also consider increasing this value for specific scenarios. The choice of which new value to use would depend on your workload and should be thoroughly tested. If you decide to change the configuration value from 5 to 25, for example, this roughly means that all your plans with an original serial cost estimated at between 5 and 25 will no longer be considered to be parallelized and will continue as serial plans. You can choose either to research such queries to determine whether the new serial choice does not impact the execution performance, especially if those are critical queries or executed frequently, or to test your workload in general.
Statistics
This section covers statistics, which is one of the most important things that can impact query performance. In versions prior to SQL Server 2016, a very common configuration was to enable trace flag 2371 to improve the threshold use by SQL Server to update optimizer statistics automatically. Starting with this SQL Server version, this configuration was enabled by default, so the trace flag was no longer needed.
By default, SQL Server automatically creates and updates query optimizer statistics. You can change this database-level default, but doing so is almost never recommended because it would require the developer or administrator to create all the required statistics manually. Although this is possible, it doesn’t make much sense, because the query optimizer can efficiently create the required statistics for you. Some other statistics will be created automatically when you create indexes for the columns involved in the index key. Manually creating statistics could be required only in a very limited number of cases, one of those being when multicolumn statistics are required. Multicolumn statistics are not created automatically by SQL Server.
Updating statistics is a little bit different. SQL Server can automatically update statistics when a specific threshold is reached. Although there are two thresholds or algorithms used to accomplish this, which I will cover in just a moment, a common problem is the size of the sample used to update the statistics object. High-performance databases may require a more proactive approach to updating statistics instead of letting SQL Server hit any of these two thresholds and using a very small sample.
The main limitation with automatic statistics update is the traditional 20 percent fixed threshold of changes required to trigger the update operation, which for large tables would require a very significant amount of changes. The second algorithm mentioned, usually enabled by using trace flag 2371, improves a bit on the threshold required to update statistics automatically, but the size of sample issue remains. The statistics update is triggered during the query optimization process when you run a query, but before it is executed. Because the update is technically part of the execution process, only a very small sample is used. Using a small sample makes sense, because you don’t want to use a large sample in the middle of the query execution since it will most likely impact the execution time.
The process can be efficient for many workloads, but for more performance-demanding applications, a more proactive approach is required. This proactive approach usually means performing a scheduled maintenance job to update statistics on a regular basis. This method fixes both problems—not waiting until you hit a specific large threshold and providing a better sample size, which may include using the entire table.
In my opinion, the new algorithm is still not enough, but it is certainly better than the default threshold. The benefit of the new algorithm is with large tables, but a small sample in large tables may still be inadequate. This is why I recommend that you proactively update statistics in the first place, but leave the automatic update enabled, just in case, as a second choice.
Although there are free tools and scripts available to update statistics, even within SQL Server, creating an efficient script to perform this update is not as easy as it sounds, especially for large databases. Your script will have to deal with some or all of the following questions: Which tables, indexes, or statistics should be updated? What percent of the table should be used as the sample size? Do I need to scan the entire table? How often do I need to update statistics? Does updating statistics impact my database performance activity? Do I need a maintenance window? The answer to most of these questions will depend on the particular implementation, because there are many varying factors.
First, you have to define a point at which you will update statistics. For example, a typical solution to rebuild indexes is using the fragmentation level, and such information is available through the sys.dm_db_index_physical_stats DMV, whose documentation can be found at https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql. It even provides a threshold and a script to get started. This process, however, is a little bit more complicated for statistics.
Traditionally, database administrators relied on updating statistics based on the last updated date for statistics (for example, using the DBCC SHOW_STATISTICS statement or the STATS_DATE function) or older columns such as rowmodctr, available on the sys.sysindexes compatibility view, both of which have some drawbacks. If a table has not changed much in a specific period of time, those statistics may still be useful. In addition, the rowmodctr column does not consider changes for the leading statistics column, as the following solution does. Introduced only relatively recently in SQL Server 2012 (and currently in SQL Server 2008 R2 Service Pack 2 and SQL Server 2012 Service Pack 1) and later versions, you can use a new DMF, sys.dm_db_stats_properties, to return information about a specific statistics object. One of the columns, modification_counter, returns the number of changes for the leading statistics column since the last time the statistics on the object were updated, so this value can be used to decide when to update them. The point at which to update will depend on your data and could be difficult to estimate, but at least you have better choices than before.
Along with jobs for statistics maintenance, usually there are also jobs to rebuild or reorganize indexes, which makes the choice to update statistics a bit more complicated. Rebuilding an index will update statistics with the equivalent of the full-scan option. Reorganizing an index does not touch or update statistics at all. We usually want to rebuild indexes depending on their fragmentation level, so statistics will be updated only for those indexes. We may not want the statistics job to update those statistics again. Traditionally, this has been left to your scripts, with the difficult decision about which statistics object to update and which sometimes end up updating the same object twice. As mentioned, currently this problem can be fixed or minimized by using the sys.dm_db_stats_properties DMF.
Finally, there is currently no documented method to determine whether statistics are being used by the query optimizer. Let’s suppose, for example, that an ad hoc query was executed only once, which created statistics on some columns. Assuming those statistics are not used again, maintenance jobs will continue to update those statistics objects potentially as long as the columns exist.
Finally, I recommend Ola Hallengren’s Maintenance Solution, available at https://ola.hallengren.com, to implement maintenance jobs for backups, index and statistics maintenance, and consistency checks.
Standard Automatic Statistics Update
Automatically updating statistics has been available since SQL Server 7, when the query processor was rearchitected. Since then, the algorithm to update statistics automatically has not changed much; for tables with more than 500 rows, the automatic update will occur when the column modification counters (colmodctrs) or changes on statistics leading column hits 20 percent of changes plus 500.
Keep in mind that SQL Server 7 and SQL Server 2000 relied instead on the rowmodctrs, or row-level modification counters, to get the same behavior. Obviously, using rowmodctrs was not optimal if, for example, there was a statistics object on column c1, 25 percent changes on column c2, but no changes on c1; it will still trigger a statistics update on column c1, which was not needed.
A few more details about the algorithm to update statistics automatically, especially as it relates to tables with 500 rows or smaller, temporary tables, and filtered statistics, can be found in the white paper at https://msdn.microsoft.com/en-us/library/dd535534(SQL.100).aspx.
Trace Flag 2371
Trace flag 2371 was introduced with SQL Server 2008 R2 as a way to change and lower the threshold in which statistics are automatically updated, and, as with any other trace flag, it has to be manually enabled. The new algorithm will use whatever number of changes is smaller between the new formula, defined as SQRT(1000 * number of rows), and the old one using 20 percent of the size of the table. If you do the math using both formulas, you can see that the threshold changes with tables of 25,000 rows, in which both cases return the same value: 5000 changes. For example, with the default algorithm requiring 20 percent of changes, if a large table has a billion rows, it would require 200 million rows to trigger the update. Trace flag 2371 would require a smaller threshold, in this case, SQRT(1000 * 1000000000), or 1 million. SQL Server 2016 and later has the behavior of trace flag 2371 enabled by default when you use the new database compatibility level 130 or later.
Finally, as covering statistics in full detail is outside the scope of this book, I would recommend the Microsoft white paper Statistics Used by the Query Optimizer in Microsoft SQL Server 2008, which is still valid for the latest versions. You can find it at https://msdn.microsoft.com/en-us/library/dd535534(SQL.100).aspx.
Summary
This chapter covered configuring SQL Server on Linux from three different areas. First, there is no Configuration Manager on Linux, so configuring the SQL Server port or a trace flag at the server level now has to be done with the mssql-conf utility. In addition, mssql-conf can be used to configure a large variety of other settings.
It also covered several CPU- and disk-related Linux kernel settings for a high-performance configuration, in addition to some operating system–level configurations such as transparent huge pages, which can improve the performance of Linux when managing large amounts of memory.
Finally, it covered some critical SQL Server configuration settings that apply either to Windows or Linux implementations. An interesting behavior used by Linux when a server is running low in memory, the Out-Of-Memory Killer, was introduced as well.