How Do I Interpret the output of sqbmemory?
Wyatt70
Posts: 50
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:
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.
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 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
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
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