Let's talk about Tempdb


tempdb is a databases system that collects all temporary objects created in SQL Server. It is destroyed and rebuilt each time the SQL instance start from the model database, as well as user bases. If you want to change an option for tempdb, or its original size, you can change these options in model.

tempdb is a databases system that collects all temporary objects created in SQL Server. It is destroyed and rebuilt each time the SQL instance start from the model database, as well as user bases. If you want to change an option for tempdb, or its original size, you can change these options in model. Tempdb is in simple recovery mode. This can not be changed, and there is no reason to put tempdb in full mode.

tempdb contains not only temporary objects (tables, procedures, keys encryption, etc.). created with prefixes or # # #, and variables of type table, but you can also create "persistent" objects, as in any database. These objects will be maintained until manually deleted by a DROP or until you restart the instance.

In addition to the temporary objects, tempdb also collects two types of structures deposits version (Version blinds) for lines involved in the version tracking (row versioning, see section 4.2.1), and internal objects. The row versioning is used for some features such as the SNAPSHOT isolation level, pseudo-tables triggers, MARS (Multiple Active Result Sets), the index generation ONLINE and some DBCC commands as CHECKDB.

Internal objects are used to store intermediate data for all types of SQL Server operations (query plans, LOB or XML variables, results cursors, Service Broker messages in transit, etc.).. internal objects and deposits versions are not included in the transaction log of tempdb. They are optimized as much as possible. User objects (temporary tables, table functions and variables of type table)generate entries in the transaction log.

Over the versions of SQL Server, tempdb brings more optimizations in how to manage transaction logging, allocation and release of objects. This is a critical problem because tempdb undergoes a large amount of creation and destruction of objects, unlike a user database, which undergoes in normal use very little modification of objects. Thus, when tempdb is very busy, because of such a high use of temporary tables in SQL, it can produce a restraint on the structure of the data file for tempdb (due to allocation and freeing pages and extensions to manage content), or metadata tables (due to a large number of creations or deletions of objects).

This risk of contention is reduced by different methods (decrease of the lock data structures, cache management table). From SQL Server 2005 variables of type table and local temporary tables of are kept in cache, and for the tables of less than 8 MB, the page allocation (IAM) and a data page are stored in memory in case the table will be recreated.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: