- 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:MSSQL7DATA (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
