Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 61 of 212

50 C H A P T E R 4 | Improved database engine logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code. Note For more detail, see "Recommendations to reduce allocation contention in SQL Server tempdb database," at Accordingly, in SQL Server 2016, this recommendation is built into the product setup. When you install SQL Server, the default configuration for TempDB now adapts to your environment, as shown in Figure 4-2. The setup wizard no longer creates a single file by default; instead, it assigns a default number of files based on the number of logical processors that it detects on the server, up to a maximum of 8. You can adjust the size of the files and the autogrowth rate if you like. Always monitor the growth of these files carefully, as performance is affected by file growth even when instant file initialization is enabled. Figure 4-2: Configuring TempDB in SQL Server 2016. Note SQL Server defaults to a conservative setting of 8 megabytes (MB) for Initial Size and 64 MB for Autogrowth. A best practice is to start with an initial file size of 4,092 MB, with an autogrowth setting of 512 MB, as the initial file size is still small by most standards. Many DBAs dedicate a standard-size file system (typically 100–200 GB) to TempDB and allocate 90 percent of it to the data files. This sizing can reduce contention and also prevents any uncontrolled TempDB growth from impacting user databases. Eliminating specific trace flags Trace flags are commonly used by administrators to perform diagnostics or to change the behavior of SQL Server. With TempDB in earlier releases of SQL Server, administrators use trace flags 1117 and 1118 to improve performance. In SQL Server 2016, the effect achieved by enabling these two trace flags has been built into the database engine, rendering them unnecessary. Trace flag 1117 Trace flag (TF) 1117 is related strictly to file groups and how data files grow within them. A file group is a logical container for one or more data files within a database. TF 1117 forces all data files in the same file group to grow at the same rate, which prevents one file from growing more than others,

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016