Notes

SET STATISTICS IO ON
SET STATISTICS TIME ON

select EtRegMaster_idx,Guarantyno,Grdcrtno,Grdcrttype
from EtRegLMkat26s where EtRegMaster_idx=@EtRegMaster_idx and Guarantyno=@Guarantyno and Grdcrttype='C '

select (SELECT TOP 1 s2.text) as 'SQL語法'
,a.spid as '工作階段識別碼',a.loginame as '登入',b.name as '資料庫'
,a.cmd as '命令',a.waittime as '等候時間(毫秒)',a.lastwaittype as '等候類型'
,a.hostname as '主機名稱'
,a.login_time as '發生時間'
,'Kill ' + cast(a.spid as nvarchar) as '刪除語法'
from master..sysprocesses a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
JOIN master.dbo.sysdatabases as b on b.dbid=a.dbid
where lastwaittype='ASYNC_NETWORK_IO'
ORDER by login_time


SELECT CreateUnit,COUNT(*) FROM PayOnline_PointManager.dbo.ForTranscript
group by CreateUnit
ORDER BY COUNT(*) DESC


SELECT DATEPART(YEAR,CreateDate),DATEPART(MONTH,CreateDate),COUNT(*) FROM PayOnline_PointManager.dbo.ForTranscript
where CreateUnit='YC_AC006' AND ApplyOK='1' AND ReplyOK='1'
group by DATEPART(YEAR,CreateDate),DATEPART(MONTH,CreateDate)

select * from sys.sysprocesses
Select count(distinct hostname) from master..sysprocesses


select DB_ID(N'Drama')

SELECT * FROM master.dbo.sysprocesses WHERE dbid IN ( SELECT * FROM master.dbo.sysdatabases WHERE NAME='Drama')

--查看當前連接數
SELECT COUNT(*) AS Expr1, hostname
FROM master.dbo.sysprocesses
WHERE (dbid = DB_ID(N'Drama'))
GROUP BY hostname
--查看總連接數
select
count(*)
from master..sysprocesses
where hostname<>'' and dbid=db_id('Drama')

--查看Lok編號
IF object_id('tempdb..#tmp') IS NOT NULL
BEGIN
DROP TABLE #tmp
END

CREATE TABLE #tmp (
[Sp_ID] NVARCHAR(50)
,[Db_ID] NVARCHAR(50)
,[Obj_ID] NVARCHAR(50)
,[Ind_ID] NVARCHAR(50)
,Type NVARCHAR(50)
,Resource NVARCHAR(50)
,Mode NVARCHAR(50)
,Status NVARCHAR(50)
)

INSERT INTO #tmp EXEC sp_lock
SELECT st.lastwaittype,tt.[Sp_ID],st.SqlNote,ss.* FROM sysobjects as ss
join (select Obj_ID,max(Sp_ID) as [Sp_ID] from #tmp group by Obj_ID) as tt on ss.id=tt.Obj_ID
join (
select (SELECT TOP 1 s2.text) AS SqlNote,a.spid,a.lastwaittype
from master..sysprocesses a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
) as st on st.spid=tt.[Sp_ID]
--刪除Lock SPID
--kill 153
KILL 153 WITH STATUSONLY;
KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF';
--查看語法
DBCC INPUTBUFFER(153)

select (SELECT s2.text) AS sql_statement,a.* ,s2.*
from master..sysprocesses a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
where spid=88

--資料庫正在執行的 T-SQL 指令與詳細資訊
SELECT r.scheduler_id as 排程器識別碼,
status as 要求的狀態,
r.session_id as SPID,
r.blocking_session_id as BlkBy,
substring(
ltrim(q.text),
r.statement_start_offset/2+1,
(CASE
WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2)
AS [正在執行的 T-SQL 命令],
r.cpu_time as [CPU Time(ms)],
r.start_time as [開始時間],
r.total_elapsed_time as [執行總時間],
r.reads as [讀取數],
r.writes as [寫入數],
r.logical_reads as [邏輯讀取數],
-- q.text, /* 完整的 T-SQL 指令碼 */
d.name as [資料庫名稱]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time desc




IF EXISTS(SELECT * FROM master.dbo.sysprocesses WHERE spid in (SELECT blocked FROM master.dbo.sysprocesses))
BEGIN
SELECT spid 處理序, status 狀態
, 登入帳號=SUBSTRING(SUSER_SNAME(sid), 1, 30)
, 使用者機器名稱=SUBSTRING(hostname, 1, 12)
, 是否鎖住=CONVERT(char(3), blocked)
, 資料庫名稱=SUBSTRING(DB_NAME(dbid), 1, 10)
, cmd 命令, waittype 等待類型
FROM master.dbo.sysprocesses
--找出鎖住別(自己未被鎖住(blocked=0) 但在別的處理序中blocked欄位出現的值)
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
END
ELSE
BEGIN
SELECT '沒有處理序被鎖住'
END


SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName
FROM SYS.DM_TRAN_LOCKS
WHERE resource_type='OBJECT'






SELECT TOP 10
wait_type, waiting_tasks_count,
( wait_time_ms - signal_wait_time_ms ) as resource_wait_time,
max_wait_time_ms,
case when waiting_tasks_count = 0 then 0
else wait_time_ms / waiting_tasks_count
end as avg_wait_time
FROM sys .dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN (
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH','KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT', 'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE','ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH' , 'LOGMGR_QUEUE', 'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS'
)
ORDER BY wait_time_ms DESC