Will the performance of a SQL server drastically degrade if the database is bigger than the RAM? Or does only the index have to fit in the memory? I know this is complex, but as a rule of thumb?
A:
All DB operations will have to be backed up by writing to disk, having more RAM is helpful, but not essential.
ck
2010-05-21 07:37:55
but how about read operations? if the data ain't in the memory, a read access to disc have to be made, which is like 1000 times slower than reading from RAM?
Carl Hörberg
2010-05-21 08:10:00
+1
A:
Only the working set or common data or currently used data needs to fit into the buffer cache (aka data cache). This includes indexes too.
There is also the plan cache, network buffers + other stuff too. MS have put a lot of work into memory management on SQL Server and it's works well, IMHO.
Generally, more RAM will help but it's not essential.
gbn
2010-05-21 08:03:35
A:
Loading the whole database into RAM is not practical. Database can be upto a Terabytes these days. There is little chance that anyone would buy so much RAM. I think performance will be optimal even if the size of the RAM available is one tenth of the size of the database.
nitroxn
2010-05-21 08:12:58
there for we must switch to NoSQL alternatives? How can a db be efficent if it have to read from disc?
Carl Hörberg
2010-05-21 08:14:31
I think performance will be optimal even if the size of the RAM available is one tenth of the size of the database.
nitroxn
2010-05-21 08:15:10
@Carl Hörberg: what are you on about? You'll almost never have all data in memory or need it there either.
gbn
2010-05-21 09:32:05
really? if i attach multi GB DB to a idle MS SQL server it will use all available memory, what does the server load it with if not the db data?
Carl Hörberg
2010-05-21 11:47:54
ok, so the performance will be the same as long as the indexes fits in the buffer cache?
Carl Hörberg
2010-05-21 13:59:34
"Query time will depend significantly on whether the affected data currently resides in memory or disk access is required. For disk intensive operations, the characteristics of the disk sequential and random I/O performance are also important." http://www.sql-server-performance.com/articles/per/large_data_operations_p7.aspx
Carl Hörberg
2010-05-21 15:28:04
found out after more googling.. (and now i can't delete the question)
Carl Hörberg
2010-05-22 18:27:25
A:
Yes, when indexes cant fit in the memory or when doing full table scans. Doing aggregate functions over data not in memory will also require many (and maybe random) disc reads.
For some benchmarks:
Query time will depend significantly on whether the affected data currently resides in memory or disk access is required. For disk intensive operations, the characteristics of the disk sequential and random I/O performance are also important.
http://www.sql-server-performance.com/articles/per/large_data_operations_p7.aspx
There for, don't expect the same performance if your db size > ram size.
Edit: http://highscalability.com/ is full of examples like:
Once the database doesn't fit in RAM you hit a wall.
Or here:
Even if the DB size is just 10% bigger than RAM size this test shows a 2.6 times drop in performance. http://www.mysqlperformanceblog.com/2010/04/08/fast-ssd-or-more-memory/
Although, remember that this is for hot data, data that you want to query over and don't can cache. If you can, you can easily live with significant less memory.
Carl Hörberg
2010-05-23 09:56:20