High memory usage of sqlservr.exe

[sudhir]

Hi,
Can anyone please tell me why sqlservr.exe uses very high memory usage?
In one of our application, we found that the system becoming too slow
because of this SQLSERVR.exe. The momory usage sometimes crossing>1 GB. Can
you please tell me
What is the function of SQLSERVR?
How to release the momory used by SQLSERVR?
Why the memory usage of SQLSERVR keep on increasing?

Thanks and Regards
Sudhir

[Uri Dimant]

sudir
It is by design.SQL Server allocates as much memory as it needs and realeses
it ifOS or another application needs the memory

Do you have MAX memory set up?

"sudhir" <sud...@discussions.microsoft.com> wrote in message
news:6C4F2148-0D0A-45B0...@microsoft.com...

[sudhir]

Hi,
But why this is taking > 1 GB memory? Yes the max server memory is set to
2147483647 MB. But once it crosses the 1 GB memory the system becomes too
slow. Can you please let me know how we can improve the performance? Or how
to release the memory?

Regards
Sudhir

[Uri Dimant]

How much memory does the server have?

"sudhir" <sud...@discussions.microsoft.com> wrote in message

news:5B39F37A-2751-4DCD...@microsoft.com...

[WiredUK]

Are you running other services on the server?

Is it SQL Server that is becoming slow or the other services?

If it is the other services, then you need to restrict the amount of memory
that SQL Server uses by using the aforementioned MAX memory.

"sudhir" <sud...@discussions.microsoft.com> wrote in message

news:5B39F37A-2751-4DCD...@microsoft.com...

[dhirenshah]

we have server with 2 GB RAM.Once we switch ON server,the sqlservr.exe increases drastically above 1.7 GB & inturn server becomes extremely slow.can you please suggest how we can restrict sqlservr.exe to a some specified limit or how we can clamp SQl memory.
Please do reply.

[dhirenshah]

[Jeffrey Williams]

Because your server only has 2GB of memory, and by default SQL Server will
take 2GB of memory - it is taking all of the servers memory. You need to
set the max memory to something less than 1.25GB of memory which will leave
enough memory for the OS and other SQL Server operations.

Do not set the min memory setting to anything at all - that way the OS can
manage all of the memory that is available.

Jeff

"dhiren shah" wrote in message
news:200812290552...@honeywell.com...

[Erland Sommarskog]

You can use sp_configure to set the max server memory:

sp_configure 'max server memory', 100
reconfigure

This will set the server to use at most 100 MB for the buffer cache.

However, you should not do this, because it's perfectly normal for SQL
Server to all memory it can if it needs it. This is because it thinks it
good to have data in cache rather than on disk.

Apparently, you SQL Server instance gets to do some heavy work already on
startup, and that is your real problem. You need to find out what that is.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Eric Russell]

The following script, will list every user, system, and tempdb table
contained in SQL Server's buffer memory. It is based on a much simpler script
I originally found in a blog somewhere years ago, but I made modifications to
resolve the actual object names and span across all databases. It's pretty
rough, so if you discover something is amiss, like not calculating the
storage MB accurately, then reply back with bug fixes.

-- SQL Server stores its data in 8KB data pages. As these pages are read
off disk they are stored in memory.
-- This is referred to as buffer memory. A list of all the data pages in
memory is stored in the dynamic management
-- view sys.dm_os_buffer_descriptors. Remember that keeping a clustered
index in the buffer (or memory) is the same
-- as keeping the table in memory.
-- NOTE: For performance reasons, I am first inserting the result of the
DMVs to table variables and then joining them.
declare @sys_allocation_units table
(
database_id int,
allocation_unit_id bigint,
type tinyint,
type_desc varchar(20),
container_id bigint,
data_space_id tinyint,
total_pages int,
used_pages int,
data_pages int
)

insert into @sys_allocation_units
EXEC sp_MSforeachDB @command1='use ?; select db_id(''?'') as database_id, *
from sys.allocation_units'

declare @sys_partitions table
(
database_id int,
objname varchar(255),
partition_id bigint,
object_id bigint,
index_id int,
partition_number int,
hobt_id bigint,
rows bigint
)

insert into @sys_partitions
EXEC sp_MSforeachDB @command1='use ?; select db_id(''?'') as database_id,
object_name(object_id) as objname, * from sys.partitions;'

declare @allocation table
(
database_id int,
objname varchar(255),
index_id tinyint,
allocation_unit_id bigint,
object_id bigint
)

insert into @allocation
SELECT au.database_id, objname, index_id ,allocation_unit_id, object_id
FROM @sys_allocation_units AS au
INNER JOIN @sys_partitions AS p
ON p.database_id = au.database_id and
au.container_id = p.hobt_id and
(au.type = 1 OR au.type = 3)
UNION ALL
SELECT au.database_id, objname, index_id, allocation_unit_id, object_id
FROM @sys_allocation_units AS au
INNER JOIN @sys_partitions AS p
ON p.database_id = au.database_id and
au.container_id = p.hobt_id and
au.type = 2

declare @bd table
(
database_id int,
allocation_unit_id bigint,
Buffered_Page_Count int,
row_count int
)

insert into @bd
SELECT
bd.database_id,
bd.allocation_unit_id,
count(*)AS Buffered_Page_Count ,
sum(bd.row_count) as row_count
FROM
sys.dm_os_buffer_descriptors AS bd
GROUP BY
bd.database_id,
bd.allocation_unit_id

SELECT --distinct
db_name(bd.database_id) as DBName,
obj.ObjName,
sum(bd.Buffered_Page_Count) as Buffered_Page_Count,
sum(bd.row_count) as row_count,
convert(numeric(9,2),sum(bd.Buffered_Page_Count) * 8192.0 / (1024.0 *
1024.0)) as Buffer_MB
FROM
@bd as bd
INNER JOIN @allocation AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id and
bd.database_id = obj.database_id
group by
db_name(bd.database_id),
obj.ObjName
order by
convert(numeric(9,2),sum(bd.Buffered_Page_Count) * 8192.0 / (1024.0 *
1024.0)) desc
--db_name(bd.database_id),
--obj.ObjName

[khwaja Sohail]

Dear , what type of operating system are you using ? if it is Windows Server 2003 Standard Edition by default the SQLserve.exe can utilize upto 2GB maximume even if you have 10GB RAM .Now first of all upgrade your RAM by 4GB or >4GB . Then you have to remove the default value through below steps .
1.Right Click on Mycomputer , go to properties .
2.Go to Advance Tab/Startup and recovery , press Settings.
3.System Startup , click on Edit /Boot.ini notepad must open .
4.You should be able to see the following lines
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Standard" /noexecute=optout /fastdetect
5.Add the following switches at the of this line /3GB /PAE
6.At last it must look like
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Standard" /noexecute=optout /fastdetect /3GB /PAE
7.Please note that if it windows Server 2008 and Windows server 2003 Enterprise Edition such type of switch is not required
Still you have a problem drop an email to my personal email id Khwajam...@gmail.com

> On Tuesday, March 20, 2007 4:27 AM sudhi wrote:

> Hi,
> Can anyone please tell me why sqlservr.exe uses very high memory usage?
> In one of our application, we found that the system becoming too slow
> because of this SQLSERVR.exe. The momory usage sometimes crossing>1 GB. Can
> you please tell me
> What is the function of SQLSERVR?
> How to release the momory used by SQLSERVR?
> Why the memory usage of SQLSERVR keep on increasing?
>
> Thanks and Regards
> Sudhir

>> On Tuesday, March 20, 2007 5:35 AM Uri Dimant wrote:

>> sudir
>> It is by design.SQL Server allocates as much memory as it needs and realeses
>> it ifOS or another application needs the memory
>>
>> Do you have MAX memory set up?

>>> On Tuesday, March 20, 2007 6:27 AM sudhi wrote:

>>> Hi,
>>> But why this is taking > 1 GB memory? Yes the max server memory is set to
>>> 2147483647 MB. But once it crosses the 1 GB memory the system becomes too
>>> slow. Can you please let me know how we can improve the performance? Or how
>>> to release the memory?
>>>
>>> Regards
>>> Sudhir
>>>
>>>
>>> "Uri Dimant" wrote:

>>>> On Tuesday, March 20, 2007 7:22 AM Uri Dimant wrote:

>>>> How much memory does the server have?

>>>>> On Tuesday, March 20, 2007 9:32 AM WiredUK wrote:

>>>>> Are you running other services on the server?
>>>>>
>>>>> Is it SQL Server that is becoming slow or the other services?
>>>>>
>>>>> If it is the other services, then you need to restrict the amount of memory
>>>>> that SQL Server uses by using the aforementioned MAX memory.
>>>>>
>>>>>
>>>>> "sudhir" <sud...@discussions.microsoft.com> wrote in message
>>>>> news:5B39F37A-2751-4DCD...@microsoft.com...

>>>>>> On Monday, December 29, 2008 12:52 AM dhiren shah wrote:

>>>>>> we have server with 2 GB RAM.Once we switch ON server,the sqlservr.exe increases drastically above 1.7 GB & inturn server becomes extremely slow.can you please suggest how we can restrict sqlservr.exe to a some specified limit or how we can clamp SQl memory.
>>>>>>
>>>>>> Please do reply.

>>>>>>> On Monday, December 29, 2008 12:55 AM dhiren shah wrote:

>>>>>>> we have server with 2 GB RAM.Once we switch ON server,the sqlservr.exe increases drastically above 1.7 GB & inturn server becomes extremely slow.can you please suggest how we can restrict sqlservr.exe to a some specified limit or how we can clamp SQl memory.
>>>>>>>
>>>>>>> Please do reply.

>>>>>>> Submitted via EggHeadCafe
>>>>>>> Twitter Search API with jQuery and JSONP
>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/94d7bda5-c477-4ff9-b71d-e24e5fc70c24/twitter-search-api-with-jquery-and-jsonp.aspx

[Bob Barrows]

Dear, you are responding to a question that was posted _4 years ago_. Do you
really anticipate a reply??