SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Test query that will use multiple threads.


Test query that will use multiple threads.

Author
Message
kevin.unglesbee
kevin.unglesbee
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 242
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!
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10585 Visits: 7891
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

  • kevin.unglesbee
    kevin.unglesbee
    Mr or Mrs. 500
    Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

    Group: General Forum Members
    Points: 538 Visits: 242
    Thanks, this does exactly what I was looking for.
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10585 Visits: 7891
    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

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search