When users start complaining about performance problems, you need to quickly identify the current database activity in real-time. Alternatively, you might be asked to troubleshoot why a particular scheduled task takes longer to run when compared to its normal execution duration.
This week we are going to examine how to monitor database activity in real-time, and historically as well.
Monitor Current Sessions
SQL Server database engine provides a number of DMV's that allow you in real-time to determine what users are currently connected to the SQL Server instance, what queries they are running, the resource consumption of those queries, whether are experiencing any contention and what resources they are waiting on. In this section, we will examine these DMV's and learn how to query them.
I will show you the relationships that we can query the currently executing connections, sessions, requests and query executions.
sys.dm_exec_connections Provides detailed information about the connections made to the database engine. Detailed information includes the authentication used (Kerberos, NTLM or SQL), the network transport protocol (shared memory or TCP), and the client IP address.
sys.dm_exec_requests Provides information about each request that is executing with the database engine. The following columns are important and should be taken in mind when you build a query. blocking_session_id, command, dop (Degree of Parallelism), last_wait_type, percent_complete, status, transaction_isolation_level, wait_time, wait_type.
sys.dm_exec_sessions Provides information about all active user connections and internal tasks, including the client (TDS) version, client program name, client login time, login user, current session setting, resource consumption, status, contest visit, and a range of metrics.
sys.dm_tran_session_transactions Returns correlation information for sessions and their associated transactions. It differentiates between local and distributed transactions.
sys.dm_tras_session_wait_stats New to SQL Server 2016, this DMV returns information about all the waits encountered by threads for each session. Use this view to diagnose performance issues for a specific session.
sys.dm_os_waiting_tasks Returns correlation information about the wait queue of tasks that are waiting on resources, pay special attention to the following columns, blocking_session_id, the SPID of the session that is blocking the request. Resource_id, detail information about the resource that is being consumed. Wait_time, wait_duration_ms this last one in milliseconds :)
Identify Sessions that Cause Blocking Activity
Blocking is a common cause of proof performance in a database engine, where multiple transactions are isolated from each other through locking. By identifying sessions that are causing blocking you will be able to kill the offending sessions, engage your developers to rewrite their code and re-index tables appropriately.
The database engine offers a number of different techniques that you can use to identify sessions that cause blocking. The techniques include, but are not limited to:
Querying any of following DMV's, sys.dm_exec_requests, sys.dm_os_waiting_tasks.
Executing the sp_who or the undocumented sp_who2 system stored procedures.
Using the blocked processed report.
Configuring and consuming the blocked process threshold reports in the database engine.
-- Queries allocated the most space in tempdb
Select r.session_id, r.request_id, t.text as Query,
u.allocated AS task_internal_object_page_allocation_count,
u.deallocated AS task_internal_object_page_deallocation_count
FROM ( Select session_id, request_id,
SUM(internal_objects_alloc_page_count) AS allocated,
SUM(internal_objects_dealloc_page_count) as deallocated
GROUP BY session_id, request_id) AS u
JOIN sys.dm_exec_requests as r
ON u.session_id = r.session_id and u.request_id = r.request_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) as t
ORDER BY u.allocated DESC;
-- Querying current sessions
Select 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, s.login_time
FROM sys.dm_exec_connections AS C
JOIN sys.dm_exec_sessions AS S
ON c.session_id = s.session_id;
Need more review files, scripts or examples? OK Let me know here - Email Me and we have a complete zone of documentation, scripts and solutions.
Things that you can find in the Paddock Zone.
Perform database snapshots
Backup databases to Azure
Understand transaction log chains.
Manage the full transaction log incident.