THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

MemToLeave and 32-bit SQL Backup and Restore failures

Has any one encountered SQL backup or restore failures that were ultimately diagnosed to a MemToLeave issue? This should only occur on 32-bit SQL Server versions, including 2000 and 2005. A number of other operations could generate a MemToLeave issue, but I am only addressing backup failures here. (the diagnosis should have led to messages in the SQL Logs like: reserve contiguous memory of Size=65536, 131072, or some power of 2 bytes failed).

 

A search on the MemToLeave topic might point to using the –g switch on sqlservr startup, possibly setting this to 384 or 512, but hopefully, not a higher value. If the only MemToLeave problem is with backups, then there is an alternative to jacking up the –g MemToLeave setting.

 

The default SQL Server backup allocates (or tries to) a certain number of buffers of a certain size (the BUFFERCOUNT and MAXTRANSFERSIZE settings). The default MAXTRANSFERSIZE is 1M (1,048,576 bytes for the digital deficient). I am not sure what the default BUFFERCOUNT is; it might be 10 or 20. So in a 32-bit SQL Server instance doing various funky things, there very well might not be 10 or 20 contiguous 1M chunks of virtual address space (VAS) in the MemToLeave area (which completely not related to how much physical memory your system has or how much physical memory is available, so do not even mention this, and just shoot anyone who does). When the allocation fails, the backup command will progressively try smaller MaxTransferSize settings, which can take awhile and maybe ultimately failing.

 

Of course jacking up the MemToLeave addresses this issue, but alternatively one could just decrease the BufferCount or MaxTransferSize. The default settings usually yields decent backup performance on ordinary storage systems (i.e., not the brute force configurations I discussed in an earlier post). It is quite possible that decreasing the BufferCount to 4 and MaxTransferSize to 262144 will not cause much of drop in backup performance, perhaps, 10-20%. If this seems severe, consider the alternative of failed backups with no action or daytime impact with the –g512 setting. Which is more acceptable?

 

If you use a third party database backup compression software that is multi-threaded, the default maybe X buffers per thread. If your system has 16 cores, and the default threads is one-half of the number of cores, then that might be 80 x 1MB buffers by default.

 

In case you are the curious type, the BufferCount and MaxTransferSize setting are described in SQL Server 2005 BOL, not in the SQL 2000 BOL, but are mentioned in the SDK optional part of the SQL Server 2000 installation. Everybody installs the SDK part on their personal systems right? It has lots of code samples.

From comments below: At one time LiteSpeed defaulted to n-1 threads, if you have 4 cores, 3 threads was default. The Core2 2.6GHz can compress 150-200MB/sec depending on the data, so if you have 16 cores, 15 threads can do 2.2GB/sec if you can feed it. If you cannot feed the beast, then what is the point of all the threads? If your disks can read just 600MB/s, 4 threads will work fine.

Last I saw, 4.6 was supposed to be 4 buffers per thread on 32-bit SQL. For LiteSpeed, I would recommend just dialing the right thread and buffercount, set threads no more than what your disks can supply, set buffercount to 4 if its not default. Only dial down the maxtransfersize if you still have failures or see the error message in the log.

I do not expect VAS problems on restore because the restore target is often not busy and should not have a fragmented VAS. However, if there VAS related restore failures, then noted that of the 3 backup tuning parameters: threads, buffer count, and maxtransfersize, two are automatically carry over to the restore: threads and maxtransfersize. The tuning that is independent of the backup is buffercount. This can be tuned on the restore independent of the backup setting. If the restore has problems, it may be necessary to tune threads and maxtransfersize on the backup side to lower than what is required from a successful backup.

Published Tuesday, August 12, 2008 6:35 PM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

grumpyolddba said:

yes encounter this from time to time - adjusting the buffers and threads in litespeed will usually make the problem go away.

August 12, 2008 7:06 PM
 

Linchi Shea said:

Had same experience as grumpyolddba.

August 12, 2008 10:13 PM
 

jchang said:

At one time LiteSpeed defaulted to n-1 threads, if you have 4 cores, 3 threads was default. The Core2 2.6GHz can compress 150-200MB/sec depending on the data, so if you have 16 cores, 15 threads can do 2.2GB/sec if you can feed it. If you cannot feed the beast, whats the point of all the threads? If your disks can read just 600MB/s, 4 threads will work fine.

Last I saw, 4.6 was supposed to be 4 buffers per thread on 32-bit SQL. For LiteSpeed, I would recommend just dialing the right thread and buffercount, set threads no more than what your disks can supply, set buffercount to 4 if its not default,

only dial down the maxtransfersize if you still have failures or see the error message in the log.

August 12, 2008 10:39 PM
 

Chris Wood said:

I too had this problem with Red Gate SQL Backup 5 but this was also impacted by applications using the CLR in SQL2005. Both SQL backup and CLR routines use the MemToLeave memory space so a backup would push the CLR appdomain out. Took me a few attempts to increase the -g parameter to run smoothly every night when the full backups run.

Chris

August 13, 2008 12:07 PM
 

xyvyx said:

Old topic, but...

When I ran into this same problem (also using 4.6, but on the x64 flavor of SQL & LS), the Quest support folks told me to drop the MaxTransferSize  paramter down to 524288.  Worked... but slowed it down a bit. Don't know if it makes a difference, but here are my other xp_slssqlmaint parameters:

-WriteHistory  -VrfyBackup -BkUpOnlyIfClean -BkUpMedia DISK -BkUpDB  "Z:\Backup" -DelBkUps 12HOURS -CrBkSubDir -BkExt "SLS" -NOOPTOLR -Threads 4  -Throttle 100  -BufferCount 20  -MaxTransferSize 524288  -CompressionLevel 1  -Priority 0  -Logging 2  -Affinity 0  -CryptLevel 0

October 5, 2009 10:47 AM
 

jchang said:

I cannot believe that you would run into this problem with x64 SQL Server, the memtoleave has no relavence in X64.

The right combination of values for Threads, BufferCount and MaxTransferSize depends on the capability of your storage system. The Core2 architecture cores could compress about 150MB/s (or more), so if your disk system can only support 600MB/s, there is no point to having more than 4 threads.

In general BufferCount 4 and MTS 512K-1M gets you 95% utilization on the CPU if your disks can support it. Dropping MTS to 256K gives up around 10% performance, but if thats all the memtoleave you have, its good enough. All this only applies to 32-bit SQL.

There must be another source to what you are seeing

October 5, 2009 12:09 PM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement