Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 60 of 212

49 C H A P T E R 4 | Improved database engine server. These changes allow administrators to focus their efforts on more pressing performance and data issues in their environments. Configuring data files for TempDB In earlier versions of SQL Server, the default configuration uses one data file for TempDB. This limitation sometimes results in page-latch contention, which has frequently been misdiagnosed by administrators as a storage input/output (I/O) problem for SQL Server. However, the pages for TempDB are typically in memory and therefore not contributing to I/O contention issues. Instead, three special types of pages are the cause of the page-latch contention issue: Global Allocation Map (GAM), Shared Global Allocation Map (SGAM), and Page Free Space (PFS). Each database file can contain many of these page types, which are responsible for identifying where to write incoming data in a physical data file. Whenever a process in SQL Server needs to use any of these files, a latch is taken. A latch is similar to a lock but is more lightweight. Latches are designed to be quickly turned on and just as quickly turned off when not needed. The problem with TempDB is that each data file has only one GAM, SGAM, and PFS page per four gigabytes of space, and a lot of processes are trying to access those pages, as shown in Figure 4-1. Subsequent requests begin to queue, and wait times for processes at the end of the queue increase from milliseconds to seconds. Figure 4-1: Contention in TempDB. An easy way to remedy TempDB page-latch contention in SQL Server is to add more data files. In turn, SQL Server creates more of the three special types of pages and gives SQL Server more throughput to TempDB. Importantly, the files should all be the same size. SQL Server uses a proportional fill algorithm that tries to fill the largest files first, leading to hotspots and more latch contention. However, because the default setting creates only one file, many database administrators have not been aware of the solution. Even after learning about the need to create multiple files, there was often confusion about the correct number of files to configure, especially when factoring in virtual machines, hyperthreading, and cores versus CPU sockets. In 2011, Microsoft released the following guidance for TempDB configuration: As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of

Articles in this issue

Archives of this issue

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