• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

TempDB best practices

November 30, 2011 by Andy Hayes 9 Comments

Continuing on from my post describing what SQL Server TempDB is used for, I wanted to write about tempdb best practices and how on a busy system, modifications to the default setup of tempdb and its placement can boost performance.

How to optimize tempdb

  • Place tempdb on a separate disk to user databases. See my post on how to move tempdb.
  • Place tempdb on the fastest IO subsystem possible.
  • Size tempdb accordingly and configure autogrow. This is especially important if your system cannot tolerate performance degradation. If you size the database too small with autogrow enabled, tempdb will automatically grow according to the size increments you have set up. Imagine you have set it to grow in increments of 10Mb and you have a busy workload requiring tempdb to be used. If tempdb needs to expand a number of times to accommodate load, these sizing operations can generate lots of IO. Better to try and set your tempdb to be based on a staging server workload where you have witnessed how large it can grow.
  • Create multiple data files for tempdb, but how many? Allocate 1 data file per physical or virtual CPU core.
  • Keep tempdb data files equally sized and have autogrow increments configured equally across data files.
  • If your SAN administrator can provide it, split the multiple data files over different LUNs as opposed to holding everything on one LUN.
  • Configure the data files to be of equal size.

Please note that there will be many systems out there which have had no modifications done at all to tempdb and they run fine.

Whether you choose to apply all or some of these methods will largely depend on your budget and your knowledge of the system based on projected or analysed workload. I would recommend that for any system, that you try and size tempdb accordingly however.

Related Posts:

  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…
  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…

Filed Under: All Articles, SQL Server Administration Tagged With: performance, tempdb

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Comments

  1. Sandeep says

    December 27, 2013 at 12:20 pm

    Great job Andy. I have been reading your articles from last week which I found very useful for the person from beginner to expert level in DBA field. I think you should publish a book of this articles and real life experiences faced by you as DBA. I will definitely buy it.
    Thank you so much.

    Sandeep.

    Reply
    • Andy Hayes says

      December 31, 2013 at 8:29 am

      Thank you Sandeep. It’s really good to hear that you are finding these articles useful.

      All the best, Andy

      Reply
  2. Yuvraj says

    January 31, 2014 at 2:39 pm

    Great work andy keep it up 🙂

    Reply
    • Andy Hayes says

      January 31, 2014 at 5:24 pm

      Thanks Yuvraj! 🙂

      Reply
  3. Jim Wolff says

    February 12, 2014 at 4:00 pm

    I wonder about the tempdb transaction log. Is it advisable for it to also be on its own LUN, or can it be on the same LUN that all regular database transaction logs are written.

    Reply
  4. krishna says

    September 3, 2014 at 8:59 am

    Hi Andy Hayes,
    First, i would like to thank you for the articles. those are very helpful.
    I need some information regarding
    1. multiple tempdb files. even I configured multiple, only one is taking most of the disk space. can I know why & how to solve it.
    2. when tempdb used space will be cleared. It didn’t clear leading to out of disk space. I don’t prefer the sqlserver restart, because most of my operations are on mass data. please suggest me a way to clear tempdb, if it not using.

    Reply
  5. Johnny Boy says

    May 11, 2015 at 6:39 am

    This article is nice – very simplistic, down to the basics, and not a lot of wording. I came here after visiting cc.davelozinski.com/sql/increase-sql-server-tempdb-performance and like how you keep your articles easy enough to read for us non DBA types. 🙂 My only suggestion for improvement is perhaps include some sample SQL scripts you use (if any). For example, when you created multiple data files for your tempdb.

    Reply
    • Andy Hayes says

      May 19, 2015 at 7:23 pm

      Thank you for your comment Johnny! Glad you like the article. 🙂

      Reply
  6. aswani vemuri says

    February 17, 2017 at 7:58 pm

    Can we place Temp Data files in separate drive and Temp log file in seperate file? any advantage?

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©