The tempdb database is one of SQL Server's included system databases and is used as a shared temporary workspace for many different kinds of activities, such as the following:
Storing user objects, such as temporary local and global tables and indexes, temporary stored procedures, table variables, and the cursor.
Storing temporary work tables used for hash joins, aggregations, cursors, and spool operations, and temporarily storing large objects; storing intermediate sort results from many different internal operations, such as creating or rebuilding indexes, in addition to some GROUP BY, ORDER BY, and UNION queries.
Storing objects used when using AFTER triggers and INSTEAD OF triggers.
Storing large XML objects.
Storing the SQL Server version store (SQL Server 2005/2008), which includes the common version store and the online-index-build version store.
Storing intermediate results and sorting activity used during DBCC CHECKDB operations.
Storing temporary objects for Service Broker.
If your SQL Server instance is not busy, and it doesn't employ many of the activities described previously, then tempdb performance may not be a problem for you. On the other hand, if any of your SQL Server instances are busy, and are heavily involved in many, if not most, of the previously described activities, then you may find that tempdb can become a significant bottleneck for your entire SQL Server instance. Unlike other SQL Server databases, the tempdb database is dropped and re-created every time the SQL Server service is stopped and restarted. Here's what happens. When the SQL Server service is started, by default, SQL Server makes a copy of the model database to create a new 8 MB tempdb database, inheriting customizations made to the model database. In addition, a transaction log file of 1 MB is created. For both the MDF and the LDF files, autogrowth is set to grow by 10 percent with unrestricted growth. Each SQL Server instance may have only one tempdb database. In addition, tempdb exhibits many behaviors that don't occur with other SQL Server databases. For example, tempdb is configured to run using the simple recovery model, and this setting cannot be changed. In addition, many database options, such as Online, Read Write, Auto Close, Auto Shrink, and others are preset and cannot be modified. The tempdb database has many other restrictions including the following: it can't be dropped; it can't be captured in a database snapshot; it can't participate in mirroring; and it can't allow DBCC CHECKDB to be run on it. And as you would expect, neither the tempdb database nor its transaction log file can be backed up.
This makes sense, as tempdb is designed for temporary objects only, and is re-created each time SQL Server restarts. After tempdb has been created, DBAs can create objects in it just as in other databases. As user-created or internal objects are added to tempdb, it will automatically grow as necessary to whatever size is required to hold the objects. On servers with heavy tempdb activity, tempdb can grow considerably. As activity transpires in tempdb, transaction logging occurs, but somewhat differently than with other SQL Server databases. Operations performed within tempdb are minimally logged, which means that only enough information is logged so that temporary objects can be rolled back, if necessary. Minimal logging helps to reduce the overhead put on the SQL Server instance. Because the database is set to the simple recovery mode, the transaction log is truncated constantly. As I mentioned previously, if your SQL Server instance doesn't use tempdb much, then tempdb performance may not be an issue for you. On the other hand, you should keep one important thing in mind about tempdb: there is only one tempdb database per SQL Server instance, and it can become a major bottleneck that can affect the entire performance of your SQL Server.
Keep in mind that even if most of your applications and databases behave well, a single misbehaving application and database can affect the performance of all the other databases running on the same server. When building a new SQL Server instance, it is often difficult to determine how busy the tempdb database will be in production. Because of this, you may want to consider implementing many of the following suggestions when you build a new instance, as in many cases it is much easier to implement these recommendations when the server is first built, rather than trying to implement them after a problem has developed. Consider this as an ounce of prevention to avoid potential and unforeseen problems in the future.
Our website is not responsible for the information contained by this article. Articleinput.com is a free articles resource thus practically any visitor can submit an article. However if you notice any copyrighted material, please contact us and we will remove the article(s) in discussion right away.
Note: This article was sent to us by: Oliver F. Brock at 01092010
1. Lambda expressions and generator functions in Python
All articles are property of their respective authors. Please read our Privacy Policy!
© 2009 ArticleInput.com.