Today we are going to talk about something that has become near and dear to my heart: sysprocesses. I wish I could go back in time and meet me on Day Zero of being a DBA and say “Hey! You there! Stop picking your nose and have a look at THIS!” and then show me the goodness inside the sysprocesses table.
As always please note that this system table will be removed from a future version of SQL Server.
What is this table for?
This table will contain one row for each processes that is currently running against the instance. Note that when I say ‘running’ I am not talking about the status of the process (which could be ‘running’, but it could also be ‘runnable’, or ‘suspended’). Go ahead and see for yourself:
SELECT * FROM master.dbo.sysprocesses
One thing you should notice is that an awful lot of processes are system processes, and many of those have a SQL Server session ID (spid) that is less than or equal to 50. You may find code on the intertubz will say to filter out the spids that are less than or equal to 50, so that you can focus on only the user processes, but you should know that sometimes a non-user process will have a spid greater than 50 (for example, Service Broker will likely have spids higher than 50). We will later see how we can avoid this issue using some new DMVs.
Why should you care?
As a DBA you really have two operational methods: proactive and reactive. If someone is pounding on your desk telling you to fix something NOW then you will be need to react quickly and looking at the sysprocesses table can help. In a proactive manner you will want to periodically gather details about the running processes so that you can review later, perform some trend analysis, and try to make some changes that will have a positive effect on performance.
In both cases you are going to want to focus on a handful of columns like the following:
- blocked – (if THIS spid is blocked, then this column shows the spid doing the blocking)
- lastwaittype – (shows the last OR current wait event)
- status – (current status such as runnable, suspended, running, rollback)
- sql_handle – (the current executing statement, could be a batch)
- stmt_start – (starting point inside the sql_handle)
- stmt_end – (ending point inside the sql_handle)
For example, say you identify SPID 65 as an offending process and you want to see additional details. You can run the following:
DECLARE @handle binary(200) SELECT @handle = sql_handle FROM master..sysprocesses WHERE spid = 65 SELECT [text] FROM ::fn_get_sql(@handle)
And now you can see the entire statement that is being executed.
My favorite column here would be the lastwaittype column, as this gives me the DBA equivalent of unicorn bacon. Knowing exactly what the statement is waiting for allows for me to fix the specific bottleneck causing that wait, which will increase performance. Diving into wait events is outside the scope of this simple blog post, so I will refer you to this excellent whitepaper instead.
Where else is this information?
Remember how I said this system table will be removed from a future version of SQL Server? Well then you should get yourself familiar with the new execution related DMVs such as:
- sys.dm_exec_connections
- sys.dm_exec_sessions
- sys.dm_exec_requests
These views offer you the ability to get some details on the current running processes in a more efficient manner than the sysprocesses table alone. For an example of using sys.dm_exec_requests to return the text from the sql_handle check out this blog post by Aaron Bertrand (blog | @aaronbertrand).
Another example that you may find useful is the following:
SELECT s.loginame, db_name(s.dbid) name, s.hostname, s.program_name, s.stmt_start, s.stmt_end, s.spid, CONVERT(smallint, s.waittype) waittype, s.lastwaittype, s.ecid, s.waittime, CONVERT(varchar(64), s.context_info) context_info, s.blocked, r.plan_handle ,[obj] = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid])) + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])), t.[text] FROM master..sysprocesses AS s LEFT OUTER JOIN sys.dm_exec_sessions es ON es.session_id = s.spid LEFT OUTER JOIN sys.dm_exec_requests r ON r.session_id = s.spid OUTER APPLY sys.dm_exec_sql_text(s.[sql_handle]) AS t WHERE ( s.dbid<>0 AND s.cmd<>'AWAITING COMMAND' AND s.cmd NOT LIKE '%BACKUP%' AND s.cmd NOT LIKE '%RESTORE%' AND es.is_user_process = 1 AND s.spid<>@@SPID )
This code filters out for some common commands such as a backup and restore, but you can alter it as you see fit. It is also filtering for only user proceeses as a result of the following line in the WHERE clause:
AND es.is_user_process = 1
These DMVs are valuable for either a reactive or proactive DBA. The sysprocesses table (and the newer DMVs) are your best friend if you need to see what is happening now or you need to track activity over time to perform some trend analysis.
