Database Reference
In-Depth Information
It is extremely unlikely you will ever reach the maximum storage capacity
limits of a SQL Server 2012 database system. We will not be covering the
maximums here. We recommend you refer to Microsoft
( http://technet.microsoft.com/en-us/library/ms143432.aspx ) .
Microsoft recommends as a best practice that you should configure one Temp DB data
file per CPU core and 0.25 to 1 data file (per file group) per CPU core. Based on our
experience, our recommendation is slightly different.
If your database is allocated eight or fewer vCPUs as a starting point, we recommend
you should configure at least one Temp DB file per vCPU. If your database is allocated
more than eight vCPUs, we recommend you start with eight Temp DB files and increase
by lots of four in the case of performance bottlenecks or capacity dictates.
Tip
Temp DB is very important because it's extensively utilized by OLTP databases
during index reorg operations, sorts, and joins, as well as for OLAP, DSS, and
batch operations, which often include large sorts and join activity.
We recommend in all cases you configure at least one data file (per file group) per
vCPU. We recommend a maximum of 32 files for Temp DB or per file group for
database files because you'll start to see diminishing performance returns with large
numbers of database files over and above 16 files. Insufficient number of data files can
lead to many writer processes queuing to update GAM pages. This is known as GAM
page contention. The Global Allocation Map (GAM) tracks which extents have been
allocated in each file. GAM contention would manifest in high PageLatch wait times.
For extremely large databases into the many tens of TB, 32 files of each type should be
sufficient.
Updates to GAM pages must be serialized to preserve consistency; therefore, the
optimal way to scale and avoid GAM page contention is to design sufficient data files
and ensure all data files are the same size and have the same amount of data. This
ensures that GAM page updates are equally balanced across data files. Generally, 16
data files for tempdb and user databases is sufficient. For Very Large Database (VLDB)
scenarios, up to 32 can be considered. See
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/04/what-is-
allocation-bottleneck.aspx .
If you expect your database to grow significantly long term, we would recommend that
you consider configuring more data files up front. The reason we specify at least one
file per CPU is to increase the parallelism of access from CPU to data files, which will
 
Search WWH ::




Custom Search