Deprecated features to take out of your toolbox – Part 1

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…

sysprocesses

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_requests, sys.dm_exec_sessions, and sys.dm_exec_connections. The official documentation for sys.sysprocesses warns:

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 t0 and t1 shortly):

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;

Note that 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 sys.sysprocesses:

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;

Results:

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.

Sure, sys.sysprocesses has dbid and waittime. But 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_tran, but open_transaction_count was added back in SQL Server 2012. So there is very little reason to even think about using sys.sysprocesses today.

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.

Next time

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.