Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Tempdb Configuration Best Practices in SQL Server

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (8)   |   Related Tips: More > DBA Best Practices

Problem
In SQL Server 2005, TempDB has taken on some additional responsibilities.  As such, some of the best practice have changed and so has the necessity to follow these best practices on a more wide scale basis.  In many cases TempDB has been left to default configurations in many of our SQL Server 2000 installations.  Unfortunately, these configurations are not necessarily ideal in many environments.  With some of the shifts in responsibilities in SQL Server 2005 from the user defined databases to TempDB, what steps should be taken to ensure the SQL Server TempDB database is properly configured?

Solution
In an earlier tip, we discussed sizing (Properly Sizing the TempDB Database) the TempDB database properly.  The intention of that tip was to determine the general growth and usage of the database in order to determine the overall storage needs.  In this tip we want to take a broader look at how TempDB can be optimized to improve the overall SQL Server performance.

What is TempDB responsible for in SQL Server 2005?

  • Global (##temp) or local (#temp) temporary tables, temporary table indexes, temporary stored procedures, table variables, tables returned in table-valued functions or cursors.
  • Database Engine objects to complete a query such as work tables to store intermediate results for spools or sorting from particular GROUP BY, ORDER BY, or UNION queries.
  • Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
  • DBCC CHECKDB work tables.
  • Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

What are some of the best practices for TempDB?

  • Do not change collation from the SQL Server instance collation.
  • Do not change the database owner from sa.
  • Do not drop the TempDB database.
  • Do not drop the guest user from the database.
  • Do not change the recovery model from SIMPLE.
  • Ensure the disk drives TempDB resides on have RAID protection i.e. 1, 1 + 0 or 5 in order to prevent a single disk failure from shutting down SQL Server.  Keep in mind that if TempDB is not available then SQL Server cannot operate.
  • If SQL Server system databases are installed on the system partition, at a minimum move the TempDB database from the system partition to another set of disks.
  • Size the TempDB database appropriately.  For example, if you use the SORT_IN_TEMPDB option when you rebuild indexes, be sure to have sufficient free space in TempDB to store sorting operations. In addition, if you are running into insufficient space errors in TempDB, be sure to determine the culprit and either expand TempDB or re-code the offending process.

Where can I find additional information related to TempDB best practices?

Next Steps

  • Based on this information, take a look at your TempDB configurations across your SQL Server environment and determine what changes are needed.
  • As you spec out new machines, be sure to keep TempDB in mind.  If you suspect TempDB should have its own disks, be sure to account for those as you purchase and/or configure your disk drives.
  • If you have a standard SQL Server deployment checklist, be sure that it includes the items from this tip that make sense in your environment.
  • Check out these related tips:
  • If you have some additional tips and tricks related to TempDB, please share your knowledge with the community in the forum below.


Last Update: 2/12/2008


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, April 16, 2013 - 11:47:41 AM - Jeremy Kadlec Read The Tip

viv,

Thanks so much.  That makes my day.

Thank you,
Jeremy Kadlec
Community Co-Leader


Tuesday, April 16, 2013 - 10:05:42 AM - viv Read The Tip

 

Thanks for this. Hope you know how much your blog helps others! :)


Friday, March 15, 2013 - 7:34:39 AM - Jeremy Kadlec Read The Tip

Pinky,

Can you post the code that creates the temp table?  This will help figure out the correct code for your select statement.

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, March 15, 2013 - 2:34:11 AM - pinky Read The Tip

Hi,

I already tried select * from tempdb..#temp this statement,

but still i get error Invalid table name

 

Thanks

 

 


Thursday, March 14, 2013 - 4:08:41 PM - Jeremy Kadlec Read The Tip

Pinky,

I assume this is what you are looking for:

select * from tempdb..#temp

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader


Thursday, March 14, 2013 - 7:00:28 AM - pinky Read The Tip

How i read temp table in vb application, i try

Select * from #temp table name

but it give error Invalid table name, actually it is in tempdb database.

plz give me solution.

thanks

 

 


Tuesday, February 12, 2013 - 4:05:22 PM - Jeremy Kadlec Read The Tip

SivaS,

I assume you are asking for this code:

DROP TABLE #YourTableName

or

DROP TABLE ##YourTableName

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader


Tuesday, February 12, 2013 - 3:51:37 AM - SivaS Read The Tip

 

How to delete the temporary table in tempdb

I h've executed a qry which cause getting no.of records,,& increased the size upto 70 gb in tempdb.mdf

I know the table name (#Loan) ,,so how to drop this table with out restarting the Sqlserver....

 




 
Sponsor Information