February 21, 2011 at 12:36 pm
Hey guys, I am interested in upgrading my current server's memory to 16GB and trying to load the entire DB into it. The database is currently about 16GB in size, so I probably need to shrink it to about 12GB or a little smaller to fit into entirely into memory and still have room for the OS, SQL Server, and joins or anything else that may take up extra memory. However, this SS2005 instance has 2 other databases associated with it, a Adhoc and Archive db that are about 40GB in size. I was wondering if there is a way to tell SQL Server to just load my main DB into memory? Thanks.
February 21, 2011 at 2:16 pm
There really is no way to tell SQL Server to load a database into memory. SQL Server loads data into the buffer cache when it is accessed the first time - and the more frequently that data is accessed the longer it will stay in memory.
Are you having a performance issue you are trying to resolve?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 21, 2011 at 2:19 pm
coder_t2 (2/21/2011)
Hey guys, I am interested in upgrading my current server's memory to 16GB and trying to load the entire DB into it. The database is currently about 16GB in size, so I probably need to shrink it to about 12GB or a little smaller to fit into entirely into memory and still have room for the OS, SQL Server, and joins or anything else that may take up extra memory. However, this SS2005 instance has 2 other databases associated with it, a Adhoc and Archive db that are about 40GB in size. I was wondering if there is a way to tell SQL Server to just load my main DB into memory? Thanks.
If Adhoc and Archive aren't used, they won't be loaded to memory. If they are used, you'll need some memory so SQL can work with them.
Are you trying to deal with an actual issue/perceived problem here, or is this a just for fun exercise?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 21, 2011 at 2:21 pm
The buffer pool is managed separately for each instance, so if you have separate instances (check your licensing before you decide on this), you can assign xx memory to each instance and somewhat control things.
However you can't necessarily load the entire database, nor would you want to. You want to load the stuff you need for the workload, which might not mean the entire db.
Also, don't forget that you'll still be writing to disk to ensure changes get hardended to the log and also written to disk in case the server dies.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 21, 2011 at 3:21 pm
And also note that memory is required for other things than data pages. Plan cache, lock memory, thread stacks, token store, backup buffers, query grants, temp tables, work tables, etc, etc
In the vast majority of cases you should just let SQL manage it's memory, it probably knows better than you do what does and does not need to be in memory.
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
February 22, 2011 at 12:21 pm
Ok thanks guys. After doing some more research and reading the responses in this thread, I misunderstood the idea of "loading a database" in memory. I thought if SQL Server saw that there was more memory than the size of the database it would load the entire database into memory. From what I understand now, SQL Server only loads things into memory when it is used. At times, my server is hitting memory and I/O bottlenecks, and I was thinking about managing the database to fit entirely in memory to overcome those bottlenecks. Versus upgrading both memory and I/O. It actually seemed like it may have been a more cost effective solution. I have already gone and done a lot of performance tuning on my queries, but with the added load my server has received, and some hardware improvements will help a lot.
The adhoc and archive databases are used, but their priority is much lower and are they used much less than my main DB. So if they were given just a small portion of memory to work with and ran off of the hard drive, that would have been fine.
February 23, 2011 at 11:31 pm
coder_t2 (2/21/2011)
However, this SS2005 instance has 2 other databases associated with it, a Adhoc and Archive db that are about 40GB in size. I was wondering if there is a way to tell SQL Server to just load my main DB into memory? Thanks.
When you upgrade to SQL Server 2008, take a look at the Resource Governor feature.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 28, 2011 at 3:54 pm
hello,
if you are intending to upgrade the physical server to 16GB i assume it currently has 8GB. you mention this instance has databases, 2 which are low priority and low usage, one priority with high usage. you dont mention if this is 64bit OS or SQL so make sure that you have already configured Windows and SQL if using 32bit
a couple of questions:
1. is this a dedicated SQL server or are there other applications running on this server?
2. and is this the only instance of SQL installed?? remember that only data that is to be read is loaded into memory but if you are seeing memory and i/o bottlenecks you need to check if the i/o are read and write or write delays only
more memory will definately help with the read problems and is cheap and quick....the write i/o problems will most likely involve hardware changes. if you are running other applications on this server consider moving them off to something else if possible. if you are running multiple SQL instances on this server you will need to assess their individual memory usage and sum them all up to give a whole server number....and make sure to limit each instance to a set min/max limit to avoid them competing
take a look at the wait statistics for the server (many posts in ssc for that) and post them if you are unsure of the exact issues
regards
lilywhites
March 1, 2011 at 7:34 am
coder_t2 (2/22/2011)
At times, my server is hitting memory and I/O bottlenecks, and I was thinking about managing the database to fit entirely in memory to overcome those bottlenecks.
That usually turns out to be a code problem. You need to find the code that is causing those problems and fix it.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply