如果一个查询请求需要等待分配授予内存,那么可以通过系统视图:sys.dm_exec_query_memory_grants 查看等待系统分配授予内存的查询请求(Request),及它的关于授予内存的信息。如果一个查询请求不需要等待分配授予内存,或者不需要授予内存,那么它不会出现在该视图中。在编译查询请求时,SQL Server首先评估内存的使用量(ideal_memory_kb),查询计划在执行之前,需要向SQL Server申请内存(requested_memory_kb),SQL Server 根据系统系统内存的使用情况,分配一定数量的物理内存(granted_memory_kb)给该查询请求,查询请求获得授予内存之后开始“真正地”执行。
- select
- g.session_id,
- g.request_time,
- g.grant_time,
- g.wait_time_ms,
- g.query_cost,
- g.dop,
- g.requested_memory_kb,
- g.granted_memory_kb,
- g.required_memory_kb,
- g.used_memory_kb,
- g.max_used_memory_kb,
- g.ideal_memory_kb,
- g.wait_order,
- g.is_next_candidate,
- g.group_id,
- g.pool_id,
- g.resource_semaphore_id,
- st.text,
- p.query_plan
- from sys.dm_exec_query_memory_grants g
- outer apply sys.dm_exec_sql_text(g.sql_handle) as st
- outer apply sys.dm_exec_query_plan(g.plan_handle) as p
视图:sys.dm_exec_requests 的字段:granted_query_memory ,用于表示为该查询请求已经分配的授予内存页的数量,如果一个查询请求正在等待授予内存,那么字段wait_type是RESOURCE_SEMAPHORE。
二,查看资源信号的汇总数据
通过系统视图:sys.dm_exec_query_resource_semaphores 查看当前的所有资源信号的状态,以确定当前系统是否有足够的内存分配给查询请求。该视图汇总系统中所有查询请求的授予内存,为每个资源池(Resource Pool)返回两行,一行是常规的资源信号,另一行是小查询( small-query)的资源信号,所谓小查询的资源信号是指:申请的授予内存小于5MB,查询开销(Query Cost)小于3个开销单位(Cost Unit)。
三,查看内存书记统计的保留内存
在SQL Server中,只有内存书记(Memory Clerk)能够分配内存,任何一个需要使用内存的对象,必须创建自己的Memory Clerk,并使用该Memory clerk来分配内存。
Memory Clerk会记录已经分配内存的数量,SQL Server 使用 ‘MEMORYCLERK_SQLQERESERVATIONS’ 来分配执行排序或哈希操作所需要的内存,可以使用 sys.dm_os_memory_clerks 来查看系统中执行排序或哈希操作时分配的总内存。
- select type,
- pages_kb,
- virtual_memory_reserved_kb,
- virtual_memory_committed_kb,
- shared_memory_reserved_kb,
- shared_memory_committed_kb,
- page_size_in_bytes
- from sys.dm_os_memory_clerks
- where type = 'MEMORYCLERK_SQLQERESERVATIONS'
- and memory_node_id<>64
在该视图中,内存节点ID(memory_node_id)为64时,只在DAC中使用,该节点不会关联到任何物理内存节点(Physical Memory Node),仅是为了支持DAC而专门设计的一个逻辑内存节点(Logical Memory Node)。
四,数据溢出到tempdb
如果授予内存不足,对查询请求会有什么副作用?授予内存不足,会导致请求数据溢出到tempdb,实际上,是溢出到硬盘,这种警告,是查询语句的性能低下的一个信号。
在执行一个查询语句时,发现 TOP(10) 和 TOP(100)所用时间差距很大。在对其调优时,发现排序操作符(Sort Operator)消耗的时间高达95%,并抛出警告:
Operator used tempdb to spill data during execution with spill level 1
SQL Server 之所以抛出警告,是因为排序操作符的实际值(Actual Number of Rows)明显大于评估值(Estimated Number of Rows),SQL Server引擎根据执行计划的评估值,计算授予内存的数量,然后分配授予内存。在SQL Server 真正进行排序操作时,由于查询请求预先被分配的授予内存少于实际需要的内存,这导致SQL Server必须把中间结果集转存到tempdb中。这种情况虽然不会导致错误,但是会降低查询的性能。如果能使全部数据都在内存中排序,那么就能提高查询语句的性能。
SQL Server引擎计算授予的评估值是根据系统维护的统计信息(statistics)来评估的,如果索引的统计信息(Index Statistics)过期,或者长时间未刷新,这会导致查询优化器(Query Optimizer)低估实际值(Actual number of rows),导致Actual Number of Rows明显大于Estimated Number of Rows,因此,必须刷新索引的统计信息(Statistics),使查询优化器基于正确的统计信息做评估。还可以优化查询语句,使其能够引用索引;或者增加必要的内存,或者创建正确的索引,或者重新编译执行计划。
参考文档:
Memory Meditation: The mysterious SQL Server memory consumer with Many Names
Understanding SQL server memory grant
Identifying and Solving Sort Warnings Problems in SQL Server
Never Ignore a Sort Warning in SQL Server
SQL Server 2012: Sort operator causing tempdb spill
Correct SQL Server TempDB Spills in Query Plans Caused by Outdated Statistics
SpillToTempDb warning and SpillLevel’s mapping to single versus multiple pass
来源: http://www.cnblogs.com/ljhdo/p/5654400.html