May 15, 2014 at 8:55 am
This is version 2008R2
I was hoping to future proof code to list connections to a specific database by not using master..sysprocesses and instead joining sys.dm_exec_sessions and sys.dm_exec_requests. However sys.dm_exec_sessions does not contain the database_id, sys.dm_exec_requests does, but only gives me connections actually executing and I want all of them.
I note sys.dm_exec_sessions in 2012 has database_id added, does that mean I am forced to use sysprocesses to accomplish this until I am on SQL2012?
---------------------------------------------------------------------
May 15, 2014 at 11:46 am
Or upgrade to 2014.
According to MS, that column was added with 2012, so all you can do is left joins to the sys.dm_exec_requests.
Although, you might take a look at Adam Machanic's sp_whoisactive to see if addressed this in some other fashion.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 16, 2014 at 6:43 am
thanks for the reply Grant.
Adams code is still referring to sysprocesses in the SQL2005 and 2008 compatible version so there still seems to be a need for it (although to be honest I have not worked out where and why adam is using it)
As the database the spid is connected to is only trapped in exec_requests as soon as I want to filter on database I am reduced to only getting currently executing sessions. I guess Microsoft are addressing this from SQL2012 up by introducing database_Id into exec_sessions as preparation to deprecating sysprocesses.
So I will stick with sysprocesses until such time as the app that will use this code is on 2012\14
I will sure miss sysprocesses, seems worth keeping to me.
---------------------------------------------------------------------
May 16, 2014 at 8:59 am
looking at sp_whoisactive I could of course just use it as it is to achieve what I want, but it would be overkill!
I am pretty sure Adam is joining back to sysprocesses to get info on sleeping spids, but thats a lot of code!
---------------------------------------------------------------------
May 16, 2014 at 9:06 am
yes he does join back to sysprocesses
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 16, 2014 at 9:13 am
cheers, I have a plan of action now and we know where we stand with current DMVs and those from 2012 onwards.
george
---------------------------------------------------------------------
May 16, 2014 at 12:36 pm
Wanted to count the connections yesterday and came up with this:SELECT distinct
Cses.host_name AS HostName
FROM sys.dm_exec_connections Con
INNER JOIN sys.dm_exec_sessions Cses ON Con.session_id = Cses.session_id
INNER JOIN sys.sysprocesses SP ON Con.session_id = SP.spid
INNER JOIN sys.databases DB ON SP.dbid = DB.database_id
ORDER BY HostName
-- From this:
SELECT
Con.session_id
, DB.name AS DBName
, Cses.host_name AS HostName
, Cses.login_name AS LoginName
, Cses.program_name AS ProgramName
, Con.connect_time AS ConntectTime
, Con.last_read AS LastRead
, Con.last_write AS LastWrite
, SP.cmd AS Command
, (-- Query gets text as XML for the blocking query
SELECT [text] AS [text()]
FROM sys.dm_exec_sql_text(Con.most_recent_sql_handle)
FOR XML PATH(''), TYPE
) AS ProgramText
, Cses.cpu_time / 1000.0 AS CPUTime
, SP.physical_io AS DiskIO
FROM sys.dm_exec_connections Con
INNER JOIN sys.dm_exec_sessions Cses ON Con.session_id = Cses.session_id
INNER JOIN sys.sysprocesses SP ON Con.session_id = SP.spid
INNER JOIN sys.databases DB ON SP.dbid = DB.database_id
ORDER BY LoginName
May 19, 2014 at 6:37 am
thanks djj, but all the values I require (including hostname) are in sysprocesses so thats all I need for now on pre 2012 systems.
---------------------------------------------------------------------
May 19, 2014 at 6:44 am
Was not sure if it would help as it was not exactly what you wanted but thought it might spark an idea.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply