[轉帖]召冠總的 SQLSERVER常用的效能診斷語句. --儲存學習備查

標籤：語句   hat   序列   inner   check   case   table   imp   text   

CopyFrom https://www.cnblogs.com/zhaoguan_wang

/*
常規伺服器動態管理對象包括：
dm_db_*：資料庫和資料庫物件
dm_exec_*：執行使用者代碼和關聯的串連
dm_os_*：記憶體、鎖定和時間安排
dm_tran_*：事務和隔離
dm_io_*：網路和磁碟的輸入/輸出
*/

--- 運行下面的 DMV 查詢以查看 CPU、排程器記憶體和緩衝池資訊。
select
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info

 

--- 高I/O開銷的查詢 Identifying Most Costly Queries by I/O
SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
, [Total IO] = (total_logical_reads + total_logical_writes)
, [Execution count] = qs.execution_count
, [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(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)
,[Parent Query] = qt.text
, DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

 

--- 高CPU開銷的查詢 Identifying Most Costly Queries by CPU
SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
, [Total CPU used] = total_worker_time
, [Execution count] = qs.execution_count
, [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(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)
, [Parent Query] = qt.text
, DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

 

--- 高開銷的缺失索引 Cost of Missing Indexes
SELECT TOP 10
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

 

--- 最常執行的查詢 Identifying Queries that Execute Most Often
SELECT TOP 10
[Execution count] = execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(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)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;

 

 

--- 重複編譯的查詢（plan_generation_num 指示該查詢已重新編譯的次數）
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc

 

 

--- 伺服器等待的原因 SQL Query Records Causes of Wait Times
SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE ‘%SLEEP%‘
ORDER BY wait_time_ms DESC;

 

--- 讀和寫 Identifying the Most Reads and Writes
SELECT TOP 10
[Total Reads] = SUM(total_logical_reads)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;

SELECT TOP 10
[Total Writes] = SUM(total_logical_writes)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;

 

--- 運行下面的 DMV 查詢以尋找 I/O 閂鎖等候統計資訊。
select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats
where wait_type like ‘PAGEIOLATCH%‘ and waiting_tasks_count > 0
order by wait_type

 

-- 查看SQL阻塞資訊
with tmp as (
select * from master..sysprocesses t where t.blocked != 0
union all
select b.* from master..sysprocesses b
join tmp t on b.spid = t.blocked
)
select t.spid, t.blocked, t.status, t.lastwaittype, t.waitresource, t.waittime
, DB_NAME(t.dbid) DbName, t.login_time, t.loginame, t.program_name, dc.text
from (select spid from tmp group by spid) s
join master..sysprocesses t on s.spid = t.spid
cross apply master.sys.dm_exec_sql_text(t.sql_handle) dc

--kill 53;

 

-- 查看所有會話的狀態、等待類型及當前正在執行SQL指令碼
select t.spid, t.kpid, t.blocked, t.status, t.lastwaittype, t.waitresource, t.waittime
, DB_NAME(t.dbid) DbName, t.last_batch, t.loginame, t.program_name, t.hostname, t.hostprocess
, t.cmd, t.stmt_start, t.stmt_end, t.request_id, dc.text
from master.sys.sysprocesses t
outer apply master.sys.dm_exec_sql_text(t.sql_handle) dc
where t.spid >= 50

select s.spid, s.kpid, s.blocked, s.hostname, s.hostprocess, s.program_name, s.loginame
, s.status, s.lastwaittype, s.waitresource, s.waittime
, t.transaction_id, t.name, t.transaction_begin_time, dc.text
from sys.sysprocesses s
join sys.dm_tran_session_transactions st on s.spid = st.session_id
join sys.dm_tran_active_transactions t on st.transaction_id = t.transaction_id
outer apply master.sys.dm_exec_sql_text(s.sql_handle) dc

 

---補充，查看所有會話當前持有和申請的鎖資源(選擇在特定的業務庫執行，測試類比，建議將隔離等級改為可重複讀)

set transaction isolation level repeatable read

select l.request_session_id,
l.resource_type,
l.resource_subtype,
l.request_status,
l.request_mode,
l.resource_description,
db_name(l.resource_database_id) as dbName,
case l.resource_type
when ‘database‘ then DB_NAME(l.resource_database_id)
when ‘object‘ then object_name(l.resource_associated_entity_id)
else OBJECT_NAME(p.object_id)
end as obj_name,
p.index_id,
l.request_lifetime
from sys.dm_tran_locks l
left join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
order by l.request_session_id, l.resource_type

---查看所有會話的 找到活動事務對應的執行語句
select dc.session_id,
ds.login_name,
ds.login_time,
dc.connect_time,
dc.net_transport,
dc.client_net_address,
ds.host_name,
ds.program_name,
case ds.status when ‘sleeping‘ then ‘睡眠 - 當前沒有運行任何請求 ‘
when ‘running‘ then ‘正在運行 - 當前正在運行一個或多個請求 ‘
when ‘Dormancy‘ then ‘休眠 – 會話因串連池而被重設，並且現在處於登入前狀態‘
when ‘Pre-connected‘ then ‘預串連 - 會話在資源管理員分類器中‘
end as status ,
ds.cpu_time as cpu_time_ms,
ds.memory_usage*8 as memory_kb,
ds.total_elapsed_time as total_elapsed_time_ms,
case ds.transaction_isolation_level when 0 then ‘未指定‘
when 1 then ‘未提交讀取‘
when 2 then ‘已提交讀取‘
when 3 then ‘可重複‘
when 4 then ‘可序列化‘
when 5 then ‘快照‘
end ‘會話的交易隔離等級‘,
dt.text
from sys.dm_exec_connections dc --執行串連，最近執行的查詢資訊
cross apply sys.dm_exec_sql_text(dc.most_recent_sql_handle) dt
join sys.dm_exec_sessions ds on dc.session_id=ds.session_id
where ds.login_name= ‘LCGS609999‘
--where ds.program_name = ‘.Net SqlClient Data Provider‘
ORDER BY dt.text
複製代碼

複製代碼
--清除buffer pool裡的所有快取資料
DBCC DROPCLEANBUFFERS
GO
--清除buffer pool裡的所有緩衝的執行計畫
DBCC FREEPROCCACHE
GO

 

--統計資訊、執行計畫

SET STATISTICS IO ON
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON

-- 檢查分析死結資訊

1、使用sql profiler 抓取死結鏈及圖
2、跟蹤分析SQL ErrorLog日誌

dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go

參考資料：
http://support.microsoft.com/zh-cn/kb/832524

 

 

-- 檢查分析閂鎖等阻塞資訊

WHILE 1=1
BEGIN
EXEC master.dbo.sp_blocker_pss80 -- or sp_blocker_pss08
-- Or for fast mode
-- EXEC master.dbo.sp_blocker_pss80 @fast=1
-- Or for latch mode
-- EXEC master.dbo.sp_blocker_pss80 @latch=1
WAITFOR DELAY ‘00:00:15‘
END
GO

cmd執行命令：
osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000

參考資料：
http://support.microsoft.com/zh-cn/kb/271509

揭開隱藏資料的面紗，最佳化應用程式效能
https://msdn.microsoft.com/zh-cn/magazine/cc135978.aspx

 

[轉帖]召冠總的 SQLSERVER常用的效能診斷語句. --儲存學習備查