[itdiscuss] SQL 2008 R2 Memory Configuration

Wed Jan 12 12:40:13 EST 2011

I also look at Wait Stats.  This is something that was added in SQL 2k5.  In
a nutshell, the server keeps a running tally of how many milliseconds have
been spent waiting, broken down by wait-type, since the mssqlserver service
was started.  Thus, you can see at a glance what it's had to wait on most.

Some setup is required, since looking at a single tally number doesn't give
you any date/time context.  I have a sql agent job that runs every few
minutes, putting a snapshot of the current wait stats into a repository with
a timestamp.  From this I can query total milliseconds for each wait-type on
any given timespan.  See the skitch url below for a screenshot of the Excel
file I use to monitor.

Here's more info on Waitstats:
https://skitch.com/degauss/rjyrn/waitstats-sample (screenshot)
http://www.brentozar.com/archive/2009/07/the-ozar-family-tradition-of-performance-monitoring/
(cuz
I like Brent, too)
<http://www.brentozar.com/archive/2009/07/the-ozar-family-tradition-of-performance-monitoring/>
http://www.brentozar.com/sql-server-training-videos/wait-stats-lose-wait-fast/
(but
sometimes he gets a little crazy...)
http://www.jasonstrate.com/tag/wait-stat/ (Jason has several great articles
here, including the framework I use to snapshot)
http://sqlserverpedia.com/wiki/Wait_Types (launch page for more info on each
wait type)
http://www.informit.com/articles/article.aspx?p=686168&seqNum=5 (long
article diving into the different types of waits)

Jeremy Marx
Database Administrator
Rhema Bible Church

2011/1/12 Jason Hand <jhand at xcentric.com>

> Any of the markers having to do with waits and locks.  When SQL is not
> performing properly you will have transactions just sitting there waiting
> for either CPU cycles or Disk I/O cycles.  If the CPU’s are underpowered you
> will see excessive utilization by the SQLServer service.  If the Disk is
> paging too much then you might have too little RAM for your database(s) to
> use.  If the Disk is bottlenecked because of not enough spindles due to
> either poor iScsi implementation or poor performing disks then you should
> look at faster storage.  Perfmon can tell you all of this and the site
> article below can tell you and show you with some how to videos what you
> need to know to set up Perfmon to check for these issues.
>
>
>
>
> http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
>
>
>
> Jason Hand
> Infrastructure Services Consultant
> (678) 297-0066 x514 | jhand at xcentric.com
>
>
>
> [image: Description:
> http://static.xcentric.com/images/signature/1010_service.gif]<http://xcentric.com/help>
>
> [image: Description: https://www.xcentric.com/images/signature/connect.gif]
>
> [image: Description:
> https://www.xcentric.com/images/signature/facebook.gif]<http://www.facebook.com/pages/Xcentric/101402506769>
>
> [image: Description: https://www.xcentric.com/images/signature/twitter.gif]<http://twitter.com/xcentric>
>
> [image: Description:
> https://www.xcentric.com/images/signature/linkedin.gif]<http://www.linkedin.com/companies/xcentric>
>
> [image: Description: https://www.xcentric.com/images/signature/vimeo.gif]<http://www.vimeo.com/xcentric>
>
> 3015 Windward Plaza, Suite 500, Alpharetta, GA 30005
> [image: Description: Phone:]866-XCENTRIC |[image: Description: Fax:](678)
> 297-0190
> ------------------------------
>
> This e-mail and any attachments are intended solely for the use of the
> named recipient or recipients. This e-mail may contain privileged
> communications or work product. Any dissemination, use or reliance on this
> e-mail by anyone other than an intended recipient is strictly prohibited. If
> you are not the named recipient and a client of this Firm, you are
> prohibited from any further viewing of the e-mail or any attachments or from
> making any use of the e-mail or attachments. If you believe you have
> received this e-mail in error, notify the sender immediately and permanently
> delete the e-mail, any attachments, and all copies thereof from any drives
> or storage media and destroy any printouts of the e-mail or attachments.
>
>
> *From:* discuss-bounces at janoah.net [mailto:discuss-bounces at janoah.net] *On
> Behalf Of *Bobby Stewart
> *Sent:* Wednesday, January 12, 2011 11:33 AM
>
> *To:* discuss at itdiscuss.org
> *Subject:* Re: [itdiscuss] SQL 2008 R2 Memory Configuration
>
>
>
> Thanks Jeremy. That brings up the thing that is probably key to getting
> what I need to make decisions about this server. What do you consider to be
> the key performance indicators that should be monitored to properly evaluate
> the performance of a server hosting SQL 2008 R2?
>
>
>
> You’ve mentioned Lock Pages in Memory. What else is key?
>
>
>
> Bobby Stewart
> Network Analyst
> Brentwood Baptist Church
> Brentwood, TN
> www.brentwoodbaptist.com
> +1 (615) 324-6149 office
> +1 (615) 830-0012 cell
>
>
>
> *From:* discuss-bounces at janoah.net [mailto:discuss-bounces at janoah.net] *On
> Behalf Of *Jeremy Marx
> *Sent:* Wednesday, January 12, 2011 9:15 AM
> *To:* discuss at itdiscuss.org
> *Subject:* Re: [itdiscuss] SQL 2008 R2 Memory Configuration
>
>
>
> Jason was right on.  While allowing SQL Server and Windows to dynamically
> manage their memory works well in most day-to-day situations, sometimes SQL
> Server can't release memory fast enough to respond to a high-server-load
> situation.
>
>
>
> The rule of thumb I've seen for setting the upper limit says 2gb except
> when you have 32gb or more (and then it goes up).  However, we don't
> typically have as much memory as those who come up with those rules, so I'd
> load-test, trying different values.  I'm sure you have a pretty good idea
> what Windows needs.
>
>
>
> Bobby, I'd recommend also looking at Lock Pages in Memory.  All this does
> is keep SQL Server's memory pages from being paged out. (A side note: an
> optimally running SQL Server machine won't use its page file.)
>
>
>
> Of course, these are all general recommendations given without more info on
> your particular system.
>
>
>
> Jeremy Marx
>
> Database Administrator
>
> Rhema Bible Church
>
>
>
> 2011/1/11 Jason Hand <jhand at handfamilysite.com>
>
> It does dynamically allocate but I usually still statically assign giving
> all but about 1 to 1.5 gb of ram for the server os.  I was experiencing
> transaction wait at times of high use and once I allowed the os a little
> room it worked better.
>
> The overhead of the dynamic system has to add some lag and that must be
> what was causing it for me.
>
> -Jason
>
> On Jan 11, 2011 6:02 PM, "Bobby Stewart" <bStewart at brentwoodbaptist.com>
> wrote:
> > I've just installed a network monitoring solution and have begun
> > receiving errors it detects. One of those is the available memory on the
> > SQL server. The monitor is reporting available memory of 4% while the
> > trigger for the alert is 20%.
> >
> >
> >
> > So, I've done a little research. It appears that a SQL Server 2008 R2
> > instance will consume as much memory as is available in the system but
> > will release it if the OS requires it for other tasks. So, the
> > recommendation is that you leave it at the default setting, permitting
> > SQL to munch as much memory as you can throw at it.
> >
> >
> >
> > Still, there's a little gnat buzzing around in the back of my head that
> > says that I really should give the OS more space by adjusting the max
> > amount of memory that SQL will consume, just like we had to do for SQL
> > 2000.
> >
> >
> >
> > So, topic of discussion: When SQL 2008 R2 on Windows 2008 R2, how are
> > you configuring memory allocation; dynamic or static and with what
> > calculation for determining the amount?
> >
> >
> >
> > Bobby Stewart
> > Network Analyst
> > Brentwood Baptist Church
> > Brentwood, TN
> > www.brentwoodbaptist.com
> > +1 (615) 324-6149 office
> > +1 (615) 830-0012 cell
> >
> >
> >
>
>
> _______________________________________________
> it discuss mailing list: discuss at itdiscuss.org
> Mailing List: http://itdiscuss.org/discuss
> Internet Relay Chat: irc://irc.freenode.net/citrt
>
>
>
> --- Scanned by the Xcentric Email Security system (http://www.xcentric.com) ---
>
>
>
> _______________________________________________
> it discuss mailing list: discuss at itdiscuss.org
> Mailing List: http://itdiscuss.org/discuss
> Internet Relay Chat: irc://irc.freenode.net/citrt
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://optimus.thompsonic.com/pipermail/discuss/attachments/20110112/82bba509/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/x-citrix-gif
Size: 459 bytes
Desc: not available
URL: <http://optimus.thompsonic.com/pipermail/discuss/attachments/20110112/82bba509/attachment-0008.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/x-citrix-gif
Size: 84 bytes
Desc: not available
URL: <http://optimus.thompsonic.com/pipermail/discuss/attachments/20110112/82bba509/attachment-0009.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/x-citrix-gif
Size: 2699 bytes
Desc: not available
URL: <http://optimus.thompsonic.com/pipermail/discuss/attachments/20110112/82bba509/attachment-0010.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/x-citrix-gif
Size: 441 bytes
Desc: not available
URL: <http://optimus.thompsonic.com/pipermail/discuss/attachments/20110112/82bba509/attachment-0011.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/x-citrix-gif
Size: 376 bytes
Desc: not available
URL: <http://optimus.thompsonic.com/pipermail/discuss/attachments/20110112/82bba509/attachment-0012.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/x-citrix-gif
Size: 401 bytes
Desc: not available
URL: <http://optimus.thompsonic.com/pipermail/discuss/attachments/20110112/82bba509/attachment-0013.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/x-citrix-gif
Size: 358 bytes
Desc: not available
URL: <http://optimus.thompsonic.com/pipermail/discuss/attachments/20110112/82bba509/attachment-0014.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/x-citrix-jpeg
Size: 396 bytes
Desc: not available
URL: <http://optimus.thompsonic.com/pipermail/discuss/attachments/20110112/82bba509/attachment-0015.bin>