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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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