sqlserver查询耗时比较高的慢查询

select top 100 Area,Controller,Action,parameter,ip,userid,time
from Sys_LoggerOperate
where CreateTime>convert(varchar,getdate(),112) and Parameter like '{"code%'
order by time desc

use LQtex

select top 500 * into #tb from Sys_LoggerOperate where CreateTime>dateadd(day, -5, getdate()) order by time desc

select action 接口名,count(1) 上榜次数,avg(time) 平均时间,max(time) 最长时间,min(time) 最短时间 from #tb group by Action

Order by count(1) desc

 

use GEMes

select top 500 * into #tb from Sys_LoggerOperate where InputDate > dateadd(day, -1, getdate()) order by time desc

select action 接口名,count(1) 上榜次数,avg(time) 平均时间,max(time) 最长时间,min(time) 最短时间 from #tb group by Action

Order by count(1) desc

 

use GEMes

select top 500 Time,action,controller

from Sys_LoggerOperate

where InputDate>convert(varchar,getdate(),112) and Time >2000

order by time desc

　

　

/*

use LQtex

select blocking_session_id,*

from sys.dm_exec_requests

where status = 'suspended'

order by start_time ----查询锁耗时开始时间

 

Select * from master.sys.sysdatabases --查询数据库所在目录

 

SELECT TOP 100

(total_elapsed_time / execution_count)/1000 N'平均时间ms' ,total_elapsed_time/1000 N'总花费时间ms' ,total_worker_time/1000 N'所用的CPU总时间ms' ,total_physical_reads N'物理读取总次数' ,total_logical_reads/execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logical_writes N'逻辑写入总次数' ,execution_count N'执行次数' ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) N'执行语句' ,creation_time N'语句编译时间' ,last_execution_time N'上次执行时间' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

 

ORDER BY total_elapsed_time / execution_count DESC; --慢查询sql耗时

 

SELECT r.session_id, s.text

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.status = 'running'; --查询正在运行的SQL

 

use LQtex

SELECT

request_session_id,

resource_associated_entity_id,

OBJECT_NAME(

resource_associated_entity_id

) OBJECT_NAME

FROM sys.dm_tran_locks

WHERE resource_type = 'OBJECT' --查询ID对象-存储过程

*/

 

use LQtex

select blocked,spid,waittime from sys.sysprocesses where blocked <> '0' order by waittime desc --查询锁耗时

 

use LQtex

KILL 66