Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql server using more memory issue Expand / Collapse
Author
Message
Posted Monday, September 2, 2013 2:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:18 AM
Points: 90, Visits: 135
sql server is 2008 r2 on 64 bit systems and os is windows 2008.

Hi friends at one of our client side, it show sql server is using 12gb of ram out of 32 .my client is asking why sql is using this much.also there is not much hits on it

can any body tell queries which can tell me how much memory is sql server and its components are using with details description.

They want to decrease its memory consumption i said its not possible once sql gets memory it doesnt release.but they are adamant.plz tell me solution

can max memory server fix this problem , buy what if i set it and then sql server requires more memory .

since its 64 bit setting awe wont help

but does "lock pages in memory" and min max setting would be usefull.

plz tell queries through which i can know what memory is begin used by sql server and its components like Bpool,others etc and all that.

what other options/things i should check since database is of 2 gb so fragmentation is not an issue i think.

plz help

regards
Anoop
Post #1490688
Posted Monday, September 2, 2013 3:17 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
Chapter 4: https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Set max server memory to a sensible value.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1490693
Posted Monday, September 2, 2013 11:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:18 AM
Points: 90, Visits: 135
GilaMonster (9/2/2013)
Chapter 4: https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Set max server memory to a sensible value.


hi gail thanks for link but can u more specific

I would download the book an study it but can you be more specific for my post its urgent


thanks and regards
Anoop Pandey
Post #1490750
Posted Tuesday, September 3, 2013 12:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
Err... the second line of my post....

As for why SQL's using the memory, because it can. Because it has not been told that it can't (via max server memory). Set max server memory and have a read through chapter 4 (which is around 15 or so pages) for all the details on what to set it to, how SQL uses memory, queries that let you view memory usage, etc.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1490764
Posted Wednesday, September 4, 2013 3:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:18 AM
Points: 90, Visits: 135
GilaMonster (9/3/2013)
Err... the second line of my post....

As for why SQL's using the memory, because it can. Because it has not been told that it can't (via max server memory). Set max server memory and have a read through chapter 4 (which is around 15 or so pages) for all the details on what to set it to, how SQL uses memory, queries that let you view memory usage, etc.



Thanks gail one last question

suppose sytem is 64 bit
suppose i set max server memory to certain value , enabled lock pages in memory is enabled, now suppose sql server requires memory more then that what i have set.so then what server do , will it acquire form os.

2> secound suppose sytem is 32 bit and awe is eanbled ,so is lock pages in memory min .max server option is set too,what happens in this scene

3>setting max server memory option does limit memort for all sql server components or only that of buffer pool
Post #1491193
Posted Wednesday, September 4, 2013 4:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
anoop.mig29 (9/4/2013)
suppose i set max server memory to certain value , enabled lock pages in memory is enabled, now suppose sql server requires memory more then that what i have set.so then what server do


Nothing. You set the max memory it can use.

2> secound suppose sytem is 32 bit and awe is eanbled ,so is lock pages in memory min .max server option is set too,what happens in this scene


Nothing. You set the max memory it can use.

3>setting max server memory option does limit memort for all sql server components or only that of buffer pool


Buffer pool (which is the larges component of SQL's memory usage)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1491218
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse