UMS Scheduler question

  • Hi .

     

    We all know what the UMS schedulers task in sql server is ...I have a question though ..

    How can I see whet spid is assigned to what schedulerid  ......

    Using Dbcc sqlperf(Umsstats) I can see the thread statistics of SQL server per CPU ...but how do I know what process (Spid) is currently using what CPU for SQL processing ?

  • I hope with SQL 2000 you can't get this information. But the same can be obtained with SQL 2005 using DMV's.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Hi

     

    Thanks for the reply .

     

    What do you mean by " I hope with SQL 2000 you can't get this information "

    I realy need this on 2000 .....helps when you have 16 cpu's and one is flatlining at 90 % ....excluding the possiblity that the CPU is damaged ...and all the other posiibilities ......

  • You will not be able to get what you desire but you can get the processor usage stats using perfmon. You can't find which CPU is being used for which query.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • I know what tools I can use   

    ...I was looking for something deeper ..to realy get into it from a thread perspective ..but if there isn't then I find that is a huge problem .....because if SQL assignes a worker thread to your spid when you processes and the worker thread is assigned to a schedulerid and the schedulerID is assigned to a CPU then surely there must be  a way to map this out ..

     

    But in anyway ..thanks for the only response I got much appreciated.

     

    Wynand

  • Sure there should be a way to check this but MS has kept it internal and does not reveal to the user. But in 2005 you can get these details so check if those help for you to find in sql 2000.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Hi CoetzeeW,

    There's an undocumented command in SQL2K to tell you which scheduler is processing your spid.

    First of all, get the spid that you're interested in. Run the command below in your Query Analyzer

    DBCC TRACEON (3604)

    go

    dbcc pss(0, <<spid>>, 1)

    go

    DBCC TRACEOFF

    go

    At the result pane, look for (SchedulerId). It'll look something like 0x2, meaning this spid is being assigned to scheduler 2.

    SQL2K is a black box, but because its been around for so long, people found other way to work around some of the stuffs.

    Simon

     

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • Thanks learnt that this can be done using undocumented DBCC. The given below is alist of few

    http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocDBCC.htm#part_2_12

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Thanks for the reply guys ...I always like the undocumented commands

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply