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: Monday, July 7, 2014 5:38 AM
Points: 473, Visits: 179
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: Today @ 11:38 AM
Points: 1,780, Visits: 5,655
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


  • MMGrid Addin
  • MMNose Addin


  • 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: Monday, July 7, 2014 5:38 AM
    Points: 473, Visits: 179
    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: Today @ 11:38 AM
    Points: 1,780, Visits: 5,655
    kevin.unglesbee (10/21/2013)
    Thanks, this does exactly what I was looking for.


    You're most welcome


    MM


  • MMGrid Addin
  • MMNose Addin


  • 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