Post reply

TempDB configuration concern...

  • April 16, 2019 at 7:38 pm

    #3416167

    Hello,

    We have multiple production SQL Server VMs (created within the last 2 months, SQL 2012, 2014 + 2016). We have a standard configuration, an operating system drive and application drive (clearly a different setup from what we had before).

    Application drive is where we install SQL Server. My concern, tempdb is on the same drive as our application databases. My training suggested at least keeping tempdb separate (more likely separate for tempdb, data, and backups).

    The response I get has been it won't make a difference, they're all on the same storage. My contention, you want at least to keep tempdb separate. If you inadvertently run out of space (and tempdb is on the same drive), you have issues.

    One article I read suggested making a separate LUN for tempdb. Note I'm not focused on performance, more recovery in the event we run into a storage issue. Am I correct in my assumption? And what are my alternatives?

    Any URLs / suggestions are appreciated.  Thanks.

  • April 16, 2019 at 8:40 pm

    #3416664

    Hey,

    In our case the main reason we have TempDB in another drive is because:

    • If some big bad query starts running if it takes hours it would use the whole TempDB and could also make it expand and use the whole drive space leaving no space for the other system databases,logs, the instance would stop working.
    • Performance, most of our applications use tons of temporary objects, we keep them on SSD's in another LUN.
    • Multiple TempDB Drives with data files having the same size

    In our most busy instances we have the next structure

    C: - OS Layer

    D: - SQL Server (Master, model and MSDB)

    E: - LUN for Tempdb

    F: - LUN for DataSQL

    G:- LUN for LogSQL

    We even have 2 Luns for TempDB with 4 files in one and 4 in the another. TempDB is really important if your application make big use of it or if you have a report server that users heavily use doing sorts and group operations.

    Of course, sometimes its larger we have databases with more than 10 drives, but that's the idea.

     

    Some of Paul Randal's publication regarding tempdb

    Regards,

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply