標籤：offset lda and 拷貝 statistic max apply load request
查看系統日誌select * from sys.traces
查看sql profile轉儲檔案
- 先要用sql profile監控資料庫,匯出成trc
- 拷貝至資料庫伺服器上，eg.d:\sqldata\trace\201611290881.trc
- sql查詢中如下語句查看
--SELECT cpu,TextData,Reads,Writes
SELECT *
FROM ::fn_trace_gettable(‘d:\sqldata\trace\201611290881.trc‘,default)
WHERE SPID > 50
and StartTime > ‘2016-11-29 11:30:00.000‘
AND cpu > 100ORDER 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_planFROM 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 qpORDER 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
常用sqlserver效能分析
