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:
- get list of connected users and its sessions
- get list of long running cursor
- get list of idle session that has open transaction(s)
- get information about own query and connection info
- 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; GO 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 WHERE EXISTS (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.
SELECT 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 🙂