March 28, 2012 at 8:41 am
It'll try, yes. The algorithm's a complex LRU check.
I generally leave SQL's min memory at 0. Monitor, if you see memory allocations keep dropping very, very low then maybe you can up the min, or add more memory to the server. Seriously though, there shouldn't be anything else on a dedicated server that can chow through 7GB of memory and froce SQL to give up all of it's buffer pool
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
March 28, 2012 at 9:00 am
"Monitor, if you see memory allocations keep dropping very"
Can you advice- what to monitor? I know, know- a lot of info, but honestly very often it's not clear and confusing.
For example, right now I use PerfMon to monitor (besides Total/Target memory- equal values) Memory:Available Mbytes counter. Absolutely different recommended values- from 20% to 5% of Total memory. In my case it's around 2% now and not going up (rather a bit down- probably result of long running job- SSIS package) and this value looks a bit scary to me.
March 28, 2012 at 9:03 am
Chapter 1 and 4
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
March 28, 2012 at 9:38 am
As per this book recommendation this value (AvailableMbytes) "it remains above 150–300 MB". Probably that mean in my case (around 100 MB) we should decrease a bit Max memory value (probably from 6 GB to 5 GB and monitor this value). Good book- still did not have time to read it carefully. Thanks for recommendation
March 29, 2012 at 10:52 am
Isn't the (Windows) Lock Pages in Memory option due to be deprecated in future versions?
Right from MSDN:
"This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible."
😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
March 29, 2012 at 10:59 am
TravisDBA (3/29/2012)
Isn't the (Windows) Lock Pages in Memory option due to be deprecated in future versions?Right from MSDN:
"This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible."
😀
Yes, but in 32-bit systems it is still very relevant.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 11:05 am
Hmmmm. the MSDN article does not distinguish between the two. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
March 29, 2012 at 11:08 am
TravisDBA (3/29/2012)
Hmmmm. the MSDN article does not distinguish between the two. 😀
Considering that the current (and subsequently next) version of SQL Server will only run on 64-bit server operating systems, that's not surprising.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 11:15 am
True, but SQL 2008 still can exist on 32 bit systems I believe (which alot of people are still currently on), so this is something that definitely should be removed, or at least considered, before moving to 64-bit 2012 systems and beyond.:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
March 29, 2012 at 11:18 am
TravisDBA (3/29/2012)
True, but SQL 2008 still can exist on 32 bit systems (which alot of people are on) I believe, so this is something that definitely should be removed before upgrading to 2012 systems and beyond.:-D
2012 will run on 32-bit server operating systems.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 11:20 am
True, but the recommendation from MSDN is to remove it currently anyway.:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
March 29, 2012 at 11:22 am
TravisDBA (3/29/2012)
True, but the recommendation from MSDN is to remove it currently anyway.:-D
It depends on who "they" is..."they" over at Microsoft have recommend a lot of stupid things in the past. On this topic, your mileage may vary. Sometimes you nee LPIM to prevent Windows from stealing SQL Server's memory. It's less of a problem in Server 2008, but it's still there.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 11:25 am
True, but I am just going from what is published in MSDN and 32-bit and 64 bit is not distinguished. 😀
http://msdn.microsoft.com/en-us/library/ms190730.aspx
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
March 29, 2012 at 12:21 pm
TravisDBA (3/29/2012)
Isn't the (Windows) Lock Pages in Memory option due to be deprecated in future versions?
No, it's a documentation error. It's AWE that in final deprecation and the deprecation notice was accidentally applied to the wrong page. It will be fixed in the next refresh of the documentation.
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
March 29, 2012 at 1:08 pm
Ok thanks. Good to know. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply