RE: how to reduce the memory useage of the sqlserver.exe process

Hello Customer,

Thank you for posting here.

From your post, I understand that you would like to reduce the memory usage
of sqlserver.exe.

Before we go to any future, I'd like to confirm do you experienced really
performance issue on these SBS Server?

Based on my research, SQL Server is designed to use memory, it dynamically
grows and shrinks the size of its buffer pool (cache) depending on the
physical memory load reported by the operating system. As long as enough
memory is available to prevent paging (between 4 - 10 MB), the SQL Server
buffer pool will continue to grow. As other processes on the same computer
as SQL Server allocate memory, the SQL Server buffer manager will release
memory as needed. SQL Server can free and acquire several megabytes of
memory each second, allowing it to quickly adjust to memory allocation
changes.

For more information, please see the article below:

321363 INF: SQL Server Memory Usage
http://support.microsoft.com/?id=321363

If you still want to reduce the memory usage, let's move on:

Step 1:
=====
First let's check which SQL instance used the high memory:

1. Open Performance in Administrative Tools.
2. Add the followoing counters:

Performance object: MSSQL$MSFW-Memory Manager
Counter: Total Server Memory (KB)

Performance object: MSSQL$MONITORING-Memory Manager
Counter: Total Server Memory (KB)

Performance object: MSSQL$SHAREPOINT-Memory Manager
Counter: Total Server Memory (KB)

Then you can see which instance used high memory.

Step 2:
=====
After we isolate the suspicious instacne , we can use max server memory to
prevent SQL Server from using more that the specified amount of memory (we
can also use min server memory to guarantee a minimum amount of memory to
an instance of SQL Server). To do so, open a command prompt and run the
following command:

osql -E -S sbsserver\SBSMONITORING

You will enter the osql command prompt. Run the commends below (replace
<xxx> with the amount of memory):

1> EXEC sp_configure ''show advanced options'', 1
2> reconfigure
3> go

1> EXEC sp_configure ''min server memory'', <xxx>
2> reconfigure
3> go

1> EXEC sp_configure ''max server memory'', <xxx>
2> reconfigure
3> go

After doing the above steps, you may run the commands to check the
configured memory usage:

1> EXEC sp_configure
2> go

After that, please check if the memory usage is reduced.

Hope it helps.

Best regards,

Manfred Zhuang(MSFT)
Microsoft Online Newsgroup Support

Get Secure! - www.microsoft.com/security

=====================================================
This newsgroup only focuses on SBS technical issues. If you have issues
regarding other Microsoft products, you'd better post in the corresponding
newsgroups so that they can be resolved in an efficient and timely manner.
You can locate the newsgroup here:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx

When opening a new thread via the web interface, we recommend you check the
"Notify me of replies" box to receive e-mail notifications when there are
any updates in your thread. When responding to posts via your newsreader,
please "Reply to Group" so that others may learn and benefit from your
issue.

Microsoft engineers can only focus on one issue per thread. Although we
provide other information for your reference, we recommend you post
different incidents in different threads to keep the thread clean. In doing
so, it will ensure your issues are resolved in a timely manner.

For urgent issues, you may want to contact Microsoft CSS directly. Please
check http://support.microsoft.com for regional support phone numbers.

Any input or comments in this thread are highly appreciated.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Thread-Topic: how to reduce the memory useage of the sqlserver.exe
process
| thread-index: Ach4O9kWKM+E8Y2CT5KLtygKHNTpig==
| X-WBNR-Posting-Host: 207.46.19.197
| From: =?Utf-8?B?aGVuZ3NoYW55YW5n?=
<hengshanyang@xxxxxxxxxxxxxxxxxxxxxxxxx>
| Subject: how to reduce the memory useage of the sqlserver.exe process
| Date: Mon, 25 Feb 2008 21:53:02 -0800
| Lines: 1
| Message-ID: <909B968F-45E7-4EF2-8A4E-D88CFD3A0834@xxxxxxxxxxxxx>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| Newsgroups: microsoft.public.windows.server.sbs
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.windows.server.sbs:94707
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.windows.server.sbs
|
| please tell me.
|

.

Loading