DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Securing Your Software Supply Chain with JFrog and Azure
Register Today

Trending

  • 5 Key Concepts for MQTT Broker in Sparkplug Specification
  • Getting Started With the YugabyteDB Managed REST API
  • 10 Traits That Separate the Best Devs From the Crowd
  • A Complete Guide to AWS File Handling and How It Is Revolutionizing Cloud Storage

Trending

  • 5 Key Concepts for MQTT Broker in Sparkplug Specification
  • Getting Started With the YugabyteDB Managed REST API
  • 10 Traits That Separate the Best Devs From the Crowd
  • A Complete Guide to AWS File Handling and How It Is Revolutionizing Cloud Storage
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Optimize Tempdb in SQL Server

How to Optimize Tempdb in SQL Server

Optimizing the temp database for the server is one of the greatest ways to increase Server performance.

Abrienne Jonethan user avatar by
Abrienne Jonethan
·
Nov. 27, 18 · Tutorial
Like (4)
Save
Tweet
Share
20.26K Views

Join the DZone community and get the full member experience.

Join For Free

SQL Server has four databases system by default and one of them is called tempdb. The tempdb is used for many SQL operations, which include creating user-defined temporary objects, internal temporary objects and version stores, and other features like online re-indexing, multiple active record sets (MARS), and others. Optimizing the temp database for the server is one of the greatest ways to increase Server performance. Keep on reading to learn some points on how to optimize tempdb in SQL Server.

One fact to note is that everyone is using an instance shares of the same tempdb; you cannot have more than one within an instance of SQL Server but you can get detailed information about what is been done in tempdb using DMVs.

Features and Attributes of tempdb:

    • Tempdb is always set in a simple recovery mode, that means the transaction log records for committed transactions are marked for reuse after every checkpoint.
    • Tempdb can only have one filegroup, you can’t add more.
    • Tempdb can store three types of objects: user objects, internal objects, and the version store.

How to Optimize Tempdb in SQL Server

It seems obvious to pay attention to tempdb. But how? I have summarized a few rules for this:

1. The tempdb is rebuilt after each reboot which give the tempdb a sufficiently large initial size.

2. Since the tempdb is accessed very often and outsourcing the tempdb to another drive is a good measure for accelerated access.

Script to move the tempdb files.

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME =, E: DATAtempdb.mdf ');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME =, E: Datatemplog.ldf ');

GO

3. Always give the tempdb a sufficient capacity. Leave it to grow on automatic. 

4. Set the recovery mode to a Simple. Not everything is recorded in the transaction log, means when a transaction completed it is removed from the transaction log. Therefore the log file does not continue to grow unnecessarily.

5. The creation of additional data files can optimize to get access to the data carrier, as this storage conflicts can be avoided. There is a pi * thumb rule: number of CPUs equals the number of files. This improves the access to the data carrier.

6. If you are using several files which specify the same file sizes. This facilitates the proportional filling of the files.

7. Disable Auto-Update to increase the performance of temp data in the SQL database.

Conclusion

The tempdb must be paid much more attention, especially under SQL Server 2005. Whoever thinks of backing up tempdb, deleting tempdb, database mirroring, or optimizing by filesets doesn’t help you out in optimizing the SQL database.

I hope this provided some useful and quick points to optimize tempdb in SQL Server.

sql Database

Opinions expressed by DZone contributors are their own.

Trending

  • 5 Key Concepts for MQTT Broker in Sparkplug Specification
  • Getting Started With the YugabyteDB Managed REST API
  • 10 Traits That Separate the Best Devs From the Crowd
  • A Complete Guide to AWS File Handling and How It Is Revolutionizing Cloud Storage

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: