To improve the performance of your Teamcenter installation let’s move the tempdb database and resize it. The default Microsoft SQL Server installation does not have an optimally sized or placed tempdb database for Teamcenter, and this slows down some of your SQL Server queries. This post helps you choose optimal configurations for tempdb.
SQL Server uses the built-in tempdb database as a workspace for temporary objects. These objects can be temporary tables resulting from application code, internal sort tables used by SQL Server, or a variety of other things. Depending on the load and usage patterns in a given environment, the configuration of tempdb can become a factor in overall performance. For this reason, there are some important best practices for managing this database.
These tempdb best practices can be boiled down to a few key points:
By default, tempdb files are located in the same directory as the other SQL Server system databases. For busy servers, this is usually not the best configuration. You should move tempdb to a high-performance, fault-tolerant disk volume—a RAID 1 or RAID 10 volume on fast disks, for example. You should not place tempdb on a RAID 5 volume because of the inferior write performance of RAID 5. Use an ALTER DATABASE command or the Microsoft SQL Server Management Studio (SSMS) GUI to move the database location.
You must restart SQL Server for this change to take effect. Note that SQL Server creates fresh copies of tempdb data and log files every time the service starts, so there is no need to move any files yourself. Just restart the service, and you can see the files in the new location(s) you have specified.
If you have the resources to do so, place tempdb on one or more disks that are only used for the tempdb database. Since tempdb is frequently used during the middle of query processing, any slowdowns in I/O operations because of other operations that are using the same disk(s) affect your query performance. If you have the resources to do so, placing tempdb physically over more than one disk (or even over more than one disk controller) should improve performance, particularly on a larger Teamcenter installation.
When you move the tempdb database (described above), you should also size tempdb so that you’re not slowed down by automatic growth in the database files, as SQL Server needs more physical space in tempdb. This is something you need to monitor over time, but here is the recommended guidance for initial sizing from the Teamcenter team:
Environment Size
Description
Database Size (MB)
Log Size (MB)
Small
Teamcenter DB < 100 GB
1–100 Users
2,048
1,024
Medium
Teamcenter DB 100–500 GB
100–1,000 Users
10,240
5,120
Large
Teamcenter DB > 500 GB
1,000+ Users
20,480
By default, tempdb contains only a single database file and a single transaction log file. For better performance, create multiple database files (Siemens recommends one per logical CPU core), ensuring that the database files are equally sized. It is not necessary that the files all be on different disks.
As you can see, sticking with the default configuration of tempdb is not the best choice. With a few simple changes, you can significantly improve the performance of many queries in SQL Server and speed up your Teamcenter application. For more details on configuring tempdb, including sample code, see the Siemens Best Practices white paper.
Richard Waymire is a mentor with Solid Quality Mentors and a former member of the Microsoft SQL Server development team. He has been working on SQL Server for more than 15 years, is a contributing editor of SQL Server Magazine, and is the author of several books about SQL Server. http://www.richardwaymire.com/