So today I had a requirement to provide a quick and dirty way of collecting data on the sessions connected to SQL Server. Specifically, I had to write a job that collected basic data on the current sessions, and if these sessions had not been previously recorded, record them into a table. If the session was identical in all ways other than the session ID, then a counter called 'session count' is incremented for that session.
The challenging part here was providing what is basically a mashup of an INSERT and UPDATE statement, sometimes called an UPSERT. In SQL Server 2008 and later versions, this functionality is provided using the MERGE statement. My requirement here is to update a table with values if they exist; otherwise, insert them.
So here's an example of how to use this MERGE statement in the context of my requirement. The example code is below. You can modify it to suit your purpose - for example, write out to a permanent table, put it in a job, query the table in a different way, monitor requests rather than sessions, etc. or re-write it entirely, or simply use it as an example of MERGE for reference.
There's also a self-join in there which provides the columns on which to match. This isn't strictly essential but I needed to match on columns then only update the last login time and last request end time if matched. The outer query simply aggregates the results in the target table to provide a good view of the data.
Note this won't collect data on sessions which are opened and closed between running the collection. So if you modify this code by e.g. using a permanent table and putting this code into a job (can't use a temp table in this context), run the job frequently. If you run it e.g. every 15 minutes and a session is created and destroyed between the job execution intervals, you won't capture the data.
The challenging part here was providing what is basically a mashup of an INSERT and UPDATE statement, sometimes called an UPSERT. In SQL Server 2008 and later versions, this functionality is provided using the MERGE statement. My requirement here is to update a table with values if they exist; otherwise, insert them.
So here's an example of how to use this MERGE statement in the context of my requirement. The example code is below. You can modify it to suit your purpose - for example, write out to a permanent table, put it in a job, query the table in a different way, monitor requests rather than sessions, etc. or re-write it entirely, or simply use it as an example of MERGE for reference.
There's also a self-join in there which provides the columns on which to match. This isn't strictly essential but I needed to match on columns then only update the last login time and last request end time if matched. The outer query simply aggregates the results in the target table to provide a good view of the data.
Note this won't collect data on sessions which are opened and closed between running the collection. So if you modify this code by e.g. using a permanent table and putting this code into a job (can't use a temp table in this context), run the job frequently. If you run it e.g. every 15 minutes and a session is created and destroyed between the job execution intervals, you won't capture the data.
-- ---------------------------------------------------------------------------------------------
-- Script to audit sessions on the server, accumulate session counts per distinct group of
-- values from sys.dm_exec_sessions, will ignore duplicate sessions (by session_id and unique
-- set of values), will increment when a new session is started that has the key values
-- matching, will insert when a brand new session is opened. Uses MERGE.
-- Run inside a stored procedure, or on the query window, multiple times over the course of X
-- hours to get a full list of sessions.
-- Add filters if necessary on the final SELECT.
-- Change #Logins to a permanent table if required.
-- Works only in SQL Server 2008, R2, 2012 and 2014.
-- Author: Derek Colley, 13/12/2013
-- ---------------------------------------------------------------------------------------------
-- TRUNCATE TABLE #Logins -- for debug
IF NOT EXISTS ( SELECT name FROM tempdb.sys.tables WHERE name LIKE ('#Logins%') )
CREATE TABLE #Logins (
uqid INT IDENTITY(1,1),
session_id INT,
last_login_time DATETIME,
host_name NVARCHAR(128),
program_name NVARCHAR(128),
client_interface_name NVARCHAR(32),
login_name NVARCHAR(128),
last_request_end_time DATETIME )
DECLARE @CurrentSessions TABLE (
session_id INT,
login_time DATETIME,
host_name NVARCHAR(128),
program_name NVARCHAR(128),
client_interface_name NVARCHAR(32),
login_name NVARCHAR(128),
last_request_end_time DATETIME )
INSERT INTO @CurrentSessions
SELECT session_id, login_time, host_name, program_name, client_interface_name,
login_name, last_request_end_time
FROM sys.dm_exec_sessions
WHERE session_id <> @@SPID
AND session_id > 50
MERGE INTO #Logins
USING (
SELECT c.session_id, c.login_time, c.host_name, c.program_name, c.client_interface_name,
c.login_name , c.last_request_end_time
FROM @CurrentSessions c
LEFT JOIN #Logins l
ON c.session_id = l.session_id
AND c.host_name = l.host_name
AND c.program_name = l.program_name
AND c.client_interface_name = l.client_interface_name
AND c.login_name = l.login_name ) AS CurrentSessions
ON #Logins.session_id = CurrentSessions.session_id
AND #Logins.host_name = CurrentSessions.host_name
AND #Logins.program_name = CurrentSessions.program_name
AND #Logins.client_interface_name = CurrentSessions.client_interface_name
AND #Logins.login_name = CurrentSessions.login_name
WHEN MATCHED THEN
UPDATE
SET last_login_time = CurrentSessions.login_time,
last_request_end_time = CurrentSessions.last_request_end_time
WHEN NOT MATCHED THEN
INSERT ( session_id, last_login_time, host_name, program_name, client_interface_name,
login_name, last_request_end_time )
VALUES (session_id, login_time, host_name, program_name, client_interface_name,
login_name, last_request_end_time );
SELECT COUNT(*) [session_count], MAX(last_login_time) [last_login_time], host_name, program_name,
client_interface_name, login_name, MAX(last_request_end_time) [last_request_end_time]
FROM #Logins
GROUP BY host_name, program_name, client_interface_name, login_name
ORDER BY COUNT(*) DESC
