Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

SQL Server error: messages are appearing when the working set of SQL Server 2005 process reaches 50 percent of the memory that is committed to the process. - SQL Server Knowledge Sharing Network (@sqlmaster)

SQL Server error: messages are appearing when the working set of SQL Server 2005 process reaches 50 percent of the memory that is committed to the process.

It is evident that SQL Server 2005 component handles memory differently as compared to SQL Server 2000 version. As one of the KBA refers SQL Server 2000 Enterprise Edition introduces support for the use of Microsoft Windows 2000 Address Windowing Extensions (AWE) to address approximately 8 GB of memory for instances that run on Microsoft Windows 2000 Advanced Server, and approximately 32 GB for instances that run on Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory that is not in use for other applications and the operating system. Each instance that uses this memory; however, must statically allocate the memory it needs. SQL Server can only use this AWE allocated memory for the data cache and not for executables, drivers, DLLs, and so forth. This is changed as per BOL documentation and you can get more information by referring to the topics such as: memory architecture, Server memory options, enabling AWE memory & Enabling Memory Support for Over 4 Gb of Physical Memory.

Also the compilation below as per one of the SSP member (Dave):

Physical Memory = 8GB
Target Server Memory = 6GB (referred from SYSMON)
Total Server Memory = 690MB (referred from SYSMON)
Minimum Server Memory = 2GB
Maximum Server Memory = 6GB

So the Target Server memory equals to the Maximum Server memory on the server.

Physical Memory = 32GB
Target Server Memory = 28GB (SYSMON reports)
Total Server Memory = 397MB   (SYSMON reports)
Minimum Server Memory = 4GB
Maximum Server Memory = 30GB

Here is the catch where the Target Serve memory is shown less than Maximum Server memory availabile. 

Once for all within SQL Server 64 bit edition ensure you have applied the 'Lock Pages in Memory' to the SQL Server service account in order to take into account on higher memory set. Also bear in mind any resource intensive activity within SQL Server will consume more memory during at that point of time of your testing, so better to run during less traffice hours and no scheduled jobs.

So when you talk about SQL Server 2000 you should think about MemToLeave area which is reserved for SQL alone, that is calculated as follows: 

max worker threads value (default is 255) * Stack Size (default is 512KB) + external needs setting

Bear in mind the default memtoleave is 128MB until SQL 70 version and increased to 256MB within SQL 2000 version. For this reason it is suggested to leave the settings as is in order to ensure that SQL Server always has a contiguous region of address space available in the MemToLeave area. The default setting of DYNAMIC memory for SQL Server grows and shrinks its memory usage by committing and de-committing buffers from the buffers reserved at startup. In any case the LazyWriter process you see within task manager is responsible for growing and shrinking the buffer pool. Always you see is memory usage increase and do not decrease, that means SQL will not release unless it is asked by windows process. What you see above matches to:

    Target Server Memory (KB) – The total amount of dynamic memory the server can consume. This corresponds to the buffers reserved at SQL Server startup.
    Total Server Memory (KB) – The total amount of dynamic memory the server is currently using. This corresponds to the sum of buffers committed in the BPool and OS buffers of type "OS In Use."

In addition to above warning message you would also see the error stated in ThisBlogpost which are generated when the 'working set'  of SQL Server 2005 process reaches half of its allocated memeory, which is 50 percent of memory that is committed for the process. Sometimes this is associated with a decrease in the performance too where the Windows process is trying to reduce the SQL 2005 process usage. Additionally to get what is the usage percent you could take help of DMV sys.dm_os_ring_buffers:

FROM sys.dm_os_ring_buffers

Bear in mind the above you would see within SQL Server 64 bit edition and related blogs on web explains a lot about, SlavaOka- & TonyRogerson blogs and the following
  • SQL-Workingset blog
  • 918483: How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
  • 905865: The sizes of the working sets of all the processes in a console session may be trimmed when you use Terminal Services to log on to or log off from a computer that is running Windows Server 2003
  • 920739: You may experience a decrease in overall system performance when you are copying files that are larger than approximately 500 MB in Windows Server 2003 Service Pack 1.

Additionally if you are getting this constantly better to open up a case with Microsoft support for a hotifx, as most of them are resolved with such fixes.


Published Monday, July 28, 2008 5:54 AM by ssqa.net


No Comments