How to adjust memory usage by using configuration options in SQL Server

When I was at Tech Insights 2009, a few delegates asked me on ways that they could adjust memory usage by using configuration options in SQL Server. They were complaining that SQL Server uses a lot of memory. 🙂 In fact, a lot of people posted this question to me when I delivered trainings and talks. Well. I think is worth I blog on this.
 
When you start SQL Server, SQL Server memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and Performance Monitor may show that the physical memory available on the computer is steadily decreasing until the available memory is between 4 to 10 MB. This behavior alone does not indicate a memory leak. This behavior is normal and is an intended behavior of the SQL Server buffer pool. By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on the physical memory load reported by the operating system. As long as enough memory is available to prevent paging (between 4 – 10 MB), the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.
 
You can establish upper and lower limits to the amount of memory (buffer pool) used by the SQL Server database engine with the min server memory and max server memory configuration options.
 
An Example:
The following example sets the max server memory option to 4 GB:
 
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘max server memory’, 4096;
GO
RECONFIGURE;
GO
 
Note:
 
There are two principal methods for setting the SQL Server memory options manually:
 
. In the first method, set min server memory and max server memory to the same value. This value corresponds to the fixed amount of memory to allocate to the SQL Server buffer pool after the value is reached.
. In the second method, set min server memory and max server memory to span a range of memory values. This method is useful where system or database administrators want to configure an instance of SQL Server in conjunction with the memory requirements of other applications that run on the same computer.
Advertisements

myVSTS: Tech Insights 2009

We are 2 weeks away from Tech Insights 2009. Just wondering whether any myVSTS members have registered for the event. If you have yet to register, you’ll still have the chance to do so. There is a special promotion for myVSTS members. You can get the promotion code from http://www.facebook.com/event.php?eid=116827408124.
 
So what is Tech Insights 2009?
 
The Event
 
Thursday September 17th, 2009 – Friday September 18th, 2009
From 09:00am – 18:00pm
Registration Fees: RM80 for non-members of supporting organizations, RM30 for supporting organizations and communities (inclusive of lunch and freebies). For your information, myVSTS is categorized under "communities".
 
Location:
Universiti Sains Malaysia, Pulau Pinang
 
Tech Insights 2009 sets the pedestal as an unwavering icon in the IT and technology industry in Malaysia and the region. This is an unique event, a one-of-a kind and a first in the island of Penang technical community event led and powered by the Malaysia Microsoft Most Valuable Professional (MVP) as well as fellow MVPs from USA and other countries who are highly respectable leaders in their own technical communities. The event is also a dedicated platform for like minded folks in the IT and technology industry to come together and help to drive community awareness and event and as a social media portal for all technology inspiring lovers to network, collaborate and build personal connections with Microsoft experts and peers.
 
Tech Insights 2009 event’s agenda has been specifically tailored made to serve the technical needs and knowledge of not just the public, but also technology inspiring lovers. This 2-day intensive conference will equip you with the up-to-date and soon-to-be-released Microsoft technologies delivered by our diverse group of experts on these topics, helping to make life easier for you as well as everyone both at work and at home. Topics in the event will focus on best practices of existing products or lap around on pre-release products to help you decide technology roadmaps and gain valuable insights into Microsoft technology.
 
Tech Insights 2009 Penang is supported by Universiti Sains Malaysia (USM), InvestPenang, Penang Development Corporation (PDC), Software Consortium of Penang (SCoPe Penang) and MCA ICT Resource Centre Penang (MIRC Penang).
 
Join us at this 2-day conference and learn from our panel of Microsoft experts and peers on how you can get the most out from the latest Microsoft technologies. Seize this rare opportunity to learn how you can gain and maintain a competitive edge in the technology arena!
 
For more information, please visit http://www.techinsights.com.my

Tech Insights 2009 Geek @Work Contest

As part of the organizer of Tech Insights 2009, I would like to invite all my friends to join the following contest.
 
Geek @Work
 
Running Duration
31st August – September 15th
 
How to Play
1. From now until September 15th, do this:
Take a photo of yourself or get a friend to help you. The photo must be following the theme of “GEEK AT WORK”. Post this photo into the Tech Insights Facebook Group (http://www.facebook.com/group.php?gid=102576994382&ref=ss) and TAG yourself on to the picture (only 1 tag per photo). 
2. The winner will be the photo with the most amount of ‘Likes’. The prizes will be given to the person tagged.
3. The tagged person must remained an active Facebook user throught out the contest period.
4. The organizer will try to contact the person via Facebook private message.
 
WINNING TIPS
1. Be creative! Use items at work that may represent IT, Tech Insights or anything related! Idea: a whacky photo of yours in server room, or you doing something fun on your laptop.
2. Spread the word! The more people that knows about your photo.. the more chances of ‘Likes’ you will receive.
 
RULES AND REGULATIONS
1. Photos that are not self-tagged will be disqualified.
2. No nudity in the photos are allowed and will be automatically disqualified and banned from the facebook group.
 
Winner will be informed on the 16th September and the prize will be presented during the keynote of TechInsights Penang on September 17th!