Microsoft is not in the habit of deprecating things these days, but when they do, it's for a reason – and it's certainly not because they want to make your life harder. On the contrary, it is almost always because they have developed better and more modern ways to solve those same problems.
But habits are hard to break; ask me how I know. All too often, I see people clinging to an older way of accomplishing some task, even though a better way exists.
I would like to share a couple of recent examples that help illustrate how using deprecated SQL Server features continues to bite us. In this first part, I want to talk about…
The system table
sys.sysprocesses was replaced way back in SQL Server 2005 by a set of dynamic management views (DMVs), most notably
sys.dm_exec_connections. The official documentation for
A recent example
Recently one of our teams was investigating a log reader latency issue. We pay a lot of attention to latency here, along with any long-running transactions, because of downstream impact to technologies that use the log reader – like Availability Groups and transactional replication. Our first warnings are usually spotted on a dashboard that plots log reader latency against transaction duration (I'll explain the points in time I labeled
They determined, let's say at time
t0, that a certain session had an open transaction blocking the log reader process. They first checked the output of
DBCC INPUTBUFFER, to try to determine what this session did last, but the results simply indicated that they also issued other batches during their transaction:
event_type parameters event_info -------------- ---------- --------------- Language Event 0 SET ROWCOUNT 0;
DBCC INPUTBUFFER also has a more capable replacement in modern versions:
sys.dm_exec_input_buffer. And while it doesn't have an explicit deprecation warning, the official documentation for the
DBCC command has this gentle nudge:
After getting nothing from the input buffer, they queried
SELECT spid, [status], open_tran, waittime, [cpu], physical_io, memusage, last_batch FROM sys.sysprocesses WHERE spid = 107;
The results were similarly useless, at least in terms of determining what the session had been doing to keep their transaction open and disrupt the log reader:
I'm highlighting the
physical_io column because this value sparked a discussion about whether or not they wanted to risk killing the sleeping session. The thinking was that, in the event all those physical I/Os are writes, killing the transaction might result in a lengthy and disruptive rollback – potentially making the problem even worse. I'm not going to put actual times on this, but let's just say this turned into a prolonged conversation, and it left the system in this state from time
t0 to time
t1 on the graph above.
Why this is a problem
The issue in this specific case is that they spent that time contemplating a decision based on incomplete information. Are those I/Os reads or writes? If the user has an open transaction and has merely read a lot of data, there is far less impact in rolling that transaction back than if they have changed a lot of data. So, instead of
sys.sysprocesses, let's see what the more modern DMV,
sys.dm_exec_sessions, can show us about this session:
SELECT session_id, [status], open_transaction_count, cpu_time, [reads], writes, logical_reads, last_request_start_time, last_request_end_time FROM sys.dm_exec_sessions WHERE session_id = 107;
Here we see that
sys.dm_exec_sessions breaks out the physical I/O separately into reads and writes. This allows us to make a much more informed decision, much more quickly than
t1 - t0, about a potential rollback's impact. If the I/O is all writes, and depending on how high the number is, we might hesitate a bit more and perhaps spend the time trying to locate the user (so we could smack their knuckles or ask them why they have an open transaction). If we know it is mostly reads, we can instead lean toward killing the session and forcing the transaction to roll back.
dbid is unreliable and marginally useful anyway, particularly for cross-database queries; there's much better info in
sys.dm_tran_locks. Wait info (time and last wait type) can be found in
sys.dm_exec_requests, but much more detailed info is offered in
sys.dm_exec_session_wait_stats (added in SQL Server 2016). An excuse I used to hear a lot was that
sys.dm_exec_sessions was missing
open_transaction_count was added back in SQL Server 2012. So there is very little reason to even think about using
If you want to discover how often
sys.sysprocesses has been referenced since SQL Server restarted last, you can run this query against the performance counters DMV:
SELECT instance_name, cntr_value FROM sys.dm_os_performance_counters WHERE [object_name] LIKE N'%:Deprecated Features%' AND instance_name = N'sysprocesses' ORDER BY cntr_value DESC;
If you really want to avoid sleep tonight, or you just like to constantly add to the laundry list of things you worry about, remove the predicate against
instance_name. This will give you a scary, high level idea of how many things your instances are running that you will eventually need to change.
In the meantime, go download
sp_WhoIsActive, Adam Machanic's ultra useful stored procedure for monitoring and troubleshooting SQL Server processes in real time. We have deployed this stored procedure to every instance in our environment, and you should too, regardless of what other high-end monitoring tools you might also be using.
In Part 2, I'm going to talk a little about SQL Server Profiler, an application that people use more because of familiarity than anything else – without realizing how dangerous it can be.