sys.sysprocesses Versus sys.dm_exec_requests

Question

Post reply

sys.sysprocesses Versus sys.dm_exec_requests

Latheesh NK

SSCertifiable

Points: 6559
More actions
March 18, 2011 at 7:21 am

#248453

Whats the difference between sysprocesses and dm_exec_requests. I can see both showing different statuses at the same time. What would be the order of execution.

Say, sys process shows runnable and dm_exec_requests ahows running.

Help me to understand the order of the way it looked into...

Viewing 4 posts - 1 through 3 (of 3 total)

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

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 1

Remark from BOL (2-nd ref)

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

have a look at http://msdn.microsoft.com/en-us/library/ms187997.aspx

and

http://msdn.microsoft.com/en-us/library/ms179881.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 2

Latheesh NK

SSCertifiable

Points: 6559

March 18, 2011 at 9:47 am

#1300614

Thanks for your reply!!!

However, still not clear on the descrepancy of the results. Could you please correct me if my understanding is wrong...

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

you should use the DMVs to get the most reliable results.

sysprocesses itself is a backward compatibility view. It has more statuses documented in BOL than sys.dm_exec_requests.

sys.dm_exec_sessions also has a status column.

sysprocesses has been replaced by :

select *



from sys.dm_exec_requests ER

left join sys.dm_exec_sessions ES

on ES.session_id = ER.session_id

left join sys.dm_exec_connections EC

on EC.session_id = ER.session_id 

--where ER.session_id = 51

order by ER.session_id