MSSQLCity.Com - All about MS SQL
     
About Us  
SSWUG Articles  
Articles  
Administering  
Comparison  
General  
Know How  
Replication  
Tuning  
Undocumented  
UDF  
SQL 6.5  
FAQ  
Scripts  
Tips  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 

Some tips about tempdb database

Alexander Chigrik
chigrik@mssqlcity.com


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 of moving tempdb database:

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 set tempdb to be in RAM (set value to 1).
5. Stop and restart MSSQLServer service.
6. From the Enterprise Manager 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/2000.

SQL Server 7.0/2000

There are no database devices and segments in SQL Server 7.0/2000, 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, 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/2000 in comparison with SQL Server 6.5, so the size of this database can be increased on 25-50 percentages. There is no option 'tempdb in RAM' in SQL Server 7.0/2000. Microsoft recommends placing the 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.


 

 
Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2010 Bits on the Wire, Inc