Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Test query that will use multiple threads. Expand / Collapse
Author
Message
Posted Friday, October 18, 2013 11:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 2, 2015 1:47 PM
Points: 474, Visits: 229
Hello everyone,

Short Version:
What I need is a query that will use multiple threads, thereby creating multiple records in the sys.sysprocesses view.

Long Version:
I am working on a login alert for our systems and need some help recreating a condition causing me problems. Basically, I am working with the sys.sysprocesses system view. This gives back one record per thread. So this means that 99.99% of the time (or at least that's how it seems to me), it will return 1 and only 1 record per SPID. However, sometimes it will return more than 1 record per SPID, all with a different KPID. I have come to understand that the KPID is the thread number. I need some code that will create multiple records in the sysprocesses view while it is running (and it has to run long enough so that I can actually catch it).

All of the records where there is only 1 record per SPID seem to have a KPID of 0. I want to find out if there is always at least one record per SPID with a KPID of 0 or if when there are multiple threads running, the KPID of 0 may no longer exist. Sometimes (or maybe all the time?) when there are multiple records per SPID in this table, some of the records will not contain all the data I need for my login audit (ie. loginame is blank). So if I can find a query which creates multiple threads, I think it should also make multiple records in the sysprocesses view and I will be able to test its behavior and see what is going on with this missing loginame.

Thanks in advanced for your help!
Post #1506279
Posted Friday, October 18, 2013 4:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:02 PM
Points: 1,964, Visits: 6,603
with ten(N) as
(select ROW_NUMBER() over(order by @@spid) from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(a))
,hundred(N) as
(select ROW_NUMBER() over(order by @@spid) from ten t1,ten t2)
,thousand(N) as
(select ROW_NUMBER() over(order by @@spid) from ten t1,hundred h2)
,million(N) as
(select ROW_NUMBER() over(order by @@spid) from thousand t1,thousand t2)
select a.N
from million a , million b

That should do it, but if not, just add another cross join

You will need to have a global maxdop>1 of course.


MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw
  • Post #1506342
    Posted Monday, October 21, 2013 5:31 AM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Tuesday, June 2, 2015 1:47 PM
    Points: 474, Visits: 229
    Thanks, this does exactly what I was looking for.
    Post #1506635
    Posted Monday, October 21, 2013 8:22 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 6:02 PM
    Points: 1,964, Visits: 6,603
    kevin.unglesbee (10/21/2013)
    Thanks, this does exactly what I was looking for.


    You're most welcome


    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw
  • Post #1506700
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse