SQL server 查找会话
sp_who
sp_who2
use master
GO
select * from sysprocesses (nolock) where blocked = 0 and spid in (
select blocked from sysprocesses (nolock) where blocked <> 0
)
GO
sql2000 或2005中查找运行时间长的spid 进程
select
P.spid
, right(convert(varchar,
dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'),
121), 12) as 'batch_duration'
, P.program_name
, P.hostname
, P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and P.status not in ('background', 'sleeping')
and P.cmd not in ('AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
--查看相应的结果
declare
@spid int
, @stmt_start int
, @stmt_end int
, @sql_handle binary(20)
set @spid = XXX -- Fill this in
select top 1
@sql_handle = sql_handle
, @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
, @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from master.dbo.sysprocesses
where spid = @spid
order by ecid
SELECT
SUBSTRING( text,
COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(text)
ELSE
(@stmt_end - @stmt_start)
END
)
FROM ::fn_get_sql(@sql_handle)
使用DMV查询
SELECT
p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, r.command,
p.program_name, text ,r.total_elapsed_time,b.blocking_session_id,r.cpu_time
FROM
sys.dm_exec_requests AS r,
master.dbo.sysprocesses AS p
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE
p.status NOT IN ('sleeping', 'background')
AND r.session_id = p.spid
kill 会话
Kill @spid
使用脚本自动kill
use master
GO
CREATE PROC support_KillBlockingProcesses (@RecursiveCount int = NULL)
AS
DECLARE @count int, @spid int, @sql nvarchar(max)
SET @count = ISNULL(@RecursiveCount, 3)
while @count > 0
begin
begin try
set @spid = (
select top 1 spid from sysprocesses (nolock)
where blocked = 0 and spid in (
select blocked from sysprocesses (nolock) where blocked <> 0
)
)
if @spid > 50
begin
set @sql = N'kill ' + cast(@spid as nvarchar(100))
exec sp_executesql @sql
--print @sql
end
end try
begin catch
--print 'error'
end catch
set @count = @count - 1
end
GO
----------------
--Usage: ListBlocking [@KillOrphanedProcesses = 0]
--If @KillOrphanedProcesses is set to 1 then the script will attempt to kill orphaned processes.
--https://www.codeproject.com/tips/267037/list-blocking-processes-in-sql-server
------------------
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ListBlocking]')
AND type in (N'P', N'PC'))
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ListBlocking] AS'
GO
/*===================================================================
Description: Wrapper to sp_who2 to show only those processes that
are blocking.
Please see http://support.microsoft.com/kb/224453 for
more info on locking.
===================================================================*/
ALTER procedure ListBlocking
(
@KillOrphanedProcesses bit = 0
)
as
IF OBJECT_ID('tempdb..#Process') IS NOT NULL drop table #Process
IF OBJECT_ID('tempdb..#BlockingProcess') IS NOT NULL drop table #BlockingProcess
create table #Process (
SPID int,
Status varchar(500),
Login varchar(500),
Hostname varchar(500),
BlkBy varchar(50),
DBName varchar(500),
Command varchar(500),
CPUTime int,
DiskIO int,
LastBatch varchar(500),
ProgramName varchar(500),
SPID2 int,
RequestId int
)
CREATE TABLE #BlockingProcess (
BlockingProcessID int IDENTITY(1,1),
ProcessID varchar(20),
EventType varchar(100),
Parameters varchar(100),
EventInfo varchar(500),
CPUTime int,
DiskIO int,
TransactionCount int
)
DECLARE @BlockingPID varchar(20),
@CPUTime int,
@DiskIO int,
@TransactionCount int
-- Let SQL Server get us a list of what's going on
SET NOCOUNT ON
insert into #Process exec sp_who2
-- From this list of what's going on, get those items that are blocked, and loop through them
DECLARE ProcessCursor CURSOR FAST_FORWARD FOR
SELECT BlkBy, SUM(CPUTime) as CPUTime, SUM(DiskIO) as DiskIO
FROM #Process
WHERE ISNULL(BlkBy,'') <> ''
GROUP BY BlkBy
OPEN ProcessCursor
FETCH NEXT FROM ProcessCursor INTO @BlockingPID, @CPUTime, @DiskIO
WHILE @@FETCH_STATUS = 0
BEGIN
-- Only valid PIDs
if ISNULL(@BlockingPID, '') <> '' and @BlockingPID <> '0' AND @BlockingPID <> ' .'
BEGIN
-- For each blocked process get what information we can on the process and what's blocking it
SET @TransactionCount = 0
-- -2 = The blocking resource is owned by an orphaned distributed transaction.
IF SUBSTRING(@BlockingPID, 1, 2) = '-2'
BEGIN
-- For orphaned processes we need to get the Unit of work if we're to do anything with it
DECLARE @UnitOfWork varchar(50)
select top 1 @UnitOfWork = ISNULL(req_transactionUOW, '')
from master..syslockinfo
where req_spid = -2
if @KillOrphanedProcesses = 1
BEGIN
if @UnitOfWork <> '' exec('KILL ''' + @UnitOfWork + '''')
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
VALUES('', '', '- Killed UOW ' + @UnitOfWork + ' -')
END
ELSE
BEGIN
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
VALUES('', '', '- Orphaned. UOW = ' + @UnitOfWork + ' -')
END
END
-- -3 = The blocking resource is owned by a deferred recovery transaction.
ELSE IF SUBSTRING(@BlockingPID, 1, 2) = '-3'
BEGIN
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
VALUES('', '', '- deferred recovery transaction -')
END
-- -4 = Session ID of the blocking latch owner could not be determined at this
-- time because of internal latch state transitions.
ELSE IF SUBSTRING(@BlockingPID, 1, 2) = '-4'
BEGIN
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
VALUES('', '', '- Latch owner could not be determined -')
END
-- Nothing unusual here. A process is being blocked by another processes, so let's get the
-- info on the process that's doing the blocking
ELSE
BEGIN
SELECT @TransactionCount = open_tran FROM master.sys.sysprocesses WHERE SPID=@BlockingPID
INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
EXEC ('DBCC INPUTBUFFER(' + @BlockingPID + ') WITH NO_INFOMSGS')
END
-- Add some aggregate info on the blocking processes
UPDATE #BlockingProcess
SET ProcessID = @BlockingPID,
CPUTime = @CPUTime,
DiskIO = @DiskIO,
TransactionCount = @TransactionCount
WHERE BlockingProcessID = SCOPE_IDENTITY()
END
FETCH NEXT FROM ProcessCursor INTO @BlockingPID, @CPUTime, @DiskIO
END
CLOSE ProcessCursor
DEALLOCATE ProcessCursor
-- Display the results
SELECT ProcessID, EventInfo, TransactionCount, CPUTime, DiskIO FROM #BlockingProcess
SET NOCOUNT OFF
refer
http://www.kodyaz.com/articles/identify-kill-blocking-sql-server-processes.aspx