Configuring Files for TempDB database

TempDB database is a system database
used to hold temporary user objects and row versions etc. IN addition, TempDB holds internal
objects that are created by the SQL Server Database Engine.  TempDB plays an
important role with respect to database operations. Since TempDB is used
heavily, DBAs tend to allocate multiple data files to the tempdb to improve
performance.
Comprehensive article at SQLSkills (http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/)
explains the optimal number of files for tempdb database.

In the previous installations of
SQL Server, only one data file is added to the tempdb and after installing and you will
need to add the files to the TempDB. For the newly added files to effect, you
need to restart the SQL Server service.

In SQL Server 2016, there is an additional
feature of an option to specify
number of temp DB files as shown in the below screenshot.

The default value for the TempDB file is 8 or the
number of cores whichever is lower. You cannot have a value higher than the
number of cores for number of temdb files.

This option is available in the Database
Engine Configuration options.

You don’t have an option of
providing the different parameter values ( file size, growth properties) for
each file in TempDB where as you only have the option of providing the number
of files and each file size will be 8 MB with auto growth set to 10%. These are not
configurable during the SQL Server installation.




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |