• 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.

    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)