Post reply

TempDB in RAM

  • March 12, 2012 at 10:05 am

    #258536

    Hi -

    SQL 2008 R2 Enterprise on win 2008 (stand alone server).

    I have a pretty large production sql server - 8 sockets - 64 cores, 1 TB memory, data drive residing on Fusion IO cards for all databases except TempDB and then another drive (again fusionIO cards) for TempDB.

    My question - has anyone tried to move tempdb to RAM and is it even supported?

    Any information you can provide is appreciated.

  • March 12, 2012 at 10:18 am

    #1457986

    Weird. Tempd would have been my first candidate for moving to FusionIO.

    Is there any particular reason why you didn't put tempdb on FusionIO?

    As per your question, theoretically you could create a ramdisk and use it for Tempdb, but I have no direct experience of this.

    -- Gianluca Sartori

  • March 12, 2012 at 10:22 am

    #1457990

    My apology for being unclear.

    Technically - we have two data drives.

    X: all database files (except TempDB) on a large drive residing on 4 FusionIO cards.

    Y: TempDB residing on 2 FusionIO cards.

  • March 12, 2012 at 10:22 am

    #1457991

    BTW, I found this interesting blog that describes how to move tempdb to a ram disk: http://sqlblog.com/blogs/paul_nielsen/archive/2010/02/15/tempdb-in-ram.aspx

    -- Gianluca Sartori

  • March 12, 2012 at 10:23 am

    #1457992

    cheshirefox (3/12/2012)

    My apology for being unclear.

    Technically - we have two data drives.

    X: all database files (except TempDB) on a large drive residing on 4 FusionIO cards.

    Y: TempDB residing on 2 FusionIO cards.

    My bad. Now I re-read your post and you state very clearly you are using FusionIO for tempdb as well.

    -- Gianluca Sartori

  • March 12, 2012 at 1:11 pm

    #1458119

    If tempdb is on an SSD drive, why would you want to move it to ram?

  • March 12, 2012 at 1:27 pm

    #1458137

    Lynn Pettis (3/12/2012)

    If tempdb is on an SSD drive, why would you want to move it to ram?

    I would think RAM would be a little faster..

    In SQL 6.5 and below this was supported directly.. However The only way now that I know of involved a RAM disk as I saw mentioned earleir in the thread. I did this before with a SYBASE install on Sun Solaris where 500MB was on a RAMdisk and the other 2GB were on disk. This worked pretty good, was tricky to setup. But for the processes that ran fully within that 500MB they screamed, but if they went over, you could tell..

    That is an obscene amount of RAM, how big are the databases?

    CEWII

  • March 12, 2012 at 2:51 pm

    #1458194

    Elliott Whitlow (3/12/2012)

    Lynn Pettis (3/12/2012)

    If tempdb is on an SSD drive, why would you want to move it to ram?

    I would think RAM would be a little faster..

    Not sure. With all that RAM, everything would primarily live in the buffer cache, both internal objects and user temporary tables.

    Sorts and hash tables would spill to tempdb when the memory grant is insufficient, but that can be tuned.

    User temporary tables would still be in the buffer cache until lazy writer or checkpoint force a disk write.

    I suspect that having tempdb on a ram disk would only double the size of tempdb in RAM (buffer cache + data files).

    Did you check how much IO activity comes from tempdb?

    -- Gianluca Sartori

  • March 12, 2012 at 5:12 pm

    #1458253

    Considering that SQL Server will use RAM as part of TempDB until it no longer fits, is this not a moot point on a system with a TB of ram?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 13, 2012 at 7:15 am

    #1458439

    Response to questions.

    Lynn - I asked the same question to many individuals w/in my organization.

    Short answer - 'Because RAM would be faster. See if it is possible.'

    Elliott - Agree, 1 TB is a substantial amount of RAM. The databases on the server are pretty large. We have a few production databases 1+ TB's and an additional dozen ranging from 20 GB to 400 GB.

    Gianluca - the environment is a very volatile OLTP system with high activity on tempDB. We were suffering from a high amount of write stalls on the tempdb files. The IO benchmarking from perfmon is acceptable but we did experience performance issues.

    In the SQL error log - we did experience 'I/O requests taking longer than XX seconds' on the TempDB database files.

    Jeff - Truthfully, was hopeful that would be the case. But, there is tremendous amounts of activity.

  • March 13, 2012 at 7:45 am

    #1458473

    I suppose that you already have gone through design tuning, SQL tuning and index tuning before deciding to investigate hardware upgrades.

    Are you sure there is no place for finding the tempdb intensive queries and tune them further?

    -- Gianluca Sartori

  • March 13, 2012 at 2:27 pm

    #1458845

    So depending on your query mix it is fully possible to have an entire database cached in RAM..

    CEWII

  • March 14, 2012 at 8:18 am

    #1459218

    I would really question putting TEMPDB on a RAM disk in this scenario. FusionIO cards take up a LOT of system memory - which is obviously then not available for the SQL Server buffer pool (or any other system executable). Taking even MORE RAM away for a tempdb RAM disk would likely be counter productive.

    With a system like that, if you are not getting the performance you expect I would LOVE to come and do some consulting for you to find out what is going on!! I will even give you a discounted rate just so I can play on such awesome hardware! 😎

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • March 14, 2012 at 8:39 am

    #1459237

    TheSQLGuru (3/14/2012)

    I would really question putting TEMPDB on a RAM disk in this scenario. FusionIO cards take up a LOT of system memory - which is obviously then not available for the SQL Server buffer pool (or any other system executable). Taking even MORE RAM away for a tempdb RAM disk would likely be counter productive.

    With a system like that, if you are not getting the performance you expect I would LOVE to come and do some consulting for you to find out what is going on!! I will even give you a discounted rate just so I can play on such awesome hardware! 😎

    I would even give it for free, if you were in the same continent as me. 🙂

    -- Gianluca Sartori

  • March 14, 2012 at 12:19 pm

    #1459433

    Gianluca Sartori (3/14/2012)

    TheSQLGuru (3/14/2012)

    I would really question putting TEMPDB on a RAM disk in this scenario. FusionIO cards take up a LOT of system memory - which is obviously then not available for the SQL Server buffer pool (or any other system executable). Taking even MORE RAM away for a tempdb RAM disk would likely be counter productive.

    With a system like that, if you are not getting the performance you expect I would LOVE to come and do some consulting for you to find out what is going on!! I will even give you a discounted rate just so I can play on such awesome hardware! 😎

    I would even give it for free, if you were in the same continent as me. 🙂

    Well, I am pretty darn happy you live across the pond then!! 😛

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 15 posts - 1 through 15 (of 22 total)

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