SQL-Server admin sqlQuery to view the running stats on the server

Connect with

tsql admin query tsql admin query to view the running stats. I have used this frequently on SQL-Server 2016 for day to day activities and useful for me most of the time. You can also try this if you are an admin or developer or user of an SQL server.

1. Overview of TSQL admin Query

In this article, I tried to cover a few tsql admin queries which I use day-to-day life. The list of operation are as follows:

  1. get list of connected users and its sessions
  2. get list of long running cursor
  3. get list of idle session that has open transaction(s)
  4. get information about own query and connection info
  5. Get information about blocked request

I have tested the following query in SQL server 2016. You usually need this to get server connection related, session related information, an idle session which has open transaction, your own query, and its connection related things, about blocked request. These are the few lists which you need but the actual list is more which I provide you in the next post. You can visit my another post of MogoDB Admin cheatsheet
Following are the SQL query you can run and find the stats as per your need.

2. Get list of connected users to server

The following tsql admin query will be used to get a list of all connected users at that point in time to sever and return a number of sessions.

SELECT login_name ,COUNT(session_id) AS session_count   
FROM sys.dm_exec_sessions   
GROUP BY login_name; 

3. Get list of long-running cursors

The following query can be used to find cursors that have been open for more than a specific period of time, who created the cursors, and what session the cursors are on.

USE master;  
SELECT creation_time ,cursor_id ,name ,c.session_id ,login_name   
FROM sys.dm_exec_cursors(0) AS c   
JOIN sys.dm_exec_sessions AS s   
   ON c.session_id = s.session_id   
WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 3; 

output of query as:

creation_time		cursor_id	name	session_id	login_name
2020-10-16 18:01:50	180,150,011	[NULL]	438			ranjeetUser

4. Get Idle sessions that have open transactions

The following tsql admin query can be used to find idle sessions that have been open transactions.

SELECT s.*   
   FROM sys.dm_exec_sessions AS s  
   (SELECT *  FROM sys.dm_tran_session_transactions AS a WHERE a.session_id = s.session_id )  
    AND NOT EXISTS (SELECT * FROM sys.dm_exec_requests AS b WHERE b.session_id =  s.session_id  ); 

5. Get information about a queries own connection

The following admin tsql query can be used to gather information about a query’s own connection.

    s.original_login_name, s.login_time ,c.session_id, c.net_transport, 
    c.encrypt_option, c.auth_scheme, s.host_name, s.program_name,   
    s.client_interface_name, s.login_name, s.nt_domain,   
    s.nt_user_name,  c.connect_time
FROM sys.dm_exec_connections AS c  
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id  
WHERE c.session_id = @@SPID;

6. Get all currently blocked requests

Following sql admin command can be used to get information about blocked requests.

SELECT session_id, status, blocking_session_id ,wait_time
	,wait_resource,wait_type, transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended'; 

Share you thought by writing comment, will this helpful for you about tsql admin query. Your reached at this point, it means you like this post. Happy learning 🙂

Connect with

Leave a Comment

Your email address will not be published. Required fields are marked *