SQL Server Performance - Block, SQL, Open Trans, Lock

針對 SQL Server，我不是 DBA，但是還是可以透過系統的資訊查詢到可能有問題的節點。

先檢查 DB Server CPU, RAM, I/O的執行狀況，離峰與尖峰的時段的情況，有 Whatsup可用的話，定時捉取 Server資訊比較方便。


查詢那些 SQL秏用的時間比較多，可以用下列 SQL 查詢，最主要看的是每次平均執行最長的前 20，看是否再作 SQL Tuning或是建立 Index方式可以改善

select * from (SELECT TOP 20
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 ) as statement_text ,
total_worker_time / 1000000.0 as total_worker_time_second,
execution_count,
( total_worker_time / 1000000.0 ) / execution_count as avg_worker_time_second,
total_logical_reads,
total_logical_reads / execution_count as avg_logical_reads,
qp.query_plan ,creation_time,last_execution_time
,CONVERT(varchar(max),sql_handle,2) as sql_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle ) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY avg_worker_time_second DESC
) a


查詢 Block資訊，看有那些 process 造成塞車，要把源頭解決，這部份 SQL Server有標準報表，而我是寫一隻 procedure定期執行，可以把當下的情況記錄下來，如果有一隻 process一直塞成塞車的情況，就可以大約知道塞了多久，前不久才發現有人使用 TOAD for SQL Server可以造成 block的狀況，而且有 2個多小時。不知道是人的問題還是 TOAD的問題

SELECT 0 as spid ,blocked
    FROM ( SELECT * FROM master..sysprocesses WHERE blocked > 0 ) a
    WHERE NOT EXISTS ( SELECT * FROM master..sysprocesses WHERE a.blocked = spid AND blocked > 0 )
    UNION
    SELECT spid ,blocked
    FROM master..sysprocesses
    WHERE blocked > 0;


查詢 process 狀況，每一個 connection 連線狀況，如果是 client/server的軟体連線到 DB上，當一個使用者使用系統時，在 DB的部份就可能會發現一次就會好幾個 process產生，最主要這邊要看是否有 open_tran >= 1，執行很久的就要注意，尤其 status  還是 sleeping的就是馬上要解決的 process，看是那一個程式連線到 DB的可能要關掉

select spid,loginame,hostname,program_name
, login_time,last_batch ,text,open_tran, blocked
 ,CONVERT(varchar(max),sql_handle,2) as sql_handle
,status,Cmd,s.DBID
from master.sys.sysprocesses s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle)


查詢 Lock情況，找出 resource_mode為 X，這部份要先確認 process的執行狀況，正常的情況是 process open_tran = 0，lock就會消失，所以先解決 process的問題後都沒有辦法，再來作 kill process的動作

select * from (SELECT request_session_id AS spid,
    resource_type AS rt,
    resource_databASe_id AS rdb,
    (CASE resource_type
    WHEN 'OBJECT' then object_name(resource_ASsociated_entity_id)
    WHEN 'DATABASE' then '<db_name>'
    ELSE
    (SELECT object_name(object_id) FROM sys.partitions
        WHERE hobt_id = resource_ASsociated_entity_id) END) AS objname,
    resource_description AS rd,
    request_mode AS rm,
    request_status AS rs
FROM sys.dm_tran_locks ) a
where  1=1