March 30, 2006 at 4:46 pm
Sometimes when our SQL Server 2005 database server is under heavy load, often when there is some blocking going on, one will see duplicate spids in sp_who results. Shockingly, a direct query of master..sysprocesses also returns multiple rows with the same spid.
This seems a little odd to me. I think I might have seen it once or twice, with a single row of duplication, on our SQL Server 7 instance. I chalked it up to it catching it mid-change. Now, there are many, many rows of duplication and they stick around through multiple sp_who2 executes.
Again, there are multiple rows with the same spid, but different "Status"es, CPU usage, etc. Most of the dupes have a blank "login" but the same "hostname". I've even seen some cases where some will be blocked and others not. And, though I couldn't really believe my eyes, I think I've even seen sometimes where the spid is blocking itself (some of the rows have the duplicated spid as the blocked by).
Below is some pasted sp_who showing a tame version of this issue (no blocking, e.g.). I'm sure it will be mangled by pasting it in here, but it's as much "proof" as I can offer.
We're running SQL Server 2005 Standard x64 on Windows 2003 Server Standard x64.
Any ideas? Am I nuts? Should I provide more examples?
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
87 SUSPENDED gqwebuser INET1 . LIFT SELECT 109 2 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 32 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 32 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 32 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 32 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 16 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 47 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 47 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 47 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 47 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 46 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 47 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 47 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 15 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 32 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 47 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 31 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 32 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 15 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 16 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 16 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 16 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 16 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 16 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 SUSPENDED INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 RUNNABLE INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 RUNNABLE INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 RUNNABLE INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 RUNNABLE INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 RUNNABLE INET1 . LIFT SELECT 0 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 RUNNABLE INET1 . LIFT SELECT 16 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 RUNNABLE INET1 . LIFT SELECT 16 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
87 RUNNABLE INET1 . LIFT SELECT 16 0 03/30 16:55:13 Microsoft(R) Windows (R) 2000 Operating System 87 0
March 31, 2006 at 7:35 am
I get the same thing at times. Its almost always from a webserver started query, and it shows when a poorly crafted search is executed by a user/ program and is returning or searching tens or hundreds of thousands of rows. In my case it manifests as either searching for several years worth of records, or searching for values that are all not indexed (table scans).
If you are running profiler and logging long running queries (greater than 2000ms/ 2 seconds) you should see these beasts and should try to make sure your index schema is structured to accomodate.
If you are having blocking problems or performance complaints, try running this procedure every so often. Its a variation of sp_who2 but you dont have to specify 'active', and it gives you the duration of each spid so you can see what might be causing the problem and "KILL <spid>" the process before it affects other connections too much.
CREATE PROCEDURE sp_what --- 2006/3/22
-- @loginame sysname = NULL
as
set nocount on
declare
@retcode int, @loginame sysname
declare
@sidlow varbinary(85),@sidhigh varbinary(85) ,@sid1 varbinary(85) ,@spidlow int ,@spidhigh int
declare
@charMaxLenLoginName varchar(6),@charMaxLenDBName varchar(6),@charMaxLenCPUTime varchar(10),@charMaxLenDiskIO varchar(10),
@charMaxLenHostName varchar(10),@charMaxLenProgramName varchar(10),@charMaxLenLastBatch varchar(10),@charMaxLenCommand varchar(10)
--------
select @retcode = 0 -- 0=good ,1=bad.
--------------------------------------------------------------
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
-------------------- Capture consistent sysprocesses. -------------------
SELECT
spid,status,sid,hostname,program_name,cmd,cpu,physical_io,blocked,dbid,
convert(sysname, rtrim(loginame)) as loginname,spid as 'spid_sort',
substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char',last_batch
INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
--------Screen out any rows
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
,'TASK MANAGER'
)
and blocked = 0 or spid <= 50
---set the column widths
UPDATE #tb1_sysprocesses set last_batch = DATEADD(year,-10,GETDATE())
where last_batch IS NULL or last_batch = '01/01/1901 00:00:00' or last_batch < '01/01/1950'
update #tb1_sysprocesses set status = substring(status,1,10), program_name = substring(program_name,1,20)
ALTER TABLE #tb1_sysprocesses
ALTER COLUMN status varchar(10)
ALTER TABLE #tb1_sysprocesses
ALTER COLUMN program_name varchar(20)
--------Prepare to dynamically optimize column widths.
SELECT
@charMaxLenLoginName = convert( varchar ,isnull( max( datalength(loginname)) ,5)),
@charMaxLenDBName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)),
@charMaxLenCPUTime = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)),
@charMaxLenDiskIO = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)),
@charMaxLenCommand = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)),
@charMaxLenHostName = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)),
@charMaxLenProgramName = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)),
@charMaxLenLastBatch = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9))
from
#tb1_sysprocesses
where
spid >= 0 and spid <= 32767
--------Output the report.
EXECUTE(
'SET nocount off
SELECT SPID = convert(char(5),spid)
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END
,ActiveSeconds = DATEDIFF(ss,last_batch,getdate())
,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command = substring(cmd,1,' + @charMaxLenCommand + ')
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,BatchStart = CONVERT(varchar(8),last_batch,14)
,Now = CONVERT(varchar(8),getdate(),14)
,LBDate = substring(last_batch_char,1,5)
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
from
#tb1_sysprocesses --Usually DB qualification is needed in exec().
order by CAST(SPID as int)
-- (Seems always auto sorted.) order by SPID
SET nocount on')
drop table #tb1_sysprocesses
--return @retcode -- sp_whoBLK
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply