How Do I Interpret the output of sqbmemory?

Wyatt70Wyatt70 Posts: 50
edited October 26, 2007 6:27AM in SQL Backup Previous Versions
I'm troubleshooting some failed backups that write to a network share. I've read some posts on this forum about setting the MAXDATABLOCK and MAXTRANSFERSIZE parameters for the sqlbackup procedure. I want to choose a value that will give me the best performance while ensuring a successful backup most of the time. As part of this process, I ran the sqbmemory procedure. Here is the output:
TYPE     MINIMUM   MAXIMUM   AVERAGE  BLK COUNT  TOTAL
Commit   4096      63033344  423809   6863       2908602368
Reserve  4096      8384512   45086    4742       213798912
Free     4096      45457408  302940   326        98758656
Private  4096      63033344  274845   10895	   2994438144
Mapped   4096      1060864   99023    74         7327744
Image    4096      24862720  189678   636        120635392

I wanted to check the documentation for this procedure, but to my dismay, there is no entry for it in the SQL Backup help file! Therefore, I am asking someone on this forum to review the output and help me interpret it. Perhaps someone from Red-Gate can also explain why they don't feel it's necessary to document this procedure or tell me where the documentation resides.

Comments

  • The grid provides information about the amount of memory available in the SQL Server memory space.

    The key row in relation to SQL Backup is the "Free" row, and in particular the "maximum" value of this row.

    The amount of memory used by a SQL Backup request will be based on three factors:
    - The number of threads used (or the filecount value)
    - The MAXTRANSFERSIZE value (defaults to 1MiB)
    - An internal value, which is related inversely to the number of threads (more threads means this value decreases).

    These three values multiply together to give the required amount of memory.

    For a MAXTRANSFERSIZE of 1 MiB:
    - For 1 thread, the required amount of memory is 6MiB;
    - For 2 threads, this value is 12MiB;
    - For 32 threads, this value is 64MiB.

    In the event this amount of memory is not available, SQL Backup will automatically retry with a MAXTRANSFERSIZE of half the previous value, down to a minimum of 65536 (64kiB).

    If SQL Backup cannot execute on the smallest value (requires a minimum of 384kiB), it will exit out and be unable to run. By this stage the memory space is so fragmented that the simplest solution is to restart the SQL Server instance.

    Hope that helps,
    Jason
  • Okay, that's a start.

    I have been trying the backups using multiple threads, but now I see this would be a bad idea if memory is the issue.

    Just to clarify, will SQL Backup retry multiple times until it gets down to 65,536, as shown here ...

    1st Try 1048576
    2nd Try 524288
    3rd Try 262144
    4th Try 131072
    5th Try 65536

    ... or does it only retry once at half the value of MAXTRANSFERSIZE?

    1st Try 1048576
    2nd Try 524288
  • It will retry up to 4 times, "halving" in value until it hits 65536. So your first example would be correct.

    As for thread counts, once you get into the range of 4-16 threads, the memory usage will hover around 25-35MiB.

    If it helps, the best "first approximation" value for threadcount is twice the number of physical processors (and the SQL Backup GUI uses this value minus one for the pre-populated threadcount value).

    Jumping up to 32 threads (which will require the full 64MiB) will show no real improvements in performance (and may even slightly decrease in performance) unless you have 16+ physical processors.

    Hope that helps,
    Jason
Sign In or Register to comment.