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

使用 group by 或是利用 partition by來取得資料

目前有一個需求，希望可以取得某一段區間的訂單資料，並用公司、客戶、產品型號、幣別、月份作 Group By，來計算產品數量，還有在這一期間內該產品的最新單價作為下年度的預估單價，而最新單價的部份用 Group By的方式如下：


select  company,curr_code,cust_code, prod_num,max(price)
 from ( select a.Fa02 'company'
 ,isnull(a.Fa13, 'NT$') 'curr_code'
 ,a.Fa061 'cust_code'
 ,c.Fb04 'prod_num'
 ,c.Fb23 'order_lline_date'
 , isnull(c.Fb14,0) 'price'

from DATAWIN.dbo.kfm01 a
,DATAWIN.dbo.kfm02 c
where 1=1
and a.Fa01 = c.Fb01
and isnull(Fa124,'N') = 'N'
and LEFT(c.Fb23 ,6) >= '201301'
and LEFT(c.Fb23 ,6) <= '201312'

and c.Fb23 = (select max(Fb23) from  DATAWIN.dbo.kfm01 e,DATAWIN.dbo.kfm02 f
where 1=1
and e.Fa01 = f.Fb01
and isnull(e.Fa124,'N') = 'N'
and LEFT(f.Fb23 ,6) >= '201301'
and LEFT(f.Fb23 ,6) <= '201312'
and a.Fa02 = e.Fa02
and a.Fa13 = e.Fa13
and a.Fa061 = e.Fa061
and c.Fb04 = f.Fb04
             )
 ) a
 group by company,curr_code,cust_code, prod_num ;

上面的作法應該是大家常使用的，不過還有另一種作法是使用 ROW_NUMBER() OVER (PARTITION BY ORDER BY)的方式來達成，作法如下，寫法比 Group By還簡單，可以參考看看。