Solved

SQL 2008 SP2, how to avoid memory pressure or capping system resources ?

Posted on 2011-02-23
5
2,723 Views
Last Modified: 2012-05-11
Hello,

I know this topic is very broad and really comes down to the applications and users making use of the server, but I was just looking for some general tunning directions/tips. We recently migrated from SQL2005 to 2008, and some of the services being served off there:

- Sharepoint 2010 (about 30 users)
- Microsoft Forefront Threat Management
- Microsoft Forefront End-Point Client Protection
- Microsoft System Centre Configuration Manager
- Windows Update Server

Some user databases would include DWH/Cubes totally about 30-40gb in data.

The specs are 2-core and 10GB Ram.

Recent issues (today) that I ran into was the server literally not allowing any connections to it, so after some digging I found out a developer was building a cube which crashed on him because the server couldn't handle it.

Part of the issue the developer will review and try to change configuration so the cube does not blow up the server, but another part of my issue is how do I not get SQL Server to cap system resources and deny services to other applications?

I guess, is it possible to limit resources to SQL? Any general tuning tips on Cubes (when a developer is processing it, they mentioned something about flushing cache to database?)..

The errors that the server came back with were...

Date		02/23/11 4:52:58 PM
Log		SQL Server Agent (Current - 02/23/11 4:58:00 PM)

Message
[298] SQLServer Error: 26, Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server. [SQLSTATE 08001]

Date		02/23/11 4:52:58 PM
Log		SQL Server Agent (Current - 02/23/11 4:58:00 PM)

Message
[298] SQLServer Error: 233, Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08001]

Open in new window


Also some messages I have not seen before in the SQL Server log..


Date            02/23/11 5:03:39 PM
Log            SQL Server (Current - 02/23/11 5:20:00 PM)

Source            spid139

Message
MEMORYBROKER_FOR_RESERVE (internal)           Pages
---------------------------------------- ----------
Allocations                                   55130
Rate                                              0
Target Allocations                           598210
Future Allocations                           193780
Overall                                      840453
Last Notification                                 1



Date            02/23/11 5:03:39 PM
Log            SQL Server (Current - 02/23/11 5:20:00 PM)

Source            spid139

Message
MEMORYBROKER_FOR_STEAL (internal)             Pages
---------------------------------------- ----------
Allocations                                    6963
Rate                                              0
Target Allocations                           411393
Future Allocations                                0
Overall                                      840453
Last Notification                                 1




Date            02/23/11 5:03:39 PM
Log            SQL Server (Current - 02/23/11 5:20:00 PM)

Source            spid139

Message
MEMORYBROKER_FOR_CACHE (internal)             Pages
---------------------------------------- ----------
Allocations                                  390920
Rate                                              0
Target Allocations                           795350
Future Allocations                                0
Overall                                      840453
Last Notification                                 1



Date            02/23/11 5:03:38 PM
Log            SQL Server (Current - 02/23/11 5:20:00 PM)

Source            spid139

Message
Buffer Pool                                   Value
---------------------------------------- ----------
Committed                                   1032192
Target                                      1048519
Database                                     620925
Dirty                                          2557
In IO                                             0
Latched                                           9
Free                                              0
Stolen                                       411267
Reserved                                      41742
Visible                                     1048519
Stolen Potential                             543084
Limiting Factor                                  13
Last OOM Factor                                  20
Last OS Error                                     0
Page Life Expectancy                              0


Once the Cube stopped processing, everything went back to normal. Just trying to better bullet proof my SQL Server.
0
Comment
Question by:mirde
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:mass2612
ID: 34967515
Hi,

It sounds like the box was starved of memory. What are your max and min memory settings on your SQL instance? I would probably set those. Are these services such as Sharepoint just using this SQL server for SQL and not running any of the applications?

If this is the case I would probably suggest you leave 2GB RAM for the OS, backup agents, etc and then configure a max server setting of 6-8GB. You may also want to experiment with limiting the Analysis services memory. By default I think it will try to use up 80% of the total memory in the system.

Analysis Services Preallocate Memory Setting – Insight
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/08/19/analysis-services-preallocate-memory-setting-insight.aspx

You should have also been able to use the Dedicated Admin Connection (DAC) to connect to the SQL instance as SQL reserves a portion of memory for the DAC in such cases.

Using a Dedicated Administrator Connection
http://msdn.microsoft.com/en-us/library/ms189595.aspx
0
 

Author Comment

by:mirde
ID: 34970683
Mass,

They are the default, MIN is 0 and MAX is 2147483647. SharePoint is not using any applications, basically a default installation. Will look at the links you provided.
0
 
LVL 29

Accepted Solution

by:
mass2612 earned 500 total points
ID: 34975021
Ok good luck with it. Basically this means that SQL will try to grab the entire 10GB available in your server if it needs it. I normally try to leave 2GB available for the OS, etc and then will monitor the system and consider changing this based on the performance of the system.

Its very hard to advise on perf tuning as so many of the values are really a depends kind of thing.
0
 

Author Comment

by:mirde
ID: 35003692
Its interesting, I was able to reproduce the same error when I do a file copy from say the C:\ to E:\ drive, which are both local to the OS (fiber channel SAN), the throughput is roughly 60mb/sec but during the process I can see Memory to take a spike.

Anyway to tweak that or limit? The file copy process (50gb @ 60mb/sec) chewed up a few GBs of RAM on its own. Never observed that type of effect before; but rarely do I move large files on SAN.

Just curious to see what others think of that.. per design in WIn2008R2?
0
 

Author Closing Comment

by:mirde
ID: 35130027
Limit the SQL Server memory usage has helped.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now