Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Professional SQL Server 2008 Internals and Troubleshooting
This is the forum to discuss the Wrox book Professional SQL Server 2008 Internals and Troubleshooting by Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones, Steven Wort; ISBN: 978-0-470-48428-9
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional SQL Server 2008 Internals and Troubleshooting section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 15th, 2010, 04:52 PM
Registered User
 
Join Date: Jul 2010
Location: Arkansas
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Question Buffer Pool Question

On page 50 there is a discussion about three counters to view for a quick look at memory pressure. My question concerns a statement about the MSSQL$<instance>:Memory Manager\Total Server Memory (KB) and MSSQL$<instance>:Memory Manager\Target Server Memory (KB). The book states that "If Total is significantly less than Target, then it's likely that SQL Server cannot grow the buffer pool due to memory pressure". It does say to go and investigate further.

In some other reading I was doing recently, I saw something that said that if Total was less than Target, it meant there was plenty of memory but if Total started to get equal to, or greater, than Target, this indicated memory pressure. Unless I'm missing something, these are contradictory statements and, the second one (not in the book), seems to make more sense to me.

This makes even more sense after checking one of my servers and I see that Target is about 6 GB and Total is about 4 GB but the system is moving along very smoothly and the average page life expectancy is just over 175,000.

I'm just now really digging deep into performance tuning so I would really appreciate some clarification on this topic.

Thanks!
  #2 (permalink)  
Old July 16th, 2010, 06:04 AM
Wrox Author
 
Join Date: Feb 2010
Location: Swindon, England
Posts: 2
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to ChristianBolton
Default Buffer Pool Question

Hi soccerhawg,

What you've observed is an instance of SQL Server that has been configured to use more than it currently needs.

The Target Server Memory of 6GB is either what you've set as Max Server Memory or what SQL Server has dynamically set as the most it can have.

The Total Server Memory of 4GB is an indicator of one of three things:

- The server hasn't been running long enough to grow its usage up to the target of 6GB
- You're experiencing memory pressure that is preventing memory usage growing to 6GB
- There isn't enough workload on the server for SQL Server to need more than the 4GB that its using

The next step is to check PLE which in your case is 175,000 (over 48 hours). This shows that you don't have any memory pressure so based on those results I would conclude that your SQL Server doesn't need anymore than 4GB Max Server Memory. However, that may change over time of course depending on the distribution of your workload.

I wouldn't like to comment too heavily on your other source of information without seeing the context it was written in but I will say that Target and Total Server Memory should definitely be the same or very close on a well configured, busy SQL Server.

I hope that helps!

Regards,

Christian Bolton
Lead Author - Professional SQL Server 2008 Internals and Troubleshooting
MCA: SQL Server, MCM: SQL Server, MVP
The Following User Says Thank You to ChristianBolton For This Useful Post:
soccerhawg (July 16th, 2010)
  #3 (permalink)  
Old July 16th, 2010, 10:18 AM
Registered User
 
Join Date: Jul 2010
Location: Arkansas
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Default

That makes perfect sense. I was just getting a little tunnel vision and I was giving too much weight to one counter when I should just use this as a guide and just one part of my research.

In this case, I believe it is simply just a case that the server doesn't currently need anymore than the 4 GB of memory. I'm not going to reduce my memory, though, because, based on what I know about the system, I suspect the extra memory will be used over time.

Thanks for the response and helping me take a broader look at the situation.
  #4 (permalink)  
Old July 16th, 2010, 10:32 AM
Wrox Author
 
Join Date: Feb 2010
Location: Swindon, England
Posts: 2
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to ChristianBolton
Default Buffer Pool Question

You're welcome :)

Christian Bolton
Lead Author - Professional SQL Server 2008 Internals and Troubleshooting
MCA: SQL Server, MCM: SQL Server, MVP
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Buffer question dparsons ASP.NET 1.0 and 1.1 Professional 0 March 19th, 2006 10:45 PM
flushing buffer in A2K Loralee Access 1 September 26th, 2005 05:44 PM
Buffer Overflow rekha_jsr Oracle 3 February 3rd, 2005 07:11 PM
filling in a buffer JLN VB.NET 2002/2003 Basics 2 July 8th, 2004 08:06 AM



All times are GMT -4. The time now is 05:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.