Skip to content

Latest commit

 

History

History
93 lines (63 loc) · 4.21 KB

user-sessions.md

File metadata and controls

93 lines (63 loc) · 4.21 KB
title ms.custom ms.date ms.reviewer ms.suite ms.tgt_pltfrm ms.topic ms.assetid caps.latest.revision author
User Sessions (SQL Server PDW)
na
01/13/2017
na
na
na
article
0425cef2-de4d-4f42-91c5-cb1cd4bb1265
15
BarbKess

User Sessions

A login with the appropriate permissions can manage the sessions of all logins on a SQL Server PDW appliance, including performing these actions:

  • View the current sessions on the appliance, including both active and idle sessions.

  • View the active and recent queries for a session.

  • End active sessions.

These actions can be performed by using either the Monitor the Appliance by Using the Admin Console or System Views through SQL commands, as shown below.

The permissions required to manage sessions by using either method are the same, and are described in Grant Permissions to Manage Logins, Users, and Database Roles.

Manage Sessions by Using the Admin Console

To view current sessions by using the Admin Console

  1. On the top menu, click Sessions.

  2. The resulting list displays all recent sessions. To view only ‘Active’ or ‘Idle’ sessions, click the Status column header to sort results by status.

To view active and recent queries for a session by using the Admin Console

  1. On the top menu, click Sessions.

  2. In the results list, click the session ID of the desired session.

  3. The resulting queries list shows the recent queries for the session. For information on viewing query details, see Monitoring Active Queries.

To end sessions by using the Admin Console

  1. On the top menu, click Sessions.

  2. Find the session ID for the session to cancel.

  3. Click the red X to the left of the session ID to end the session. Only sessions with a status of ‘Active’ or ‘Idle’ will have a red X; only these sessions can be ended.

Manage Sessions by Using System Views and SQL Commands

To View Current Sessions by Using System Views

Use sys.dm_pdw_exec_sessions to generate a list of current sessions.

This example returns the session_id, login_name, and status for all sessions with a status of ‘Active’ or ‘Idle’.

SELECT session_id, login_name, status FROM sys.dm_pdw_exec_sessions WHERE status='Active' OR status='Idle';

To View Active and Recent Queries for a Session by Using System Views

To see the active and recently completed queries associated with a session, you use the sys.dm_pdw_exec_sessions and sys.dm_pdw_exec_requests views. This query returns a list of all active or idle sessions, plus any active or recent queries associated with each session ID.

SELECT es.session_id, es.login_name, es.status AS sessionStatus,   
er.request_id, er.status AS requestStatus, er.command   
FROM sys.dm_pdw_exec_sessions es   
LEFT OUTER JOIN sys.dm_pdw_exec_requests er   
ON (es.session_id=er.session_id)   
WHERE (es.status='Active' OR es.status='Idle') AND   
(er.status!= 'Completed' AND er.status!= 'Failed' AND er.status!= 'Cancelled');

To End Sessions by Using SQL Commands

Use the KILL command to end a current session. You will need the session ID for the process to terminate, which can be obtained using the sys.dm_pdw_exec_sessions view.

In this example, select the login_name, session_id, and status values to find a session based on the login name.

SELECT session_id, login_name, status FROM sys.dm_pdw_exec_sessions;

Sessions with an ‘Active’ or ‘Idle’ status can be ended by using the KILL command.

KILL 'SID137';