Memory almost 90 % using in SQLSERVR.EXE

Question

Post reply

Memory almost 90 % using in SQLSERVR.EXE

SQL Galaxy

One Orange Chip

Points: 29472
More actions
August 29, 2011 at 1:16 am

#242957

Hi,
Server OS - Win 2008 Enterprise Edition with SP2.
MSSQL - SQL Server 2008 Enterprise Edition with SP2.
RAM - 4GB.
sql server using dynamic memory not configured static memory.
I have checked sqlserver using memory 90% in Task manager, and checked out as following checklist, all are showing 1.5 GB constantly not releasing memory from Buffer Pool.
Could anyone suggestion me what are the other checklist and how to clear the Buffer Pool, reduce memory from Task manager.
1. Buffer Pool size --1654936 near about(1.54GB) SIZE
2. DBCC MEMORYSTATUS -Procedure cache
--TotalPages20223 * 8 = 161784 MB
--161784 near about(1.57GB) SIZE
3. Buffer PoolValue
Committed204776
Target 204776
Database178531
Dirty531
In IO0
Latched0
Free173
Stolen26072
Reserved0
Visible204776
Stolen Potential168465
Limiting Factor11
Last OOM Factor0
Page Life Expectancy1515300
4. Buffer cache Hit Ratio - --1.0000000000000000000
5. Average Page Life Expectancy 13:19:01:000 ( run it sampling 1 Minutes)
Please suggestion me, how reduce memory using in sqlserver? and what are the steps should be followed?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

What kind of operations are you performing on this server? Is it a 64 Bit instance?

Have you tried DBCC DROPCLEANBUFFERS

Jayanth Kurup[/url]

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Jayanth_Kurup (8/29/2011)
What kind of operations are you performing on this server? Is it a 64 Bit instance?
Have you tried DBCC DROPCLEANBUFFERS

32 bit OS, OLTP Transaction.

No, I am not tried DBCC DROPCLEANBUFFERS, I have doubt if Ran dropcleanbuffer existing procedure cache plan will be droped, so in the cause of degrade perfromance.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What's the setting for max server memory? Is AWE enabled?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Hi,

1. AWE is not enabled,

2. Default memory configuration, Manually not setting max memory.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

If you want to reduce memory usage, you must set max memory. By leaving it at default you're telling SQL it can use as much memory as it wants with no limits other than physical memory size. The behaviour you're seeing (allocate, never release) is intended behaviour (and well documented behaviour)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

Thanks for reply....

Ok, I should change and setting max memory in SQLSERVER Instance.

If server 4 GB RAM, how much I should allocated for max memory?

One more doubt, pl. calrify ,If ran DBCC dropcleanbuffer command, It just clean existing procedure cache, Is it advisble run this command peroidcally.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

ananda.murugesan (8/29/2011)
Ok, I should change and setting max memory in SQLSERVER Instance.
If server 4 GB RAM, how much I should allocated for max memory?

Depends what else is running on the server. With 32 bit SQL and no AWE, SQL won't be able to use more than 1.7GB (if the OS is 32 bit) or 4GB (if the OS is 64 bit). You need to set it so that there's free space left for the OS and any other apps running on that server.

p.s. Task Manager lies, never use that to check SQL's memory usage. Use the Total Server Memory counter in perfmon

One more doubt, pl. calrify ,If ran DBCC dropcleanbuffer command, It just clean existing procedure cache, Is it advisble run this command peroidcally.

DropCleanBuffers does not clear the procedure cache (that's FreeProcCache), it clears the data cache. Clearing either data or procedure cache is a very silly thing to do on a production server. SQL caches data and procedures to reduce the work it needs to do to run queries. Clear those and performance will degrade while SQL re-populates the caches.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 8

Thanks for clarification and reply...

One more doubt for LOCK PAGE MEMORY setting. (memory swapping)

win 2008 enterprise edition, 32 bit or 64 bit. Is it required to enable lock page memory setting. If seeting this what advantage for SQL server 2008 side?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

If you're going to enable AWE, that has to be enabled. If you do enable it, you MUST set a max server memory that leaves enough space for the OS (or you risk OS crash)

It's a big topic, maybe try google, it should turn up several articles.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass