 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008the effect of huge amounts of RAM? DB cache?
I have tons of short SPs being called all the time. The # of milliseconds on each matters and I'm at a point where pulling data from the hard drive slows down the operation more than anything. I've done tuning, etc. So if I get more RAM on a SQL Server 2005 than the size of my DB, will it effectively cache the whole database automatically in RAM? It will read the into pages into cache the first time they are needed. They are not removed unless SQL or the OS needs pages. However, this may already be happening effectively. What does the buffer manager:page life expectancy counter read during busy times?  SignatureJason Massie www: http://statisticsio.com rss: http://feeds.feedburner.com/statisticsio >I have tons of short SPs being called all the time. The # of milliseconds >on each matters and I'm at a point where pulling data from the hard drive >slows down the operation more than anything. I've done tuning, etc. So >if I get more RAM on a SQL Server 2005 than the size of my DB, will it >effectively cache the whole database automatically in RAM? Sorry but I'm not clear if your response means the whole database would be effectively cached in RAM or not. I haven't run that counter you mention but I know I don't have enough RAM for the whole database currently so theoretically I have no current way to avoid all the hard drive reads. > It will read the into pages into cache the first time they are needed. > They are not removed unless SQL or the OS needs pages. However, this may [quoted text clipped - 5 lines] >>So if I get more RAM on a SQL Server 2005 than the size of my DB, will it >>effectively cache the whole database automatically in RAM? > Sorry but I'm not clear if your response means the whole database would be effectively cached in > RAM or not. Basically: yes. But the answer is never that simple. Pages from tables and indexes isn't the only thing that uses memory (from the buffer pool). Other things include procedure plans, connections, locks etc. So just because you give SQL Server the same amount of memory as your big db size doesn't mean that every thing will fit in cache. SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > Sorry but I'm not clear if your response means the whole database would be effectively cached in > RAM or not. [quoted text clipped - 9 lines] >>>anything. I've done tuning, etc. So if I get more RAM on a SQL Server 2005 than the size of my >>>DB, will it effectively cache the whole database automatically in RAM? > Sorry but I'm not clear if your response means the whole database would be > effectively cached in RAM or not. > > I haven't run that counter you mention but I know I don't have enough RAM > for the whole database currently so theoretically I have no current way to > avoid all the hard drive reads. Basically, the more memory you have the more SQL Server will cache. However, it won't cache the data until the first time it's needed. So if you have the right version of SQL Server, it should be able to cache your entire DB. However, in most cases, certainly not all and possibly not yours, caching the entire DB isn't necessary. In most cases databases tend to work along the 80/20 rule or some variation of, i.e. 80% of all work is done on 20% of the data. So in most cases, caching the entire db is not necessary. I'm curious exactly what the current performance is like and how much you need to improve it. There may be other options also. SignatureGreg Moore SQL Server DBA Consulting Remote and Onsite available! Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html >> Sorry but I'm not clear if your response means the whole database would >> be effectively cached in RAM or not. [quoted text clipped - 15 lines] > I'm curious exactly what the current performance is like and how much you > need to improve it. There may be other options also. You're right that not all the database is the cause of my problem. My largest table is, though, and it is involved in almost all lookups. Is there a way to force specific tables into memory? > You're right that not all the database is the cause of my problem. My > largest table is, though, and it is involved in almost all lookups. Is > there a way to force specific tables into memory? SQL Server will optimize this naturally and once data is loaded into memory will basically do its best to keep it there. i.e. you can't force it to load with any specific command (though I suppose maybe with a "select * from LOOKUP" might, but as you use it, it will be cached.) But I'd HIGHLY recommend you pick up some books such as Kalen Delaney's book on Inside SQL server since there may be better ways to optimize the DB then the brute force approach (and it'll help you know which of the MANY metrics SQL Server provides to focus on.) SignatureGreg Moore SQL Server DBA Consulting Remote and Onsite available! Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html Take a look at DBCC PINTABLE - as you are really concerned about one particular table, this looks like this is a case where it could legitimately be used: http://msdn2.microsoft.com/en-us/library/aa258284(SQL.80).aspx Rgds, Paul Ibison (www.replicationanswers.com) >Take a look at DBCC PINTABLE - as you are really concerned about one >particular table, this looks like this is a case where it could legitimately >be used: http://msdn2.microsoft.com/en-us/library/aa258284(SQL.80).aspx >Rgds, >Paul Ibison >(www.replicationanswers.com) No, that is not an option. The original post mentioned SQL Server 2005. Here is an excerpt from the 2005 Books On Line entry for DBCC PINTABLE. "Important: This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server. " Roy Harvey Beacon Falls, CT Fair point Roy - knew of the warnings (there are similar but less urgent ones in MSDN) but missed the SQL 2005 part. Cheers, Paul Ibison Data will still need to be transferred to/from disk even with sufficient memory to cache the entire database. For an intensive random I/O pattern, be sure to spread the workload evenly over as many spindles as possible and make sure that the log file is on a separate drive. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ >I have tons of short SPs being called all the time. The # of milliseconds >on each matters and I'm at a point where pulling data from the hard drive >slows down the operation more than anything. I've done tuning, etc. So >if I get more RAM on a SQL Server 2005 than the size of my DB, will it >effectively cache the whole database automatically in RAM? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.