TempDB Database On a Local Disk for an FCI

  • Sadan Kumar

    SSC Enthusiast

    Points: 177

    Comments posted to this topic are about the item TempDB Database On a Local Disk for an FCI

    Sadan Kumar Budde

  • Thomas Franz

    Hall of Fame

    Points: 3527

    Nice to know, how to install / configure it, but I still do not know, if and how this will work, when a server crash and has to fail over from Node1 to Node2.

    Will all tempdb-writes replicated (synchron or asynchron?) or will all open transactions be rolled back? What, if I created a #temp-table or a global ##temptable e.g. in SSMS. Will they be available (and filled) after the fail over?

    The main reason for tempdb on local SSDs is performance, but any benefit would be lost, if there would be some sort of synchronisation between the nodes, on the other hand I would need a sync, when I depend on tempdb-content.

    God is real, unless declared integer.

  • DBA_Rob

    Default port

    Points: 1492

    The installation steps are clear, but the reasons for doing this type installation are not quantified.  You mentioned that the performance "may" be better.  I think some examples of what performance was like when the tempdb files are located on SSD SAN are compared to tempdb files locally would be helpful.  For example did you see a 50% improvement on certain queries that made high use of tempdb or is this more like a 1% improvement.  I expect this would related to DB sizes since it is clear to me that moving large amounts of data over the SAN would be slower than local disk - the question is how large does the data need to be before it is noticeable?

  • Thomas Franz

    Hall of Fame

    Points: 3527

    DBA_Rob - Wednesday, March 8, 2017 7:13 AM

    You mentioned that the performance "may" be better.  I think some examples of what performance was like when the tempdb files are located on SSD SAN are compared to tempdb files locally would be helpful. 

    Performance tests would not be helpfull / significant, since it depends very much on your database / application / environment). In an ideal world, you would do only simple selects which ALL uses perfect indexes, so that no lookups / sorts / spills whatever are needed and your TempDB would not be touched. In this case you would not find any benefit for placing the TempDB on a local SSD.

    For every other database on the world it should be clear, that reads and writes to a local RAID-10 with two or more SSDs will be MUCH faster than to SAN (a single SSD could provide 500 GB per second with no latency, while acces to a SAN (particularly when it is busy) would be much slower (depending on the LAN connection and disctance between the DB Server and SAN)), disk number and types (HDD / SSD), SAN-Cache, SAN-Usage, encryption settings and of the TempDB-Usage of your application)....

    God is real, unless declared integer.

  • Michael L John

    One Orange Chip

    Points: 25670

    Tempdb is recreated with each restart.

    There is no need to sync between nodes.

    I have been doing this same thing with tempdb on my clusters for years.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Jeff Moden

    SSC Guru

    Points: 993788

    I've found that putting TempDB on SSDs normally doesn't help much.  Good code doesn't need it and bad code might see a 2X-5X improvement, which is still a pitiful improvement compared to finding the performance challenged code that uses too much TempDB and fixing it.  Fixing such code can easily realized a 30-60X improvement with a 1000X improvement not being uncommon and it doesn't take much to accomplish such fixes.

    Even MPP systems (which usually require a tweek of the code to take advantage of it) only advertise a 33X improvement.

    If you want performance, performance is in the code.  Bite the bullet and fix it before it bites you.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • gana20m

    SSCrazy

    Points: 2407

    Good post Sadan Kumar. Keep going. 🙂

    Regards,
    Ganapathi varma, MCSA
    Email: gana20m@gmail.com

  • Thomas Franz

    Hall of Fame

    Points: 3527

    Michael L John - Wednesday, March 8, 2017 7:41 AM

    Tempdb is recreated with each restart. There is no need to sync between nodes.I have been doing this same thing with tempdb on my clusters for years.

    Yes, it is recreated which each server start. But I thought, that fail over servers allows me to fail over without interupting every connection. When I restart my database server, I have to stop and start the applications, that are using it, too, because the most applications needs some init stuff and does not really like it, when the server connection get lost. I may be too optimistic (we are not using clusters, so I have no experience), but a failover should be transparent to the apps (they should not even realize, that there happens something)

    If you want performance, performance is in the code. Bite the bullet and fix it before it bites you.


    @Jeff Modem: of course fixing the code would usually result in much more improvements. But going to Amazon, ordering 3 customer SSDs (hey, its only TempDB), putting it into the server and creating a RAID 10 would be maybe 1000 EUR / USD and increases the speed immediately. Fixing the code in a big application could cost several man-years of work - if you can change it (false for the most vendor apps and of course you need somebody who is able to do it)

    God is real, unless declared integer.

  • Jeff Moden

    SSC Guru

    Points: 993788

    t.franz - Wednesday, March 8, 2017 8:07 AM

    Michael L John - Wednesday, March 8, 2017 7:41 AM

    Tempdb is recreated with each restart. There is no need to sync between nodes.I have been doing this same thing with tempdb on my clusters for years.

    Yes, it is recreated which each server start. But I thought, that fail over servers allows me to fail over without interupting every connection. When I restart my database server, I have to stop and start the applications, that are using it, too, because the most applications needs some init stuff and does not really like it, when the server connection get lost. I may be too optimistic (we are not using clusters, so I have no experience), but a failover should be transparent to the apps (they should not even realize, that there happens something)

    If you want performance, performance is in the code. Bite the bullet and fix it before it bites you.


    @Jeff Modem: of course fixing the code would usually result in much more improvements. But going to Amazon, ordering 3 customer SSDs (hey, its only TempDB), putting it into the server and creating a RAID 10 would be maybe 1000 EUR / USD and increases the speed immediately. Fixing the code in a big application could cost several man-years of work - if you can change it (false for the most vendor apps and of course you need somebody who is able to do it)

    Typically, it's only the top 10 things you need to fix and, typically, they're small things that certainly won't take "several man-years of work" yet can eliminate (personal experience) TRILLIONs of bytes of memory IO every 8 hours and have a significant (easily 30-60X improvement) reduction in CPU and Duration.  Yep... they need to be retested after bing rewritten but it's worth it.  And, yes, putting TempDB on SSDs will help, but don't expect it to work a miracle for you.  Further, if you don't actually fix the root cause and no one changes the way they write code, you'll need to buy more SSDs because the abuse of TempDB will continue to scale with the data.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • This was removed by the editor as SPAM

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

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