Shulou(Shulou.com)06/01 Report--

SQL Server has four database systems by default, one of which is called tempdb. tempdb is used for many SQL operations, including creating user-defined temporary objects, internal temporary objects and version stores, and other features such as online reindexing, multiple active record sets (MARS), and more. Optimizing your server's staging database is one of the best ways to improve server performance. Read on to learn some tips on how to optimize tempdb in SQL Server.

One fact to be aware of is that everyone is sharing instances of the same tempdb; you can't have more than one in a SQL Server instance, but you can get detailed information about what you do in tempdb using DMV.

Functions and properties of tempdb:

Tempdb is always set to simple recovery mode, which means that transaction log records for committed transactions are marked for reuse after each checkpoint.

Tempdb can have only one filegroup and cannot add more.

Tempdb can store three types of objects: user objects, internal objects, and version stores.

How to Optimize Tempdb in SQL Server

Note that tempdb seems obvious. But how? I summarized some rules:

1. Tempdb is rebuilt after each restart to provide a sufficiently large initial size for tempdb.

2. Since tempdb is accessed frequently and outsourced to another drive is a good measure to speed up access.

Script for moving tempdb files.

ALTER DATABASE tempdb

Modify file (NAME = tempdev, FILENAME =, E: DATAtempdb.mdf');

go

ALTER DATABASE tempdb

Modify files (NAME = templog, FILENAME =, E: Datatemplog.ldf');

go

3. Always provide sufficient capacity for tempdb. Let it grow automatically.

4. Set the recovery mode to Simple. Not everything is logged in the transaction log, which means it is deleted from the transaction log when the transaction completes. As a result, log files do not continue to grow unnecessarily.

5. the creation of additional data files for accessing the data carrier can be optimized because such storage conflicts can be avoided. There is a pi * thumb rule: the number of CPUs equals the number of files. this improves access to the data carrier.

6. If you use multiple files that specify the same file size. This facilitates proportional filling of files.

7. Disable automatic updates to improve performance of temporary data in SQL databases.

conclusion

More attention must be paid to tempdb, especially under SQL Server 2005. Whether you want to back up tempdb, delete tempdb, database mirroring, or optimize through filesets, nothing will help you optimize your SQL database.

I hope this provides some useful and quick points for tuning tempdb in SQL Server.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.