search

amachanic / sp_whoisactive

sp_whoisactive
GNU General Public License v3.0
1.14k stars 281 forks source link

Convert Deprecated sys.sysprocesses to sys.dm_exec_sessions #57

Closed sparkledoney closed 2 years ago

sparkledoney commented 2 years ago

sys.sysprocesses is deprecated and so it is causing "complaints" in our database project as it cannot resolve the reference: SQL71502

erikdarlingdata commented 2 years ago

The problem is that dm_exec_sessions is missing columns in older (and, yeah, unsupported) versions of SQL Server that make replacing it impossible. I had to revert back to sysprocesses in a couple cases (1, 2) where it was breaking other scripts I work on.

amachanic commented 2 years ago

I started a migration project several years ago. It's doable, but way more difficult than you might think. The big issue for me wasn't missing columns, but rather performance. sysprocesses is basically equivalent to sys.dm_exec_sessions joined to sys.dm_exec_requests joined to sys.dm_os_waiting_tasks -- but it handles all of the "join" logic internally, direct from memory, in one shot. It's much, much faster to query that one view than to hit those three and let the query processor do its thing.

Adding extra time there means taking time from somewhere else. sp_whoisactive uses sysprocesses as its core, and then joins to all of those other tables mentioned above. Perhaps we could figure out some fancy SQL maneuvers so that we could only hit each table exactly once, but I didn't quite get there. And is sysprocesses really going anywhere? It's been "deprecated" for 16 years now...

Bottom line: If anyone wants to take a stab at fixing this, go for it, and submit a PR! But I think a much nicer option for the foreseeable future is to simply disable the warning :-)

erikdarlingdata commented 2 years ago

@sparkledoney We haven't heard back from you about putting any work in on this, so I'm gonna close it out for now. If you decide you want to work on it, let us know and we'll re-open it.