金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 查询统计,Server等待事件

查询统计,Server等待事件

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-22 11:08

等待事件介绍

 

数据库引擎接收到一个新的查询请求(Batch或SP),查询优化器会生成执行计划,并缓存到内存中;下次再次执行相同的查询请求时,数据库引擎从复用已经缓存的执行计划,换句话,数据库引擎为每一个查询请求生成执行计划,并把已经生成的执行计划缓存起来,当接收到相同的查询请求时,数据库引擎复用已缓存的执行计划。查询请求(Batch或SP)中的每一个查询语句的执行计划,都会被缓存到内存中,数据库引擎统计执行计划的性能参数,缓存在DMV:sys.dm_exec_query_stats中,在该视图中,每一行数据都表示一个查询语句的统计数据:

 

 前言: 本文是对博客的翻译,本文基本直译,部分地方读起来有点不自然。 如有翻译不对或不好的地方,敬请指出,大家一起学习进步。尊重原创和翻译劳动成果,转载时请注明出处。谢谢!

  • sql_handle:用以唯一标识一个TSQL文本(Batch或SP),TSQL文本存储在SQL Manager Cache(SQLMGR)中;
  • plan_handle:用于唯一标识一个已编辑的查询计划,查询计划存储在计划缓存中;

关于等待事件RESOURCE_SEMAPHORE_QUERY_COMPILE,官方的介绍如下:

 

一个sql_handle 能够生成多个查询计划,对应多个plan_handle,但是每个plan_handle只能对应一个sql_handle

 

 

一,获取查询语句

       Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.

问题描述

视图sys.dm_exec_query_stats 缓存的是单个查询语句的执行计划,而sql_handler引用的是整个TSQL文本(Batch或SP),为了获得单个查询语句的文本,必须通过语句的偏移字段来抽取,偏移量是字节,字节数量从0开始:

 

** 

  • statement_start_offset:语句开始偏移的字节序号
  • statement_end_offset:语句结束偏移的字节序号,-1 表示TSQL文本的末尾;

该等待事件在并发查询编译的数量达到阀值限制时出现。 等待时间较长或等待次数较多可能表明编译、重新编译或不能缓存的计划过多。

今天,我们的一个SQL Server实例性能变得非常慢。当我登录到数据库服务器进行一些初始检查时,我最初始观察、注意到的是它的内存压力(memory pressure)。接下来,我们必须找出是什么导致我们的实例出现内存紧张。当我检查事务的等待类型时,RESOURCE_SEMAPHORE等待是大多数事务的问题。在这篇文章中,我将描述这个问题,以及如何查找哪个查询语句或事务导致了内存压力

由于函数 sys.dm_exec_sql_text 返回的TSQL文本是以nvarchar(max)类型存储的,一般情况下,字节偏移量都是2的倍数,获取查询语句的脚本是:

 

 

图片 1图片 2

 

解决方案

select substring(st.text 
                ,qs.statement_start_offset/2+1,
                ( case when qs.statement_end_offset = -1 
                            then len(convert(nvarchar(max), st.text))
                        else (qs.statement_end_offset - qs.statement_start_offset)/2
                    end 
                )
        ) as individual_query
        ,st.text as entire_query
from sys.dm_exec_query_stats qs
outer apply sys.dm_exec_sql_text(qs.sql_handle) as st

等待事件分析

 

View Code

 

当我检查所有事务的等待类型时,大多数事务的等待类型为RESOURCE_SEMAPHORE 等待以及某些页面IO等待。页面IO等待也是由于内存压力导致,因为这些事务无法获得足够的内存来执行这个操作。

二,查看统计数据的平均值

内存授予的等待类型叫做“RESOURCE_SEMAPHORE”.在理解这个等待事件前,我们先来了解一下查询内存授予(query memory grant),它是用于在排序或连接时存储临时数据的服务器内存的一部分。查询在实际执行前需要先请求保留内存,所以会存在一个授予的动作。这样的好处是提高查询的可靠性和避免单个查询占用所有的内存。

资源信号量等待

1,查看语句级别的统计数据

 

 

执行计划的重编译次数,执行查询的总时间,逻辑读和物理读的次数等计数器,是观察查询执行情况的重要指标:

SQL Server在收到查询时,会执行3个被定义好的步骤来返回用户所请求的结果集。

在继续之前,我想对资源信号量(Resource_semaphore)等待进行一些说明,以便您可以更好地了解SQL Server是如何将内存授予SQL Server查询语句的。

  • plan_generation_num:表示执行计划产生的数量,表示同一个TSQL文本重新编译的次数;
  • total_elapsed_time:单词elapsed是指单个语句执行的总时间,包括 waiting的时间或 CPU工作(worker)的时间;
  • total_logical_reads:查询计划完成的逻辑读的次数;
  • total_physical_reads:查询计划完成的物理读的次数;

  

当SQL Server收到用户发送的查询请求(查询语句)时,它首先创建一个编译过的计划,然后在这个基础上创建一个执行步骤(个人觉得执行步骤比执行计划要合适)。当SQL Server创建一个编译过的计划时,它会计算两个内存授予参数,称为“请求内存”(required memory)和“附加内存”(additional memory)。请求内存是运行排序和HASH连接所需的最小内存。它之所以是 "必需"的, 是因为如果没有可用的“请求内存”, 查询将无法启动。附加内存(additional memory)是在内存中存储临时行(个人觉得翻译为中间结果可能更合理)所需的内存量。这被称为额外(附加)的,因为如果没有足够可用的“附加内存”可以将查询的中间结果存储在磁盘上。 

以下脚本用于查看语句级别的执行计划的平均数据,并按照平均执行时间排序:

 

首先,服务器计算任何给定的查询执行需要多少内存。这通常是“请求内存”(required memory)和“附加内存”(additional memory)的总和,但如果您的实例正在并行处理,那么所需的内存将是(所需的内存* DOP)+附加内存。服务器检查所需的内存是否超过每个查询限制,然后服务器减少“附加内存”,直到总数达到限制。这个修改后的大小称为请求内存。SQL Server有一个内部工具称为资源信号量(RESOURCE SEMAPHORE),用于将此请求的内存授予查询。如果无法通过资源信号量向该请求的内存授予查询,那么如果查询sys.sysprocesses系统表或​​sys.dm_exec_request DMV,则该查询将处于等待状态,并出现RESOURCE_SEMAPHORE等待类型。

select top 111 
    qs.execution_count,
    qs.total_rows/qs.execution_count as avg_rows,
    qs.total_worker_time/qs.execution_count/1000 as avg_worker_ms,
    qs.total_elapsed_time/qs.execution_count/1000 as avg_elapsed_ms,
    qs.total_physical_reads/qs.execution_count as avg_physical_reads,
    qs.total_logical_reads/qs.execution_count as avg_logical_reads,
    qs.total_logical_writes/qs.execution_count as avg_logical_writes,
    qs.creation_time,
    qs.plan_generation_num,
    --st.text as entire_query,
    substring(st.text,
            qs.statement_start_offset/2 + 1,      
            ( case when qs.statement_end_offset = -1 
                        then len(convert(nvarchar(max), st.text))
                else (qs.statement_end_offset -qs.statement_start_offset)/2
              end)
            ) as individual_query
from sys.dm_exec_query_stats qs 
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st 
order by avg_elapsed_ms desc 
  1. 生成编译计划(compiled plan)。它包括各种逻辑指令,如怎么联接数据行。

当资源信号量(Resource_semaphore)接收到新的请求时,它首先检查是否有任何查询正在等待中。只要发现存在即是一个等待查询,那么会将新查询(新请求)放入队列中,因为等待队列是以先到先得的方式设计的,并且有小权重以支持于小型查询。当没有等待查询或查询返回保留的内存时。资源信号量尝试授予内存。如果找到足够的内存,那么请求内存被授予并且查询可以开始运行,并且如果没有找到足够的可用内存来授予所请求的内存,那么它将当前查询放入等待队列中,并且给当前会话RESOURCE_SEMAPHORE等待类型, 此时服务器开始面临内存压力。

2,查看存储过程级别的查询统计

2. 生成执行计划(execution plan),它包含将编译计划中的各种逻辑引用转换成实际的对象的指令和查询执行的跟踪机制。

 

对于缓存的存储过程,数据库引擎把SP相关的统计数据缓存在视图:sys.dm_exec_procedure_stats 中,每一行数据都表示一个SP的统计数据:

3. 从指令树的顶端开始执行。

 

图片 3图片 4

 

识别RESOURCE_SEMAPHORE等待

select top 111
    db_name(ps.database_id) as db_name
    ,ps.database_id
    ,object_schema_name(ps.object_id,ps.database_id)+'.'+object_name(ps.object_id,ps.database_id) as proc_name
    ,ps.type_desc as proc_type
    ,ps.cached_time
    ,ps.execution_count
    ,ps.total_worker_time/ps.execution_count/1000 as avg_worker_ms
    ,ps.total_elapsed_time/ps.execution_count/1000 as avg_elapsed_ms
    ,ps.total_physical_reads/ps.execution_count as avg_physical_reads
    ,ps.total_logical_reads/ps.execution_count as avg_logical_reads
    ,ps.total_logical_writes/ps.execution_count as avg_logical_writes
from sys.dm_exec_procedure_stats ps
where ps.database_id<32767
order by avg_elapsed_ms desc

生成编译计划是件开销较大的事情,因为它需要在数以百计的编译计划中找出较优的一个。它的时间通常很短,因为优化器会在找到最优的编译计划后便马上释放内存。编译主要使用内存和CPU资源。缺少可用内存可能会导致编译延迟和得到非最优的编译计划。    

** 

View Code

 

步骤1

对于database_id 为 32767,这个id是资源数据库(Resource Database)预留的ID,一般情况下,用户创建的数据库ID都会小于该数值。

 

** 

三,查看查询计划

当SQL Server创建编译计划时,会计算两个参数:必须内存(Requeried memory)和额外内存(Additional memory)。

** 

函数 sys.dm_exec_query_plan 以XML格式返回指定batch或SP的查询计划,参数是plan_handle,这意味着,函数返回的是整个语句(Batch或SP)的执行计划,而视图sys.dm_exec_query_stats 缓存的是Batch或SP中某一个查询语句的统计信息,在query_plan字段上会出现大量的冗余:

  

首先,我们需要研究我们的实例,弄清楚为什么在SQL Server中出现内存压力。要查看所有事务的大概信息,我们可以查询sys.sysprocesses,或者我们可以使用sys.dm_exec_requests DMV。

图片 5图片 6

  必须内存:执行排序和哈希联接所需的最少内存。这部分内存是“必须”的,它用来创建处理排序和哈希所需要的内部数据结构。

SELECT * FROM sys.sysprocesses

select top 111 
    qs.execution_count,
    qs.total_rows/qs.execution_count as avg_rows,
    qs.total_worker_time/qs.execution_count/1000 as avg_worker_ms,
    qs.total_elapsed_time/qs.execution_count/1000 as avg_elapsed_ms,
    qs.total_physical_reads/qs.execution_count as avg_physical_reads,
    qs.total_logical_reads/qs.execution_count as avg_logical_reads,
    qs.total_logical_writes/qs.execution_count as avg_logical_writes,
    qs.creation_time,
    qs.plan_generation_num,
    st.text as entire_query,
    substring(st.text,
            qs.statement_start_offset/2 + 1,      
            ( case when qs.statement_end_offset = -1 
                        then len(convert(nvarchar(max), st.text))
                else (qs.statement_end_offset -qs.statement_start_offset)/2
              end)
            ) as individual_query,
    qp.query_plan
from sys.dm_exec_query_stats qs 
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st 
outer apply sys.dm_exec_query_plan(qs.plan_handle) as qp
order by avg_elapsed_ms desc

  额外内存:存储所有临时数据行所需的内存。它的大小由基数评估(Cardinality estimate,如行数和行大小)决定。“额外”,顾名思义在缺少这部分内存时,将会将临时数据行存到硬盘上,并不会导致查询失败。一个查询的额外内存大小如果超过预设的限制,它实际得到的内存量并不一定会跟请求量一样。

ORDER BY lastwaittype

View Code

 

 

缓存的查询计划,被数据库引擎缓存在视图:sys.dm_exec_cached_plans,每一个查询计划都存储一行,从该视图中能够查看缓存的查询计划及其文本,计划占用的内存大小,以及查询计划被重用的次数等数据:

例如,对行大小为10byte的100万行数据进行排序,此查询的必须内存为为512KB(此值是SQL Server处理一个排序操作创建内部数据结构所需的最小内存量)。为了存储所有数据行,额外内存可能是10MB。

 

select cp.refcounts
    ,cp.usecounts
    ,cp.size_in_bytes
    ,cp.cacheobjtype
    ,cp.objtype
    ,st.text as entire_sql
    --,cp.plan_handle
from sys.dm_exec_cached_plans cp
outer apply sys.dm_exec_sql_text(cp.plan_handle) st

 

这里我们可以看到所有产生RESOURCE_SEMAPHORE等待类型的进程。

四,内存授予

  当编译计划中含有多个排序和联接操作时,额外内存的计算就变得复杂了。因为SQL Server要考虑所有操作符如何高效地使用内存。可以查看ShowPlan XML中的<MemoryFractions>标记部分内容,获取更多内存使用的信息。

图片 7

内存授予只出现在特定的查询语句中,如果一个查询包含排序,Hash等操作,那么该语句在执行之前,必须申请到必需的内存,这意味着,如果数据库引擎不能分配足够的授予内存,那么查询请求将不会执行。

 

 

视图sys.dm_exec_query_memory_grants 返回当前数据库中申请内存授予的状态:

RESOURCE_SEMAPHORE_QUERY_COMPILE等待事件一般是查询正在等待授予内存以开始进行编译时发生。编译内存来自缓冲池(buffer pool),并需要保留足够的时间以完成编译过程。 对于多个并发编译而言,占用太多内存页可能会导致内存压力。 为了缓解这种情况,SQL Server启动编译过程,确定哪些查询需要大量的页面,并迫使某一些查询会话等待。 同样,如果内存压力已经存在,SQL Server将限制可以同时编译的资源密集型查询的数量。

步骤2

图片 8图片 9

 

 

select mg.session_id
    --,mg.request_id
    ,mg.resource_semaphore_id
    ,mg.wait_time_ms
    ,mg.dop
    ,mg.requested_memory_kb
    ,mg.required_memory_kb
    ,mg.used_memory_kb
    ,mg.max_used_memory_kb
    ,mg.ideal_memory_kb
    ,st.text as entire_sql
from sys.dm_exec_query_memory_grants mg
outer apply sys.dm_exec_sql_text(mg.sql_handle) as st
order by mg.wait_time_ms desc

  如果你的数据库经常看到这种等待事件或此等待类型过多,那么你的数据库可能会有太多内存密集型查询(大型查询),或者其他进程可能正在从缓冲池中窃取内存页面.

从上面的SQL查询,我们可以看到大量的事务正处于Resource Semaphore(资源信号量)等待状态。现在我们可以运行下面的SQL语句来查看已分配到内存的查询的目前状态,和未被分配内存的查询的数量。                

View Code

 

                                                       

在申请内存授予时,数据库引擎会发送资源信号(resource semaphore),视图 sys.dm_exec_query_resource_semaphores 返回当前数据库中查询-执行的内存状态,用于检测当前数据库是否有足够的内存,用于所有的查询计划。

 

该DMV的输出返回两行,一个表示大型查询(resource_semaphore_id为0),另一个表示小型查询(resource_semaphore_id为1),小于5 MB。在这里,您可以获得实例的总授予内存和总可用内存。请参阅grantee_countwaiter_count上的数字,grantee_count是已经分配了内存的总查询数,waiter_count是队列中等待获取内存的总查询数量。所以在这里我们可以看到大约100个查询正在等待获得他们要求的内存。

当内存紧张时,查询请求申请不到足够的授予内存,处于RESOURCE_SEMAPHORE等待状态。此时,数据库引擎发送资源信号(RESOURCE SEMAPHORE)申请授予内存(Requested Memory)。

减少等待事件方案

 

当SQL Server实例接收到用户的查询请求时,查询优化器首先创建编译计划(Complied Plan),根据编译计划再创建执行计划(Execution Plan)。查询优化器在创建编译计划时,需要计算查询语句在执行时需要消耗的内存。用于执行查询语句的内存分为必需内存(Required Memory)和额外内存(Additional Memory),必需内存是指SQL Server实例执行Sort或Hash操作必须分配的最小内存,如果没有分配必需内存,查询请求不会执行;额外内存是查询语句用于存储临时的中间数据的内存,如果SQL Server没有足够的内存,数据库引擎把临时数据存储在硬盘中,这会降低查询语句执行的性能。

 

SELECT * FROM sys.dm_exec_query_resource_semaphores

SQL Server 要授予多少内存,查询请求才能真正开始执行呢?

 

 

  • Step1,计算需要的内存(Needed Memory):SQL Server计算每个查询需要多少内存才能执行,这通常是必需内存和额外内存之和,当查询请求以并发方式执行时,需要的内存公式是:(Required Memory*DOP)+额外内存。
  • Step2,计算请求的内存(Requested Memory):SQL Server检查每个查询请求需要的内存数量是否超出系统的限制,SQL Server减少额外内存的数量,以致于不会超出系统的上限,这个最终的内存数量是查询语句得以执行的请求内存。
  • Step3,为查询分配请求内存:SQL Server实例发送资源信号(RESOURCE SEMAPHORE),为查询(Query)语句授予/分配请求的物理内存。
  1. Decrease query complexity 降低查询语句的复杂度。

图片 10

当资源信号发送之后,如果SQL Server实例不能被授予查询的请求内存,那么查询请求处于RESOURCE_SEMAPHORE 等待状态。SQL Server维护一个先入先出( first-come-first-served)的等待队列,当新的查询请求处于RESOURCE_SEMAPHORE 等待状态,SQL Server将该查询放入队列的末尾。一旦SQL Server实例找到足够的空闲内存,那么数据库引擎取出RESOURCE_SEMAPHORE 等待队列顶端的第一个查询,立即授予其申请的请求内存(Requested Memory);该查询获得请求内存之后,开始执行查询任务。如果SQL Server实例长时间有查询处于RESOURCE_SEMAPHORE等待状态,说明SQL Server 面临内存压力。

 

 

 

  1. Appropriate indexing could reduce plan complexity  合理创建索引减少执行计划复杂度

步骤3

参考文档:

 

** 

Execution Related Dynamic Management Views and Functions (Transact-SQL).aspx)

  1. Improve plan reuse (therefore compilation can be avoided)  改善执行计划重用(因此可以避免编译)

现在我们将获取所有正在等待队列中获取所请求的内存的所有查询的详细信息,我们将使用DMV sys.dm_exec_query_memory_grants来获取队列中等待分配内存的查询总数。对于等待获取其请求的内存的查询,grant_timegranted_memory_kb列将为NULL。您可以在下面的截图中查看所请求的内存量及其等待状态,因为它们的grant_time和granted_memory_kb值为NULL。我们还可以使用该DMV获取所有查询的plan_handle和sql_handle。稍后我们将使用这些值来获取确切的查询。

 

注意:有太多列要显示,可以只显示部分所需的列。

  1. kill掉一些糟糕的SQL语句(内存资源密集型SQL),当然这个要看是否可行。

 

 

SELECT * FROM sys.dm_exec_query_memory_grants

 

 

 

图片 11

个人曾遇到过这样一个案例,由于过度灵活设计,导致很多报表需要在SQL中大量关联相关表,更糟糕的是,由于开发人员大量使用视图,尤其是还存在视图嵌套视图的情况,所以在这样一个系统中,一些查询语句往往需要授予大量的内存,尤其是当出现一个或一些写的很糟糕的SQL语句时,就会经常看到一些会话处于RESOURCE_SEMAPHORE_QUERY_COMPILE的等待状态,而且当大量会话处于RESOURCE_SEMAPHORE_QUERY_COMPILE等待时,还有一个特殊现象就是活动的会话数量会彪增,此时,可以找到消耗内存最多的SQL,然后Kill掉后,活动的会话就会立即降下来。下面就是我遇到案例的一个截图。

 

 

 

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan 

FROM sys.dm_exec_query_memory_grants AS mg

CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp

ORDER BY 1 DESC OPTION (MAXDOP 1)

步骤4

图片 12

现在,我们将找到内存密集型查询。我们可以看到所有等待查询的请求内存。在这里我们可以看到所请求的内存对于大多数事务来说太大了。我们将获得所有这些查询的plan_handle,以获得确切的SQL文本来查看查询计划。

 

从sys.dm_exec_query_memory_grants中选择前10个*

另外,内存紧张也会导致RESOURCE_SEMAPHORE_QUERY_COMPILE的出现的概率增加,那么是否增加内存就有效解决RESOURCE_SEMAPHORE_QUERY_COMPILE等待事件呢?答案是否定的,但是能缓解。如下描述:

 

 

select top 10 * from sys.dm_exec_query_memory_grants

     This wait occurs when queries cannot be compiled due to the amount of compile memory currently available. This mostly occurs due to large queries requiring an excessive amount of memory. SQL Server caps the amount of complex queries that can be compiled at once, so increasing the memory allocation will not solve the problem effectively (it will only increase the amount of memory that can be allocated, not the number of queries)

图片 13

 

 

 

步骤5

参考资料:

** 

 

现在我们将使用上面查询所获得的plan_handle和sql句柄来获取SQL代码。

运行以下语句从上述查询中获取SQL代码,使用sql_handle。

 

SELECT * FROM sys.dm_exec_sql_text(sql_handle)

 

https://blogs.msdn.microsoft.com/sqlqueryprocessing/2010/02/16/understanding-sql-server-memory-grant/

图片 14

我们还可以使用步骤4中查询中的plan_handle获取SQL计划。

SELECT * FROM sys.dm_exec_sql_plan(plan_handle)   --译者注  实际没有sys.dm_exec_sql_plan, 而是sys.dm_exec_query_plan,估计是作者笔误。

 

 

结论

** 

现在我们已经找到内存密集型查询及其执行计划,我们的下一步是研究这些查询,并找出如何调整、优化它们。我们应该查看查询中是否使用的错误的索引或是否存在索引缺失的情况,并创建正确的索引。在我们这种情况下,这是由于糟糕的索引设计导致了内存紧张。在创建、调整合适的索引之后,相同的查询运行的时候请求的内存就会少得多。

 

下一步

** 

  • 使用此提示来识别由于内存不足而消耗更多内存并将剩余的事务置于等待状态的查询。
  • 还要查看上述DMV的其他列,并将它们联系起来,以便更好地分析和了解性能问题。
  • 这些DMV应提供大量信息,以便您能够识别问题。
  • 阅读有关性能调优的更多提示,以提高系统性能。

 

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:查询统计,Server等待事件

关键词:

上一篇:java复习基础篇,基础知识与语句笔记

下一篇:没有了