SQLServer⽇常维护之⼋：查询性能消耗⾼的语句
SQL Server执⾏过程中，我们可能会需要调查服务器运⾏缓慢的原因。此时，我们需要通过⼀些SQL语句来调查数据库运⾏的SQL性能，⽐如CPU消耗⾼的SQL语句、查询时间长的SQL语句、消耗IO资源多的SQL语句、⼤批量检索数据的SQL语句等。
[操作步骤]
1. CPU消耗⾼前五的SQL语句
以下⽰例返回有关按平均CPU时间排名的前五个查询的信息。在此⽰例中，查询是根据查询哈希值聚合的，逻辑上等效的查询按累积的资源使⽤情况进⾏分组。
SELECT TOP5 total_worker_time/execution_count AS[Avg CPU Time],
,(qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN-1THEN )
ELSE qs.statement_end_offset
END- qs.statement_start_offset)/2)+1)AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)AS st
ORDER BY total_worker_time/execution_count DESC;
2. 查询最消耗时间的SQL语句
/* 查看哪些SQL语句消耗，出有问题的SQL语句进⾏优化或者索引优化*/
SELECT
(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'执⾏次数'
,,(qs.statement_start_offset/2)+1,
((CASE statement_end_offset
WHEN-1THEN )
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
WHERE
,(qs.statement_start_offset/2)+1,
((CASE statement_end_offset
WHEN-1THEN )
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2)+1)not like'tch%'
sql语句优化方式ORDER BY
total_elapsed_time / execution_count DESC;
3. 查SQL阻塞进程
/* ⾼并发情况下，容易产⽣进程阻塞，查看阻塞的SQL */
SELECT  t1.request_session_id AS'wait_sid',
DB_NAME(resource_database_id)AS'库明称',
t2.wait_duration_ms AS'wait_time_ms',
(SELECT text
FROM      sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
WHERE    r.session_id = t1.request_session_id
)
AS'wait_run_batch',
(SELECT    , r.statement_start_offset /2+1,
(CASE WHEN r.statement_end_offset =-1
THEN )
ELSE r.statement_end_offset
END- r.statement_start_offset )/2+1)
FROM      sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)AS qt
WHERE    r.session_id = t1.request_session_id
)AS'wait 运⾏的SQL语句',
t2.blocking_session_id AS'锁定sid',
(SELECT text
FROM      sys.sysprocesses AS p
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE    p.spid = t2.blocking_session_id
)AS'锁定SQL'
FROM    sys.dm_tran_locks AS t1
INNER JOIN sys.dm_os_waiting_tasks AS t2 ON t1.lock_owner_address = t2.resource_address
4. 对查询返回⾏计数聚合
以下⽰例返回查询的⾏计数聚合信息（总⾏数、最⼩⾏数、最⼤⾏数和上⼀次⾏数）。
ution_count,
,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset =-1
THEN LEN(CONVERT(nvarchar(max), qt.text))*2
ELSE qs.statement_end_offset end-
qs.statement_start_offset
)/2
)AS query_text,
qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid,
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)AS qt
like'%SELECT%'
ORDER ution_count DESC;
5. SQLServer连接情况查询
# 请求信息
select s.session_id, s.status,db_name(r.database_id)as database_name, s.login_name,s.login_time, s.host_name,
c.client_net_address,c.client_tcp_port,s.program_name,
r.cpu_time, r.reads, r.writes,c.num_reads,c.num_writes,
s.client_interface_name,
s.last_request_start_time, s.last_request_end_time,
r.start_time, r.status, rmand,
r.blocking_session_id, r.wait_type,
r.wait_time, r.last_wait_type, r.wait_resource, r.open_transaction_count, r.percent_anted_query_memory
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s  with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c  with(nolock)
on s.session_id = c.session_id
where s.session_id >50
order by s.session_id
# 按⽤户统计连接数
select login_name,COUNT(0) user_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s  with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c  with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by login_name
order by2DESC
# 按机器统计连接数
select s.host_name,c.client_net_address,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s  with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c  with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by host_name,client_net_address
order by3DESC

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。