Some Tips About tempdb Database

August 3, 2000


Introduction
SQL Server 6.5
SQL Server 7.0
Literature

Introduction

When SQL Server is installed the setup program creates tempdb database. Tempdb is a system database used by SQL Server to store temporary tables and temporary stored procedures, for sorting, subqueries, and aggregates with GROUP BY, ORDER BY, for cursors and so on. Tempdb database contains only temporary objects, so if you want to create a permanent object, do not create it in the tempdb database.


SQL Server 6.5

The tempdb database is created on the master database device. The default size of tempdb is 2 MB. If you use large temporary tables, or run many queries with GROUP BY or ORDER BY, or frequently use large cursors, then the size of the tempdb database should be increased. To increase the size of the tempdb database, you can expand master device and then expand tempdb, or create new device and then expand tempdb database on this new device. You can also move tempdb database on its own database device to simplify administering and monitoring.

This is the description:

1. Create new device with appropriate size (tempdb_dev for example)
2. Uncheck "Default device" option for the master database device (this option is enable for the master database device by default).
3. Set "Default device" option for the tempdb_dev device.
4. From the Enterprise Manager (or sp_configure) set tempdb to be in RAM (set value to 1)
5. Stop and restart MSSQLServer service.
6. From the Enterprise Manager (or sp_configure) set tempdb to not be in RAM (set value to 0)
7. Stop and restart MSSQLServer service.
8. Set whichever device you want to be default.

If you want to increase a speed of your queries (if this queries contain subqueries, or GROUP BY, or ORDER BY clause), you can set tempdb database to be in RAM. However, in most cases, the available RAM is best used for a data cache, rather than for a location of tempdb, so support for tempdb in RAM has been removed in SQL Server 7.0.


SQL Server 7.0

There are no database devices and segments in SQL Server 7.0, now database consists of two or more Windows files. There are three types of SQL Server database files: primary data files (necessary for each database, extension .mdf), secondary data files (not necessary, extension .ndf) and log files (necessary for each database, extension .ldf). Now data and log information are never mixed on the same file, and one individual file is used only by one database.

After you have installed SQL Server 7.0, you can find tempdb.mdf file with the size of 8.0 Mb and templog.ldf file with the size of 0.5 Mb in the directory C:\MSSQL7\DATA (path by default). These files contain tempdb database.

The tempdb database is re-created every time SQL Server starts. This database is used more often in SQL Server 7.0 in comparison with SQL Server 6.5, so the size of this database can be increased on 25-50 percentage. There is no option 'tempdb in RAM' in SQL Server 7.0. Microsoft recommends to place tempdb on a fast I/O subsystem to get good performance. In this case, try to use RAID 0 disks for tempdb database.

Now tempdb database can automatically grow, so not necessary to make tempdb very large. Autogrow feature is set by default. Each time the SQL Server is started, tempdb database is reset to its default size. Automatically growing results in some performance degradation, therefore you should set a reasonable size for tempdb database and a reasonable autogrow increment to avoid tempdb automatically growing too often.


Literature

1. SQL Server Books Online.

2. "Microsoft SQL Server 7.0 Storage Engine Capacity Planning Tips"
http://msdn.microsoft.com/library/techart/storageeng.htm

3. Microsoft SQL Server 7.0 Performance Tuning Guide
http://msdn.microsoft.com/library/techart/msdn_sql7perftune.htm

4. SAP R/3 Performance Tuning Guide for Microsoft SQL Server 7.0
http://msdn.microsoft.com/library/techart/sql7sapr3.htm


» See All Articles by Columnist Alexander Chigrik









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers