Roman Rehak

SQL Server and things not related

<December 2008>
SuMoTuWeThFrSa
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910


Navigation

SQL Server Sites

Subscriptions

News

eXTReMe Tracker

Post Categories



Sunday, April 24, 2005 - Posts

Roman's Weekly SQL Server Tip - Increase the size of tempdb

SQL Server uses the tempdb system database to store both temporary tables and worktables holding intermediate results from sorting and many other complex data retrieval operations. In addition, it's also heavily used during certain DBCC operations. Depending on the server workload, the tempdb database can grow to a few gigabytes in size. Everytime SQL Server is restarted, the tempdb is recreated from scratch and it will start growing again whenever more space is needed. By default, the tempdb only starts at few MB in size and then it needs to autogrow anytime more space is needed. Since frequent autogrow can negatively affect performance and in some cases it results in errors if it cannot be done fast enough, it is considered a good practice to presize the tempdb database to the expected maximum size it will ever reach. Once you change the default size, SQL Server will use the new size settings when rebuilding tempdb during startup. One of the guidelines I recall reading is to set tempdb to 25% of the largest database.

You can get more details plus additional tempdb tips from the SQL-Server-Performance.com website.

posted Sunday, April 24, 2005 9:34 PM by Roman with 712 Comments




Powered by Dot Net Junkies, by Telligent Systems