数据库优化

来源:http://www.prospettivedarte.com 作者:计算机教程 人气:125 发布时间:2020-03-17
摘要:为了准备今年的双11很久没有更新blog,在最近的几次sqlserver问题的排查中,总结了sqlserver几种典型的等待类型,类似于oracle中的等待事件,如果看到这样的等待类型时候能够迅速定位问

为了准备今年的双11很久没有更新blog,在最近的几次sqlserver问题的排查中,总结了sqlserver几种典型的等待类型,类似于oracle中的等待事件,如果看到这样的等待类型时候能够迅速定位问题的根源,下面通过一则案例来把这些典型的等待处理方法整理出来:

数据库优化 1. 查看活动监视器。 2. 查看慢Sql. 3. 查看Resources Waits里面等等类型Lock的多不多。 4. 逻辑读太多,cpu就会高。 select * from syserrorlog where friendlymsg like '%列“%”不属于表 Table%' select errorpage a,createdate aa,friendlymsg c1,* from syserrorlog where friendlymsg like '%was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction%' order by createdate desc 获取慢sql语句 A.获取有关按平均 CPU 时间排在最前面的五百个查询的信息 SELECT TOP 500 DB_NAME([st].dbid) AS DB_NAME ,OBJECT_NAME([st].objectid) as OBJECTNAME, SUBSTRING(st.text, (qs.statement_start_offset/2) 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) 1) AS statement_text,total_worker_time/execution_count AS [Avg CPU Time],execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, max_logical_writes, total_logical_reads, min_logical_reads, max_logical_reads, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st where st.dbid = DB_ID('fx361_new') ORDER BY total_worker_time/execution_count DESC; B. 查看系统等待的sql: SELECT ss.host_name, req.session_id ,req.status ,req.blocking_session_id ,req.wait_type ,req.wait_time ,req.wait_resource ,req.transaction_id ,st.text FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST cross apply sys.dm_exec_sessions ss where req.status = N'suspended' and ss.session_id=req.session_id C.返回按批执行的 SQL 查询的文本,并提供有关它们的统计信息 SELECT DB_NAME([s2].dbid) as DBNAME,s2.dbid, OBJECT_NAME([s2].objectid) as OBJECTNAME, s1.sql_handle, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2 1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2 1)) AS sql_statement, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, max_logical_writes, total_logical_reads, min_logical_reads, max_logical_reads, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid is null ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

第一种等待.memory等待

早上接到一用户反馈其RDS实例非常的慢,通过观察sqlserver活动会话监视器的waiting tasks可以看到有10多w的等待任务,可以明确数据库现在已经出现了较大的瓶颈,紧接着通过resource waits看到数据库中有大量的memory内存等待:

看到是memory 资源等待后,为了立刻恢复用户应用,想到立刻去调大内存,发现该实例已经是24G了,看来一下os的空余内存,还有较多的内存剩余,所以将内存调大到36G,发现resource waits还是在memory上等待,同时这个时候的cpu使用率飙升,达到了90%左右.这样解决不了根本问题,于是通过recent expensive queries,发现以下sql的逻辑读很高,执行非常频繁:

SELECT * FROM RefundOrder_Message messages0_ WHERE messages0_.Order_Id=@p0;

也可以通过如下方式获得造成内存等待的sql:SELECT st.text FROM sys.dm_exec_query_memory_grants req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as STwhere req.grant_time is NULL or req.granted_memory_kb is NULL

The columns grant_time and granted_memory_kb will be NULL for those queries which are waiting to get their requested memory

sp_helpindex RefundOrder_Message发现该表只有一个主键索引:

创建一下索引:create index ind_RefundOrder_Message_order_id on RefundOrder_Message(Order_Id);

第二种等待:latch等待

在索引加上去后,memory的等待立刻消失,但是resource waits的等待变为了 lock:

本文由皇牌天下投注网发布于计算机教程,转载请注明出处:数据库优化

关键词:

最火资讯