blocks|key|3364051|text|所有的数据库操作都必须通过写入磁盘来备份，拥有更多的RAM是有帮助的，但不是必须的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3364052|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

All DB operations will have to be backed up by writing to disk, having more RAM is helpful, but not essential.

blocks|key|1956162|text|只有工作集或公共数据或当前使用的数据需要放入缓冲区缓存(也称为数据缓存)。这也包括索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1956163|还有计划缓存，网络缓冲区%2B其他东西。微软在SQL+Server的内存管理上投入了大量的工作，而且效果很好，IMHO。|1956164|一般来说，更多的RAM会有所帮助，但这并不是必需的。|1956165|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

blocks|key|1244005|text|将整个数据库加载到RAM是不切实际的。如今，数据库的容量可以达到1TB。几乎没有人会买这么多的RAM。我认为，即使可用的RAM大小是数据库大小的十分之一，性能也是最优的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1244006|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|3364174|text|是的，当索引无法放入内存或执行全表扫描时。对不在内存中的数据执行聚合函数也需要多次(可能是随机的)磁盘读取。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3364175|对于一些基准：|3364176|3364177|查询时间将在很大程度上取决于受影响的数据当前是驻留在内存中还是需要访问磁盘。对于磁盘密集型操作，磁盘顺序和随机I/O性能的特征也很重要。|blockquote|3364178|3364179|3364180|http://www.sql-server-performance.com/articles/per/large_data_operations_p7.aspx|offset|length|3364181|因此，如果您的数据库大小>内存大小，则不要期望相同的性能。|3364182|编辑：+http://highscalability.com/充满了这样的例子：|style|BOLD|3364183|3364184|一旦数据库在内存中放不下，你就会碰壁。|3364185|3364186|3364187|http://highscalability.com/blog/2010/5/3/mocospace-architecture-3-billion-mobile-page-views-a-month.html|3364188|或者在这里：|3364189|即使DB大小仅比RAM大小大10%25，该测试也显示性能下降了2.6倍。http://www.mysqlperformanceblog.com/2010/04/08/fast-ssd-or-more-memory/|3364190|不过，请记住，这是针对热数据的，即您想要查询但不能缓存的数据。如果可以，您可以轻松地使用显著较少的内存。|3364191|entityMap|0|LINK|mutability|MUTABLE|url|1|http://highscalability.com/|2|3|http://www.mysqlperformanceblog.com/2010/04/08/fast-ssd-or-more-memory/^0|0|0|0|0|0|0|0|28|0|0|0|0|3|4|R|1|0|0|0|0|0|0|2W|2|0|0|Y|1Z|3|0|0^^$0|@$1|2|3|4|5|6|7|1I|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1J|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|1L|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1O|8|@]|9|@$L|1P|M|1Q|1|1R]]|A|$]]|$1|N|3|O|5|6|7|1S|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1T|8|@$L|1U|M|1V|R|S]]|9|@$L|1W|M|1X|1|1Y]]|A|$]]|$1|T|3|-4|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|U|3|V|5|G|7|20|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|21|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|22|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|23|8|@]|9|@$L|24|M|25|1|26]]|A|$]]|$1|10|3|11|5|6|7|27|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|28|8|@]|9|@$L|29|M|2A|1|2B]]|A|$]]|$1|14|3|15|5|6|7|2C|8|@]|9|@]|A|$]]|$1|16|3|-4|5|6|7|2D|8|@]|9|@]|A|$]]]|17|$18|$5|19|1A|1B|A|$1C|K]]|1D|$5|19|1A|1B|A|$1C|1E]]|1F|$5|19|1A|1B|A|$1C|Z]]|1G|$5|19|1A|1B|A|$1C|1H]]]]

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: 

<blockquote>
 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.
</blockquote>

<a href="http://www.sql-server-performance.com/articles/per/large_data_operations_p7.aspx" rel="nofollow noreferrer">http://www.sql-server-performance.com/articles/per/large_data_operations_p7.aspx</a>

There for, don't expect the same performance if your db size > ram size. 

Edit:
<a href="http://highscalability.com/" rel="nofollow noreferrer">http://highscalability.com/</a> is full of examples like:

<blockquote>
 Once the database doesn't fit in RAM you hit a wall.
</blockquote>

<a href="http://highscalability.com/blog/2010/5/3/mocospace-architecture-3-billion-mobile-page-views-a-month.html" rel="nofollow noreferrer">http://highscalability.com/blog/2010/5/3/mocospace-architecture-3-billion-mobile-page-views-a-month.html</a>

Or here: 

Even if the DB size is just 10% bigger than RAM size this test shows a 2.6 times drop in performance. 
<a href="http://www.mysqlperformanceblog.com/2010/04/08/fast-ssd-or-more-memory/" rel="nofollow noreferrer">http://www.mysqlperformanceblog.com/2010/04/08/fast-ssd-or-more-memory/</a>

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.

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?

Will performance of a SQL server degrade if the DB can't fit in the memory?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

如果数据库大于RAM，SQL server的性能是否会急剧下降？或者只有索引必须放入内存中？我知道这很复杂，但凭经验？

问如果数据库不能放入内存，SQL服务器的性能会降低吗？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如果数据库不能放入内存，SQL服务器的性能会降低吗？EN