DBCC SQLPERF(THREADS) returns a list of the currently running "threads" in the system, the information is equivalent to what is exposed via the sysprocesses table. The following query of sysprocesses provides an identical and better result set.

select   [Spid] = spid,
         [Thread ID] = kpid,
         [Status] = convert(varchar(10), status),
         [LoginName] = convert(varchar(10), loginame),
         [IO] = physical_io,
         [CPU] = cpu,
         [MemUsage] = memusage
from     [master].[dbo].[sysprocesses]
order by [spid]

The result set:

Spid   Thread ID   Status     LoginName  IO                     CPU         MemUsage
------ ----------- ---------- ---------- ---------------------- ----------- -----------
1      NULL        sleeping   NULL       0                      0           0
2      NULL        background NULL       0                      0           5
3      NULL        background NULL       0                      0           -17
4      NULL        background NULL       0                      0           0
5      0           background sa         27                     0           3
6      0           background sa         8                      0           3
7      0           background sa         0                      0           3
8      0           background sa         0                      0           3
9      NULL        sleeping   NULL       17                     0           0
10     0           background sa         0                      0           3
11     0           background sa         0                      0           3
12     0           background sa         0                      0           3
13     0           background sa         0                      0           3
14     0           background sa         0                      0           3
15     0           background sa         0                      0           3
16     0           background sa         0                      0           3
51     0           sleeping   GERTD00\AS 10                     523         20
52     0           sleeping   SDN\gertd  0                      0           16
53     0           sleeping   NT AUTHORI 4                      0           4
54     0           sleeping   NT AUTHORI 2                      40          11
55     0           sleeping   NT AUTHORI 227                    380         46
56     1568        runnable   NT AUTHORI 2                      0           5
57     5532        runnable   SDN\gertd  1                      40          39
59     0           sleeping  NT AUTHORI  0                      0           6
60     0           sleeping  NT AUTHORI  0                      60          6
61     0           sleeping  SDN\gertd   8                      211         68
62     0           sleeping  SDN\gertd   0                      60          31
 

Description of the columns:

The table structure returned, has the following shape:

create table #threads
(
    [Spid] smallint not null,
    [Thread ID] int not null,
    [Status] nvarchar(10) not null,
    [Login Name] nvarchar(10) null,
    [IO] bigint not null,
    [CPU] int not null,
    [MemUsage] int not null
)

To populate the table use:

insert into #threads exec('dbcc sqlperf(threads) with tableresults, no_infomsgs')
select * from #threads order by [Spid]

NOTE: DBCC SQLPERF(THREADS, CLEAR) has no effect since the THREADS option is not based on cumulative statistical data.

Questions or problems regarding this web site should be directed to Web Master.
Copyright © 1991-2005 SQLDev.Net. All rights reserved.
Last modified: 04/06/05.