Skip to Content

We are currently running with the default size (2K) on “session tempdb user log cache”. We are planning to go to 128K. We know this is going to burn some cache but still worth it as it reduces persistent issues with tempdb log management.

Though we do not envision any issues just want to check with the ASE community on their experience with this…

Thanks,

Prasad

P.S: Thanks to Jeff T for nudging to take this route.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Simon Ogden

    We run with up to a 6MB session tempdb log cache on some systems. 128KB is not particularly large.

    This has been sized based on testing, relative benefit versus cost, memory etc. The primary driver for this value is this represents the maximum(ish) size (on these systems) of a minimally logged transaction for a long running complex select into statement.

    The requirement in our case was not a performance/contention based one, but to prevent the tempdb plc from flushing to the tempdb log and thus preventing truncation of the tempdb log.

    However it will not prevent all suitably sized tempdb transactions flushing to tempdb log. $sort_local transactions that spill into overflow pages from hashing operations immediately get flushed to tempdb log.

    (0) 
    1. Prasad Pandhigunta Post author

      Thanks! This is very helpful information. We have 15K users and it may be difficult to go beyond 1M without compromising on cache allocation for “data caches”.

      Will let you know how our testing goes.

      Thanks again,

      Prasad

      (0) 
      1. Simon Ogden

        I am not suggesting to just arbitrarily increase it really. Only do that if you have some evidence that it may help. The general metrics around user log cache flushes don’t separate out the tempdb plc versus the regular plcs (all under user log cache). However you should as Jeff has mentions increase it from the default. If you use tempdb heavily then the session tempdb log cache should at the very least be larger that the user log cache size. Keep an eye on the log semaphore and any context switching.

        I guess what I am saying though is I wouldn’t worry about any potential negative impact of setting a higher figure, the only real possible downside is potentially wasted memory.

        (0) 
        1. Prasad Pandhigunta Post author

          I understand. There is no doubt that it is going to be useful. We had tested with 64K and the Log Appends and Log Append Waits have gone down by at least 60%.
          Knowing well how painful it is to manage log fills we want to go to a high enough number as practically possible.

          Thanks!

          (0) 
          1. Jeff Tallman

            I think a lot depends on memory.   Remember, this adds to the user connection memory impact (or at least it used to).   So….if today a user connection takes 100K (just an example) and you add 128KB session tempdb ULC, you now have connections at 228K per connection – at 10000 connections, it adds up quickly.    As a result, I have found 64-128K to be a bit of a sweet spot at maximizing the impact while not requiring extremely large amounts of memory.   With Simon’s setting of 6MB….either something has changed or they found it worthwhile to expend that much memory per connection.

            Remember, though – this is for tempdb.   If you do a lot of select into #temp – you don’t need a lot of session tempdb ULC because the only log records are allocation pages, etc.   However, if you precreate temp tables (as many do), then you have logged IO’s when doing I/U/D operations….and a lot depends on the table width as well as how many records per transaction – e.g. a temp table that is only 1KB wide that you insert 1000 records into would likely take at least 1MB of log records (more if indexed, etc.).   If you can’t afford the full 1MB, then the question is if setting to 256KB and absorbing some hits on ULC flushing to the real log is a lot better than the default (answer is likely a resounding yes) even though you get logging (where at the full 1MB size, if we see the commit, we discard the ULC vs. flushing).

            (0) 
            1. Simon Ogden

              As I said the 6MB setting is for specific systems. These are more dss type with a fewer number of user connections. The 6MB size is the size of a *minimally logged* transaction (many millions of rows).

              I am not sure that the session tempdb log cache size needn’t be high because it uses select into, I dare say the opposite is true in many circumstances. The fact you have minimally logged transactions means you stand a chance of a single transaction fitting in the tempdb plc entirely which can be a massive help in terms of select intos that may run for many minutes (avoiding holding the tempdb log). You have zero chance of fitting larger regular transactions in the ulc, all you do by increasing the size of it is reduce the number of times it has to flush (thus helping reduce contention).

              Nothing changed in terms of the recommendations for OLTP systems, we run slightly higher between 256KB and 384KB primarily as don’t have as high as 15K user connections and these sort of figures are good for also capturing the smaller select into transactions which still take place on the oltp systems.

              (0) 

Leave a Reply