需要多少内存才能保留表缓存? (SQL Server 2008)


5

我运行冗长且复杂的查询,主要使用一个大表 - 8GB,40M行。 AFAIK,每个查询都使用所有/大部分行。我在活动监视器中看到很多IO - 第一个查询和后续的每个查询。该服务器目前使用6.5GB的内存,我想升级。问题是,需要多少内存来避免所有这些磁盘读取?它是否在大小的桌子或更大的球场?

这是SET STATISTICS IO输出。 BigTable是我所问的一个,SmallTable与BigTable有一对多的关系。 #entrance保存查询的输出(数百行输出)。

Table 'SmallTable'. Scan count 249005, logical reads 2829948, physical reads 2605, read-ahead reads 10395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'BigTable'. Scan count 194004, logical reads 13482115, physical reads 33841, read-ahead reads 1181136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table '#entrance__000000000023'. Scan count 0, logical reads 1568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
+2

这是否服务器/数据库别的什么也不做比对数据的8GB的1台运行查询其他?我也在头脑里摸索了一下,但这个问题很复杂。有没有DDL的机会或对这种复杂性的本质有一点洞察力? 14 9月. 112011-09-14 22:20:40

  0

只是一个基本的检查,你使用的是什么版本的SQL服务器?如果它不是企业,那么你将无法将所有当前内存用于SQL Server。 16 9月. 112011-09-16 15:30:00

  0

我最终升级了服务器的RAM并做了测试。我在下面发布了结果作为答案。 09 2月. 132013-02-09 08:50:11

3

查询的性质是什么?只有选择?或DUI的混合(删除,更新,插入)?你的恢复模式是什么?你使用默认的事务隔离级别吗?

如果你想要把内存中的所有这些网页,那么你将需要得到更多的内存(但你已经知道了)。你想知道'多少',但事实是,根据你的查询的性质,即使有足够的内存来保存整个表,你仍然可以看到一些I/O问题。

荷兰国际集团考虑上述所有的...是啊,看球表的大小在磁盘上的内存量。如果您发现您之后仍然有磁盘I/O,请不要感到惊讶。

  0

我升级了服务器RAM,并且“将磁盘上磁盘的大小作为内存量”是绝对正确的。请参阅下面的答案。 08 2月. 132013-02-08 11:59:48


8

这是一个两部分的答案。

,第一部分是购买尽可能多的RAM,你能买得起(只要它会融入服务器)。

第二部分是你会看到数据的读取。诀窍是看看这些读取是物理的还是逻辑的。物理读取正在进行到磁盘。逻辑读取是从内存中的缓冲池中读取的。你可以通过在SQL脚本的顶部放置“SET STATISTICS IO ON”然后在SSMS中运行它来看到这一点。在消息选项卡上,您将获得正在运行的每个查询的特定IO信息,具体而言,您将获得在表格的基础上发生的逻辑和物理操作(在您的情况下读取)的数量。如果物理读取数字相当高(这些数字基本上是读取的数量,然后除以1024得到Megs中的数字),然后您需要更多的RAM。如果逻辑读取数高,那么你实际上并没有打算到磁盘,您的查询可能需要调整,索引创建等

(注:对于这个答案的目的,我假设只有SELECT语句。INSERT/UPDATE/DELETE操作有点复杂,但相同的基本应用。)

  0

我会尝试:SET STATISTICS IO ON并回报。更多信息:查询是100%选择。每个查询需要几分钟时间。服务器还运行了查询并将结果转储到Excel中的小应用程序,但我怀疑这会影响服务器性能。 15 9月. 112011-09-15 05:00:37

  0

如果您在阅读输出时需要一些帮助,请随时发布执行计划和SET STATISTICS IO输出。 15 9月. 112011-09-15 05:05:59


1

不知道,更多的RAM是要帮助这里。快速计算表明您的查询正在处理125GB数据区域中的某个位置,对于包含8GB的表格而言,这一点相当令人印象深刻。

(2829948 + 13482115 + 1568)×一百○四万八千五百七十六分之八= 125GB

物理的270MB读取将占像不亚于内存开销的运行慢的一部分,但没有。

你会得到重新工作的查询更好的结果比通过增加额外的内存。在一个新的问题中发布DDL /查询/执行计划,并且某人一定能够提供帮助。


3

我总是迟到了派对。 :)这个查询很可能会被调整到很好的效果。但是,对于需要多少内存才能缓存8 GB的表格......一个初步问题是SQL Server为服务器报告多少内存节点?每个内存节点(逻辑上等同于物理NUMA节点)具有独立管理的缓冲池缓存。在该节点中的处理器上运行的任务会将块插入该节点中的缓冲池中。

因此,如果有可能会读取整个8 GB表的任务将在单个NUMA节点上运行,那么该节点的缓冲池必须能够包含8 GB的表。因此,请从每个NUMA节点的8 GB最大服务器内存开始。然后让我们开始增加它。查询内存最多可以使用70%的缓冲池,只留下30%的数据库块内容。因此,8 GB表需要不超过服务器单个NUMA节点上最大服务器内存的30%。 26 2/3 GB用于最大服务器内存。每NUMA节点。如果它的SQL Server 2012,SQL Server 2008 R2和以前使用多页分配器(因此超出了最大服务器内存范围)的内容现在在最大服务器内存范围内并需要考虑。它必须以任何方式包含在整个服务器内存中,无论它是否是SQL Server最大服务器内存限制的一部分。

哦是的......任何使用临时表或其他临时数据库的用法都应该包含在最大服务器内存中,以免让表从缓存中被逐出。

为Windows操作系统添加足够的内存(至少4 GB)。为SQL Server工作线程,SQL Server核心进程,SQL代理作业以及任何其他应用程序内存需求添加足够的内容。

在单个NUMA节点服务器上,我会放置大约36 GB RAM,最大服务器内存至少27 GB,以可靠地缓存8 GB表。如果有多个NUMA节点,则每个NUMA节点最多可增加26个2/3 GB的最大服务器内存,并且其他工作线程将附加NUMA节点中的其他内核。

虽然这看起来可能过分,但部分原因在于SQL Server如何处理NUMA。但你可能很幸运!如果一个懒惰的写入器就足够了,可以使用启动跟踪标志8015来忽略NUMA并管理单个缓冲池。这样,无论服务器上有多少个NUMA节点,大约36 - 40 GB都足以保持8 GB的表内存驻留。在下面的博客文章中多讨论一下。如果您决定使用跟踪标志8015,我建议评估跟踪标志8048,以防止升级的螺旋锁争用和CMEMTHREAD等待与内存分配相关联。 http://sqlsasquatch.blogspot.com/2013/02/sql-server-cache-database-working-set.html


2

我将服务器RAM从8GB升级到64GB并做了测试。表大小现在为16.05GB,索引空间为187MB(1个聚簇索引和1个唯一非聚簇索引)。我在运行一个查询之前和之后检查了总的服务器RAM使用情况,该查询对表中的每个137个字段进行平均。总内存从4.22GB上升到20.4GB = 16.18GB,这是表格大小的100.8%。 SQLRockstar在预测RAM使用量是表的大小时是绝对正确的。

更多信息:聚集索引的

  • 页面丰满98.1%
  • 进一步查询该表没有造成磁盘IO。
  • 的SQL Server 2008 R2开发
  • 执行计划显示,聚簇索引使用