May 9, 2014 at 12:10 pm
1. how many connection there is on SERVER?
2. how many connection there is on the DATABASE?
suspend, sleeping... also counts as connection?
I am getting confusion answers running who2, who2 active, sys.dm_exec_session, sys.dm_exec_connection...bla.bla..
what's is the best way to check above 2 things?
May 9, 2014 at 12:25 pm
Check out sys.dm_exec_connections and sys.dm_exec_sessions
😎
SELECT COUNT(*) AS SESSION_COUNT
FROM sys.dm_exec_connections
SELECT COUNT(*) AS SESSION_COUNT
FROM sys.dm_exec_sessions SS WHERE SS.host_name IS NOT NULL
May 9, 2014 at 3:08 pm
Thank you for your reply. Can you please tell me in detail?
1. what output 'who2', 'who2 active' and 'who is active' give ONLY REGARDING database connection and Server connection.
2. how many connection there is on SERVER level?
3. how many connection there is on the particular DATABASE?
May 9, 2014 at 11:34 pm
This query is somewhat self explanatory and answers in most parts your question
😎
USE master;
GO
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY
DES.database_id
,DES.login_name
,DES.status
ORDER BY (SELECT NULL)
) AS SESS_RID
,DES.session_id
,DB_NAME(DES.database_id) AS DATABASE_NAME
,DES.login_name AS DATABASE_USER
,COUNT(DES.session_id) OVER
(
PARTITION BY
DES.database_id
) AS TOTAL_DATABASE_SESSIONS
,COUNT(DES.session_id) OVER
(
PARTITION BY
DES.login_name
) AS USER_SESSIONS
,COUNT(DES.session_id) OVER
(
PARTITION BY
DES.login_name
,DES.database_id
) AS USER_DB_SESSIONS
,DES.status AS SESSION_STATUS
,SUM(DEX.num_reads) OVER
(
PARTITION BY
DES.login_name
) AS USER_TOTAL_READ
,SUM(DEX.num_writes) OVER
(
PARTITION BY
DES.login_name
) AS USER_TOTAL_WRITES
,SUM(DEX.num_reads) OVER
(
PARTITION BY
DES.login_name
,DES.database_id
) AS USER_DB_READ
,SUM(DEX.num_writes) OVER
(
PARTITION BY
DES.login_name
,DES.database_id
) AS USER_DB_WRITES
FROM sys.dm_exec_sessions DES
LEFT OUTER JOIN sys.dm_exec_connections DEX
ON DES.session_id = DEX.session_id
) AS X WHERE X.SESS_RID = 1
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply