常用sqlserver性能分析

发布时间：2020-12-12 13:49:54 所属栏目：MsSql教程来源：网络整理

导读：查看系统日志 select?*?from?sys.traces? 查看sql profile转储文件先要用sql profile监控数据库,导出成trc 拷贝至数据库服务器上，eg.d:sqldatatrace201611290881.trc sql查询中如下语句查看 --SELECT?cpu,TextData,Reads,Writes? SELECT?* FROM?::fn_tr

查看系统日志 select?*?from?sys.traces?
查看sql profile转储文件

先要用sql profile监控数据库,导出成trc
拷贝至数据库服务器上，eg.d:sqldatatrace201611290881.trc
sql查询中如下语句查看

--SELECT?cpu,TextData,Reads,Writes?
SELECT?*
FROM?::fn_trace_gettable('d:sqldatatrace201611290881.trc',default)?
WHERE?SPID?>?50?
and?StartTime?>?'2016-11-29?11:30:00.000'
AND?cpu?>?100 ORDER?BY?cpu?DESC
--查看trace文件里内容 select * from fn_trace_gettable('C:a.trc',0) where? HostName='SIT1_WIN_API' order by reads desc
执行统计功能 打开统计功能 set statistics io on? 执行具体sql语句消息中可以看到具体执行情况

查看执行计划 打开执行计划执行语句在执行计划tab查看具体执行计划

--查看正在执行的SQL及执行计划 SELECT? s .[session_id], ? ? ? ? r.[start_time], ? ? ? ? DATEDIFF(s,r .start_time,GETDATE()) AS elapsed_MS, ? ? ? ? r.[status] AS RequestStatus, ? ? ? ? DB_NAME(r .database_id) AS DatabaseName, ? ? ? ? r.[wait_type], ? ? ? ? r.[wait_resource], ? ? ? ? r.[wait_time], ? ? ? ? r.[reads], ? ? ? ? r.[writes], ? ? ? ? r.[sql_handle], ? ? ? ? r.[logical_reads], ? ? ? ? s.[status] AS SessionStatus, ? ? ? ? s.[host_name], ? ? ? ? s.[original_login_name], ? ? ? ? s.[nt_user_name], ? ? ? ? s.[program_name], ? ? ? ? s.[client_interface_name], ? ? ? ? c.[client_net_address], ? ? ? ? SUBSTRING(qt .text,r. statement_start_offset / 2, ? ? ? ? ? ? ? ? ? ( CASE WHEN r.statement_end_offset = -1 ? ? ? ? ? ? ? ? ? ? ? ? ?THEN LEN (CONVERT( NVARCHAR(MAX ),qt .text)) * 2 ? ? ? ? ? ? ? ? ? ? ? ? ?ELSE r .statement_end_offset ? ? ? ? ? ? ? ? ? ? END - r. statement_start_offset ) / 2 ) AS ExecutingSQL, ? ? ? ? qp.query_plan FROM? ? sys .dm_exec_requests r ? ? ? ? INNER JOIN sys. dm_exec_sessions s ON r.session_id = s.session_id ? ? ? ? LEFT JOIN sys. dm_exec_connections c ON c.session_id = s.session_id ? ? ? ? CROSS APPLY sys. dm_exec_sql_text(r .sql_handle) AS qt ? ? ? ? CROSS APPLY sys. dm_exec_query_plan(r .plan_handle) AS qp
ORDER BY elapsed_MS DESC
perfmon监控Sql Server锁

--查看阻塞 SET?TRANSACTION?ISOLATION?LEVEL?READ?UNCOMMITTED SELECT Waits.wait_duration_ms?/?1000?AS?WaitInSeconds,?Blocking?.session_id?as?BlockingSessionId,?DB_NAME?(Blocked.?database_id)?AS?DatabaseName,?Sess?.login_name?AS?BlockingUser,?Sess?.host_name?AS?BlockingLocation,?BlockingSQL?.text?AS?BlockingSQL,?Blocked?.session_id?AS?BlockedSessionId,?BlockedSess?.login_name?AS?BlockedUser,?BlockedSess?.host_name?AS?BlockedLocation,?BlockedSQL?.text?AS?BlockedSQL,?SUBSTRING?(BlockedSQL?.text,?(BlockedReq?.statement_start_offset/?2)?+?1,((CASE?WHEN?BlockedReq?.statement_end_offset?=?-?1 THEN?LEN?(CONVERT(?NVARCHAR(MAX?),?BlockedSQL?.text))?*?2 ELSE?BlockedReq?.statement_end_offset END?-?BlockedReq.?statement_start_offset)/2?)?+?1) AS?[Blocked?Individual?Query],?Waits?.wait_type FROM?sys?.dm_exec_connections?AS?Blocking?????????????????????????? INNER?JOIN?sys.?dm_exec_requests?AS?Blocked ON?Blocking?.session_id?=?Blocked?.blocking_session_id INNER?JOIN?sys.?dm_exec_sessions?Sess ON?Blocking?.session_id?=?sess?.session_id? INNER?JOIN?sys.?dm_tran_session_transactions?st ON?Blocking?.session_id?=?st?.session_id LEFT?OUTER?JOIN?sys.dm_exec_requests?er ON?st?.session_id?=?er?.session_id AND?er?.session_id?IS?NULL INNER?JOIN?sys.?dm_os_waiting_tasks?AS?Waits ON?Blocked?.session_id?=?Waits?.session_id CROSS?APPLY?sys.?dm_exec_sql_text(Blocking?.most_recent_sql_handle)?AS?BlockingSQL INNER?JOIN?sys.?dm_exec_requests?AS?BlockedReq???????????????????? ON?Waits?.session_id?=?BlockedReq?.session_id INNER?JOIN?sys.?dm_exec_sessions?AS?BlockedSess ON?Waits?.session_id?=?BlockedSess?.session_id CROSS?APPLY?sys.?dm_exec_sql_text(Blocked?.sql_handle)?AS?BlockedSQL ORDER?BY?WaitInSeconds

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!