none
查询内存到底是怎么分配的? RRS feed

  • 问题

  • 最近发现一个查询内存分配了 9G, 百思不解,看看大家有什么高见

    下面是重现的方法

    -- ======================================================
    -- 1. 创建测试的表
    -- ======================================================
    USE tempdb;
    GO
    IF OBJECT_ID(N'dbo.__tb_test', 'U') IS NOT NULL
    	DROP TABLE dbo.__tb_test;
    CREATE TABLE dbo.__tb_test
    (
    	id int IDENTITY PRIMARY KEY,
    	group_id int NOT NULL,
    	date datetime NOT NULL,
    	value nvarchar(max) NULL
    );
    
    -- 200 万的数据,每个 group_id 大约 1 万记录
    DECLARE @times int;
    SET @times = 0;
    WHILE @times < 200
    BEGIN;
    	INSERT dbo.__tb_test( group_id, date )
    	SELECT TOP(10000)
    		ABS(CHECKSUM(NEWID())) % 200,
    		DATEADD(day, CHECKSUM(NEWID()) % (3 * 360), GETDATE())
    	FROM sys.all_columns A, sys.all_columns B
    	;
    	SET @times = @times + 1;
    END;
    
    -- 查看表的大小及数据分布
    EXEC sp_spaceused __tb_test;	-- 大约 50MB 的数据
    
    SELECT TOP(50)
    	group_id, COUNT(*)
    FROM dbo.__tb_test
    GROUP BY group_id
    ORDER BY COUNT(*) DESC
    GO
    
    -- ======================================================
    -- 2. 在程序中查询测试(这里用 Powershell )
    -- ======================================================
    # 连接字符串
    $ConnectionString = "Data Source=127.0.0.1;Initial Catalog=tempdb;Integrated Security=SSPI"
    
    # 打开连接
    $SqlCnnection = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
    $SqlCnnection.Open()
    
    # 获取 session id
    $SqlCommand = New-Object System.Data.SQLClient.SQLCommand
    $SqlCommand.Connection = $SqlCnnection
    $SqlCommand.CommandText = "SELECT @@SPID"
    $SessionId = $SqlCommand.ExecuteScalar()
    
    # 获取查询的 group_id
    $SqlCommand.CommandText = "SELECT TOP(1) group_id FROM dbo.__tb_test GROUP BY group_id HAVING COUNT(*) > 5000 ORDER BY COUNT(*)"
    $GroupId = $SqlCommand.ExecuteScalar()
    
    # 查询数据
    $SqlCommand.CommandText = "SELECT * FROM dbo.__tb_test WHERE group_id = $GroupId ORDER BY date"
    ## $SqlCommand.CommandText = "SELECT * FROM dbo.__tb_test WHERE id IN ( SELECT id FROM dbo.__tb_test WHERE group_id = $GroupId ) ORDER BY date"
    $SqlReader = $SqlCommand.ExecuteReader()
    
    # 获取查询 session 的内存使用情况
    $SqlCnnectionSession = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
    $SqlCnnectionSession.Open()
    $SqlCommandSession = New-Object System.Data.SQLClient.SQLCommand
    $SqlCommandSession.Connection = $SqlCnnectionSession
    $sql = "SELECT 请求的内存总量GB = CONVERT(decimal(19, 2), requested_memory_kb / 1024. / 1024),"
    $sql = $sql + "实际授予的内存总量GB = CONVERT(decimal(19, 2), granted_memory_kb / 1024. / 1024),"
    $sql = $sql + "运行查询所需的最小内存GB = CONVERT(decimal(19, 2), required_memory_kb / 1024. / 1024),"
    $sql = $sql + "此刻使用的物理内存GB = CONVERT(decimal(19, 2), used_memory_kb / 1024. / 1024),"
    $sql = $sql + "到此刻为止所用的最大物理内存GB = CONVERT(decimal(19, 2), max_used_memory_kb / 1024. / 1024)"
    $sql = $sql + " FROM sys.dm_exec_query_memory_grants WITH(NOLOCK)"
    $sql = $sql + " WHERE session_id = $SessionId"
    $SqlCommandSession.CommandText = $sql 
    $SqlReaderSession = $SqlCommandSession.ExecuteReader()
    $DataTableSession = New-Object System.Data.DataTable
    $DataTableSession.Load($SqlReaderSession)
    $SqlReaderSession.Close()
    $DataTableSession
    $SqlCnnectionSession.Close()
    
    # 关闭连接
    $SqlReader.Close()
    $SqlCnnection.Close()
    


    2014年10月14日 8:06

答案

  • 邹建, 你好

    我去在16GB和8GB的环境下重现了一下的你的问题:

    16G-1.12G

    8G-0.66G

    对于24G以下的服务器,线性关系不明显。

    关于内存分发的机制,我找到这篇文章:

    http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

    以下是分发的步骤(从上面的文章你可以得到更多信息)

    Memory grant process
    In the previous section, we discussed how parallelism affects the query memory requirement. In this section, we will discuss how SQL server takes server memory and number of concurrent queries into considerations. The server needs to consider such dynamic factors to avoid committing memory beyond its physical limit. This is done in 2 distinct steps. First, the server calculates how much memory to grant for given query. Then it uses the internal facility called Resource Semaphore to reserve actual memory, or throttle if too many queries ask for memory. First, the following steps show how the request size is determined.

    • The server decides parallelism (DOP) based on the plan and the server state.
    • The server checks if memory grant is needed not. If not needed, the server can start the query immediately. For example, a simple serial query without “ORDER BY” or “GROUP BY” may not need memory grant.
    • The server calculates the memory limit for one query. By default, this is 25% (20% on 32bit SQL 2005) of total query memory (which is set by memory broker as about 90% of server memory). This per-query limit helps to prevent one query from dominating the whole server. This percentage is configurable on SQL 2008.
    • The server calculates the ideal query memory as required*DOP + additional (+ exchange on SQL 2008). This is amount a query would like to have, based on its cardinality estimate.
    • The server checks if the ideal memory exceeds the per-query limit. If it does, then the server reduces the additional memory until the total fits within the limit. This revised size is called requested memory.The server asks Resource Semaphore to grant the requested memory.

    我测试了一下max dop的影响,区别不明显,应该是实际使用的DOP是一样的。

    减少表的大小会减少内存开销,但是减少满足条件的记录,内存也会相应减小。

    对于两条query的区别,我看过之后,目前两个语句的执行计划不一样,他们使用的内存不一致也是正常的。 而使用内存较多的$SqlCommand.CommandText = "SELECT * FROM dbo.__tb_test WHERE group_id = $GroupId ORDER BY date"实际上运行时间更短。 执行计划选择索引是基于统计信息的如果及时更新统计信息,在估计执行计划时也会较为合理。



    2014年10月16日 4:35
  • 邹建,

    我的意思是说如果在内存压力很大时,可用内存也较小 (例如8GB),可以看看QO给予的内存。因为QO分配内存的设计本身是个比较复杂的问题,我建议您可以给我们开一个咨询类的案例,这样我们可以深入帮您去研究回答这个问题。

    咨询类的案例,我从来没有开过,不知道要怎么开,也不知道要具备什么样的条件才能开呢?

    帮你问了一下,一般是先要签合同的,然后打电话给TAM或者CSR让他们帮你开case,如果是advisory会有RDC team的人帮你看的。(不清楚大陆是不是也这样)另外你可以试打一下这个打电话咨询一下8008203800。

    SQLOS 跟QO的内存需求计算模式公开的信息比较少,但如果能看到source code的话,这个问题估计就是 请求和授予跟机器total memory的一个数学表达式。

    另外可以看看QO team的人参与写的paper.

    http://academic.research.microsoft.com/Publication/2422074/managing-query-compilation-memory-consumption-to-improve-dbms-throughput



    Please click the Mark as Answer button if a post solves your problem!



    2014年10月29日 14:09
  • SQL Server的优化器的相关特性引起的问题:相关分析及解决方式在这里:http://www.cnblogs.com/shanksgao/p/4171265.html

    2014年12月18日 4:17

全部回复

  • 问题:

    1. 测试表 200 万, 表大约 50MB 的数据

    2. 在 64G 的服务器上, 请求和授予的内存是 9GB, 32G内存的服务器上是 4.5GB, 24G内存的服务器上大约 2.8G, 16G的服务器上大约是 1.09G, 比较有规律, 但内存的分配确实太大了点, 不知道是按照什么原则去估算内存需求的

    3. 这个内存的开销评估似乎是按照表的大小的,因为减少表的大小,但不减少满足条件的记录,内存的开销会降低,难道内存分配不是主要跟满足条件的记录相关的么?

    4. 如果不排序,则不会有这么大的内存需求

    5. 如果先把满足条件的 id 取出来,再根据这个 id 查询排序,则内存开销也非常小, 也就是下面这两种方法,第1个是大内存,第2个方法是极小内存开销,难道查询要用这么变态?

    6. group_id 是加索引这个就不提了,这个上面加索引,查询不一定会用这个索引的,完全一样的数据(Replication下去的),有的服务器上会用索引,有的不会用,用索引的话,跟查询2差不多

    # 查询数据
    $SqlCommand
    .CommandText = "SELECT * FROM dbo.__tb_test WHERE group_id = $GroupId ORDER BY date"
    ## $SqlCommand.CommandText = "SELECT * FROM dbo.__tb_test WHERE id IN ( SELECT id FROM dbo.__tb_test WHERE group_id = $GroupId ) ORDER BY date"

    2014年10月14日 8:15
  • 邹建, 你好

    我去在16GB和8GB的环境下重现了一下的你的问题:

    16G-1.12G

    8G-0.66G

    对于24G以下的服务器,线性关系不明显。

    关于内存分发的机制,我找到这篇文章:

    http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

    以下是分发的步骤(从上面的文章你可以得到更多信息)

    Memory grant process
    In the previous section, we discussed how parallelism affects the query memory requirement. In this section, we will discuss how SQL server takes server memory and number of concurrent queries into considerations. The server needs to consider such dynamic factors to avoid committing memory beyond its physical limit. This is done in 2 distinct steps. First, the server calculates how much memory to grant for given query. Then it uses the internal facility called Resource Semaphore to reserve actual memory, or throttle if too many queries ask for memory. First, the following steps show how the request size is determined.

    • The server decides parallelism (DOP) based on the plan and the server state.
    • The server checks if memory grant is needed not. If not needed, the server can start the query immediately. For example, a simple serial query without “ORDER BY” or “GROUP BY” may not need memory grant.
    • The server calculates the memory limit for one query. By default, this is 25% (20% on 32bit SQL 2005) of total query memory (which is set by memory broker as about 90% of server memory). This per-query limit helps to prevent one query from dominating the whole server. This percentage is configurable on SQL 2008.
    • The server calculates the ideal query memory as required*DOP + additional (+ exchange on SQL 2008). This is amount a query would like to have, based on its cardinality estimate.
    • The server checks if the ideal memory exceeds the per-query limit. If it does, then the server reduces the additional memory until the total fits within the limit. This revised size is called requested memory.The server asks Resource Semaphore to grant the requested memory.

    我测试了一下max dop的影响,区别不明显,应该是实际使用的DOP是一样的。

    减少表的大小会减少内存开销,但是减少满足条件的记录,内存也会相应减小。

    对于两条query的区别,我看过之后,目前两个语句的执行计划不一样,他们使用的内存不一致也是正常的。 而使用内存较多的$SqlCommand.CommandText = "SELECT * FROM dbo.__tb_test WHERE group_id = $GroupId ORDER BY date"实际上运行时间更短。 执行计划选择索引是基于统计信息的如果及时更新统计信息,在估计执行计划时也会较为合理。



    2014年10月16日 4:35
  • 统计信息周建大侠应该一早已经考虑到
    2014年10月16日 9:50
  • 1 优化器使用内存的贪婪型的,你的数据量虽说预估是50M,但里面有不确nvarchar(max)类型.会导致更多使用内存

    2 内存消耗是依据bufferpool的大小来的,单条语句一般最大消耗 3/4BP*25%

    3 贪婪型,先针对你最初对象

    4 sort操作时内存消耗型运算符,至少消耗预估数据大小的200%

    5 没有相应的内存消耗符

    6 选择性

    2014年10月17日 8:58
  • nvarchar(max) 的内存开销评估标准是什么?

    另外, 不 sourt 就没有问题, sort 的开销就在得这么离谱? sourt 应该是在筛选之后吧? 但分布开销是跟总数据量有关,这个说不过去吧?

    50MB 的数据,评估出 9 GG的内存开销,不管怎么算,这个算法也是挺失败的吧?

    2014年10月17日 10:32
  • 我想这里应该聚焦在sort的算法上,50M的数据,装载至少需要50M(非溢出),但还要排序呢啊,非溢出情况使用的快速排序的算法,算法复杂度应用内存如果是贪婪的使用,可想而知了.如果你的内存是128G,优化器在评估时申请内存还会涨.

    申请和使用内存是两个阶段的事儿,申请了9G,结果只使用了10M也不是不可能啊.这就是贪婪.所以设计需谨慎.

    顺便说下,当全部装载到内存中使用的快速排序.当溢出到tempdb中会使用归并排序.

    2014年10月20日 1:42
  • 这个应该不纯粹是排序的问题, 如果把表中满足条件的数据都删除, 那么内存开销没有这么大

    评估内存开销为啥要把这满足条件的也算进去呢?

    而且,为啥为把不需要排序的列也考虑进去呢?比如下面这个查询,他的内存开销还是那么大?这个并没有对结果做排序,只是在 indate 上算一个 row_number, 这个没有招惹到 nvarchar(max) 的数据吧?

    select * from (select rid=row_number() over(order by date), * from __tb_test where group_id =  $GroupId )aa

    2014年10月20日 2:46
  • 排序时对结果集,而不是针对特定列.sort是作为内存消耗符基本会占据整体查询的绝大多部分内存消耗.

    在排序时也只是排序你满足条件的,但就是这个满足条件也会造成非常大的评估.

    执行时,优化器按照评估向BP要内存,就像上面说的,贪婪.要了10G内存,而实际只是使用了10M

    你最后的开窗,本身就在date栏位排序了,怎能说没有排序啊.

    2014年10月20日 3:15
  • 排序时对结果集,而不是针对特定列.sort是作为内存消耗符基本会占据整体查询的绝大多部分内存消耗.

    在排序时也只是排序你满足条件的,但就是这个满足条件也会造成非常大的评估.

    执行时,优化器按照评估向BP要内存,就像上面说的,贪婪.要了10G内存,而实际只是使用了10M

    你最后的开窗,本身就在date栏位排序了,怎能说没有排序啊.

    是排序了,但这个排序是针对 date 的, 关其他列什么事? 这个是派生出来的一列吧

    我不知道为什么硬要把 date 的排序和其他列扯到一齐算?就因为它们在查询的时候从同一个表出?因为如果 j自己和自己oin 一下,把 date 的排序放到另一个表就没事

    “在排序时也只是排序你满足条件的,但就是这个满足条件也会造成非常大的评估.”, 如果评估是针对满足条件的, 为啥我把满足条件之外的数据删除旧,内在开销没这么大? 就算不删除掉,不知道你有没有看我的测试中的第2个查询, 这样的查询是没问题的

    SELECT * FROM dbo.__tb_test WHERE id IN ( SELECT id FROM dbo.__tb_test WHERE group_id = $GroupId ) ORDER BY date


    2014年10月20日 3:57
  • 排序是针对结果集的,否则你取的其他栏位如何一一对应呢?

    第二句我的叙述可能由歧义,评估内存和使用内存是两回事.(参见 grant memory和require memory)

    关于第三点我不知道如何解释,因为不同执行计划申请内存是不同的.

    即便是你的第二种写法,申请了几十M的内存,也只是使用其中很少的一部分.这是贪婪的缺陷吧.

    2014年10月20日 5:35
  • 如果是针对结果集的话,两个查询的结果集是一样的,那么内存评估应该至少差别不大,我把表中的数据删除,只保留满足条件的,这样查询一样,结果集一样,但内存开销即是大大的不同

    至于排序是针对所有列的问题,每条记录,有主键的可以主键对应,没主键的也可以书签定位,索引不就是这么玩的么?把所有列移移去的做排序,有些情况下效果更佳,有些情况下也不怎么样吧?

    内存上,说的是请求的内存和分配的内存,一直是这两块的问题,不是使用的内存,分配的内存已经代表了要先划出这么大一块内存给这个查询用到,和数据库文件的磁盘空间占用是类似道理吧?

    2014年10月20日 6:24
  • 查询结果集一样,写法不同啊.邹总你去鞭策微软,让优化器更柔和,少暴力些.

    数据在内存中,书签定位比较复杂了,不过针对不同的key装入应该是可以做到的.

    其实我想说的第三点,针对请求,授予.就是贪婪型的不足.申请那么多只使用那么点,对BP的稳定,整体并发会造成影响.

    2014年10月20日 6:36
  • “方法是不同的”,这是我认为的这个问题的关键, 因为如果纠结于排序,那么排序应该是基于结果,不同的查询,排序前的结果是相同的,那么排序的开销应该是差不多的

    不同的方法,开销差别很大,而且是与排序相关,是不是排序被放在了错误的位置? 或者是排序的开销预估放在了错误的位置

    更纠结的事,这种问题还只能烂在DBA这里面,这样一个查询内存开销弄成这样,跟谁也讲不通的吧?

    这个算法难道就不能稍微多考虑一点? 表空间也可以是一个参考依据吧?虽然存储不一样,但总有点参考价值吧?

    2014年10月20日 8:54
  • 其实没必要纠结什么,排序做为典型的内存消耗操作符在实际查询中应尽量避免.

    优化器在有这类运算符贪婪地申请内存尽量避免溢出的同时,会给整体吞吐带来负面影响.凡事都有两面性啊.

    的确针对一些场景,这优化器算法是有问题的,这我是无法解答邹兄了.邹兄的想法应该直接对话微软产品组.

    (其实有些地方我也提过我的建议,只是微软产品组不太关注.以邹兄的影响力应该会好些的吧..)

    2014年10月20日 9:41
  • 试了下256GB下结果:

    请求的内存总量GB               : 9.53
    实际授予的内存总量GB           : 9.53
    运行查询所需的最小内存GB       : 0.00
    此刻使用的物理内存GB           : 0.00
    到此刻为止所用的最大物理内存GB : 0.00

    2014年10月21日 6:11
  • 对于两条query的区别,我看过之后,目前两个语句的执行计划不一样,他们使用的内存不一致也是正常的。 而使用内存较多的$SqlCommand.CommandText = "SELECT * FROM dbo.__tb_test WHERE group_id = $GroupId ORDER BY date"实际上运行时间更短。 执行计划选择索引是基于统计信息的如果及时更新统计信息,在估计执行计划时也会较为合理。



    这个跟统计可能没有什么关系,表是新表的,没有索引,只有主键,需要的统计信息也只能是查询的时候自动创建的,这种情况下应该本来就是最新的了

    执行计划不一样的问题,后面有有讨论

    效率上,查询1确实好一些(执行时间更短,CPU开销更小,IO更低),但是内存分配得太大了,这个造成的负面影响应该算是比较大的,毕竟一台服务器就那么多内存,为它分配了这么大,章法着已经缓存的东西要释放,再次用到的时候要重新加载,这个对于一台使用率较高的产品服务器来说,这个影响算是很不好的吧?

    目前看起来只能用改查询的方法来避免,又有一个新的 T-SQL书写规范了

    2014年10月27日 6:30
  • 我建议在工作负载较大的情况下,测试grant memory的大小,去看看QO给予的内存是否会相应减小。
    2014年10月28日 9:18
  • 我建议在工作负载较大的情况下,测试grant memory的大小,去看看QO给予的内存是否会相应减小。

    这个本来就是在一台负荷比较大的服务器上监控到的问题,然后通过排查简化后得到的测试案例
    2014年10月28日 14:00
  • 邹建,

    我的意思是说如果在内存压力很大时,可用内存也较小 (例如8GB),可以看看QO给予的内存。因为QO分配内存的设计本身是个比较复杂的问题,我建议您可以给我们开一个咨询类的案例,这样我们可以深入帮您去研究回答这个问题。

    2014年10月29日 3:56
  • 邹建,

    我的意思是说如果在内存压力很大时,可用内存也较小 (例如8GB),可以看看QO给予的内存。因为QO分配内存的设计本身是个比较复杂的问题,我建议您可以给我们开一个咨询类的案例,这样我们可以深入帮您去研究回答这个问题。

    咨询类的案例,我从来没有开过,不知道要怎么开,也不知道要具备什么样的条件才能开呢?

    2014年10月29日 7:32
  • 我再次对这个案例做了测试,发现了更多的不确定性

    我把查询修改如下,其中的 group_id 是通过"SELECT TOP(1) group_id FROM dbo.__tb_test GROUP BY group_id HAVING COUNT(*) > 5000 ORDER BY COUNT(*)" 得到的值

    对于这两个查询,通过 TOP 固化了数据返回的数量,这两个查询,第1个的查询计划,TOP在 SORT之后才进行,使用了并行查询,预估的满足条件的记录数是9684条(实际满足条件的是9745条记录),第2个查询按照预估,先TOP,后排序

    SELECT * FROM(SELECT TOP(8 * 1000) * FROM dbo.__tb_test WHERE group_id = 153) A ORDER BY date
    SELECT * FROM(SELECT TOP(7 * 1000) * FROM dbo.__tb_test WHERE group_id = 153) A ORDER BY date

    结果: 第1个内存分配异常,第2 个正常

    ---------------------------------------------------------------------------------------

    于是我为第一个查询加了 MXDOP 1,这样两个的执行计划一样了,都不使用并行 ,两个查询的内存开销也正常了

    这个似乎又真的跟并行有关,但如果直接查询, SELECT * FROM dbo.__tb_test WHERE group_id = 153 ORDER BY date,则执行计划是并行的,但无法加 TOP, 加 MAXDOP,或者两者都加,都无法得到与子查询相似的计划,内存开销也降不下来

    2014年10月29日 7:55
  • 关于这个查询内存开销的影响,确实是很严重的存在的, 我在 32GB的服务器上测试,如果查询的 SqlDataReader 不关闭的情况下,当进行第4个(也就是4个并发)的时候,第4个连接是suspended 状态并等待资源 RESOURCE_SEMAPHORE

    所以在产品环境的服务器上,这样的查询确实是杀手级的存在,比较按照前面提供的资源,这个内存的分配是按百分比的,服务器的内存再大都没有用

    2014年10月29日 7:59
  • 邹建,

    我的意思是说如果在内存压力很大时,可用内存也较小 (例如8GB),可以看看QO给予的内存。因为QO分配内存的设计本身是个比较复杂的问题,我建议您可以给我们开一个咨询类的案例,这样我们可以深入帮您去研究回答这个问题。

    咨询类的案例,我从来没有开过,不知道要怎么开,也不知道要具备什么样的条件才能开呢?

    帮你问了一下,一般是先要签合同的,然后打电话给TAM或者CSR让他们帮你开case,如果是advisory会有RDC team的人帮你看的。(不清楚大陆是不是也这样)另外你可以试打一下这个打电话咨询一下8008203800。

    SQLOS 跟QO的内存需求计算模式公开的信息比较少,但如果能看到source code的话,这个问题估计就是 请求和授予跟机器total memory的一个数学表达式。

    另外可以看看QO team的人参与写的paper.

    http://academic.research.microsoft.com/Publication/2422074/managing-query-compilation-memory-consumption-to-improve-dbms-throughput



    Please click the Mark as Answer button if a post solves your problem!



    2014年10月29日 14:09
  • 这个东东就这样吧
    2014年11月4日 8:57
  • SQL Server的优化器的相关特性引起的问题:相关分析及解决方式在这里:http://www.cnblogs.com/shanksgao/p/4171265.html

    2014年12月18日 4:17
  • SQL Server的优化器的相关特性引起的问题:相关分析及解决方式在这里:http://www.cnblogs.com/shanksgao/p/4171265.html

    前面的人打死都不承认有是问题
    2014年12月18日 5:36
  • 哎,逻辑上算是没问题,毕竟SCAN接着Sort,但实现上就有个小坑了.优化器好心变坏事儿了~
    2014年12月18日 6:28