UMS Scheduler question

Question

Post reply

UMS Scheduler question

CoetzeeW

Default port

Points: 1411
More actions
July 12, 2007 at 3:51 am

#73213

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 ?

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

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

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

CoetzeeW Default port Points: 1411 More actions · Answer 2

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 ......

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

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

CoetzeeW Default port Points: 1411 More actions · Answer 4

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

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

SimonLiew SSCrazy Eights Points: 8295 More actions · Answer 6

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 7

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

CoetzeeW Default port Points: 1411 More actions · Answer 8

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