Chat now with support
Chat with Support

LiteSpeed for SQL Server 8.9.5 - Security and Compliance Guide

Troubleshoot Performance-Related Issues

Please follow troubleshooting techniques below.MemToLeave is dedicated to executing linked server queries, extended stored procedure calls and OLE automation procedure calls. By default, MemToLeave is 384 MB and the balance of total memory available to SQL Server will determine the size of BPool. It is important to realize that although MemToLeave is predominantly used for in-process COM objects, such as extended procedures and linked server queries; memory allocations from this area are also used for large stored procedure or query execution plans. Any memory allocation by SQL Server that is larger than 8KB comes from MemToLeave. Furthermore, memory allocations less than 8KB can, in some cases, come from MemToLeave region as well.

LiteSpeed uses Virtual Device Interface (VDI) to generate its backups. This operates in a single contiguous region of addresses within the SQL Server process space known as MemToLeave memory area. It is set aside at startup and is left unallocated by SQL Server for the use by components in the SQL Server process space, such as extended procedures, COM/OLE Automation objects, and linked server queries. Memory allocations by SQL Server larger than 8 KB are also made from the MemToLeave area.

SQL Server's MemToLeave area becoming fragmented so that there is insufficient contiguous space to allocate the buffers required for the backups. Rebooting SQL Server will free the memory, but the underlying cause of memory fragmentation should be addressed for long-term resolution.

Factors that can drain this memory area:

  • Number of databases on each server
  • Number of Servers on physical machine
  • Number of concurrent users
  • Amount of data
  • Number of data/log files
  • Running 3rd party software

To check the available contiguous memory, do one of the following:

(required by the VDI). Make sure the available contiguous memory is more than 64 KB.

How to allocate more resources

Although the memory areas are transparent from SQL Server users perspective, the database administrator can adjust the size of MemToLeave by using a switch during SQL Server startup. You can further tweak the amount of memory reserved for MemToLeave area by adjusting the value of maximum worker threads configuration setting. That is because 128MB of MemToLeave is reserved for worker threads - by default SQL Server uses 255 worker threads (times .5MB per worker thread). The balance of 256 MB is used for memory allocations outside of buffer pool. Use the start-up parameter -g with SQL Server. The -g parameter can be set in Enterprise Manager by right-clicking on the server, selecting 'Properties' and clicking on the Start-Up parameters button at the bottom of the properties window. The default -g setting is 128Mb. It is recommended that -g256 is used, if this does not resolve the issue increase the value to -g512. Changing these settings does require SQL Server to be restarted for the changes to take affect.

When you enable AWE SQL Server can use physical memory in addition to virtual memory. At this point the database engine can utilize more than 3GB of RAM for the buffer pool. Note that if you use AWE, SQL Server 2000 will have to lock pages in memory at startup. With AWE enabled SQL Server 2000 acquires all available memory when it is started and does not release it back to the operating system until it is stopped. Dynamic memory configuration is simply ignored. Since SQL Server locks pages in memory using AWE is only recommended if SQL Server is the only significant application running on the server. The AWE behavior is different with SQL Server 2005. With this version dynamic allocation of memory works as designed; SQL Server picks up minimal amount of memory at startup and then grabs additional memory as needed.

Performance issues may be caused by sending large chunks of data over to your backup destination. Add the parameter @maxtransfersize to the LiteSpeed backup scripts or set the option within the LiteSpeed Backup options available from the Enterprise Console. If nothing is selected for this option the default value is 1Mb (1048576 bytes). Add the @maxtransfersize parameter to the backup and reducing the size to 500Kb (512000), then perform the backup again.

The other parameter that has been used in conjunction with the MaxTransferSize parameter has been the Threads parameter. The installation default for LiteSpeed is 3. The script parameter is @threads and may be reduced to 1. The recommended setting is n-1 (n being the number of CPUs) in a multi-processor environment.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating