Best Practices on TEMPDB

Posted on 2011-05-09
Last Modified: 2012-05-11
Hello Experts!
    users in my organisation want to create user-tables or temp-tables on tempdb (SYSTEM DATABASES).
These tmp-tables will be used across servers, is it a good practice..?
Question by:shanj
    LVL 9

    Accepted Solution

    instead of temp tables you can always create table variables to store tempory data. But tempory tables are good in performance perspective. For tempory tables SQL Server maintains the statistics so the query optimizer can select an optimized plan. But table variables are always treated as single row tables. I am recommending table variables if they hold huge number of rows otherwise table variables are better.
    LVL 21

    Expert Comment

    by:Alpesh Patel
    You can not use temp table outside the SEssion or Server also.
    LVL 35

    Assisted Solution

    by:David Todd

    >>I am recommending table variables if they hold huge number of rows otherwise table variables are better.

    I think that the intent is that Temp Tables should be used for large numbers of rows, otherwise table variables for small numbers of rows.

    It is easier to add indexes to temp tables.


    Author Closing Comment

    LVL 35

    Expert Comment

    by:David Todd

    Sorry that we didn't solve your specific problem.

    tempdb is a system database, you are correct, but it is precisely for users to create temp tables, there is some possible use by very large table variables, and its used behind the scenes for row versioning.

    Best practice is to have tempdb on its own disks, so that it does cause disk contention. so best practice would have a number of disks OS, system DBs (apart from tempdb), tempdb, data, transaction logs, backups.



    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now