Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 2: Connections, Sessions and Requests

Chapter 2: Connections, Sessions and Requests

• The following DMO give us a view of what is happening now in SQL Server:
• sys.dm_exec_connections – provides information about network traffic and protocols, as well as user attributes from the calling source of the connection. One of its resulted columns is most_recent_session_id which is more appropriate to use when joins between this DMV and sys.dm_exec_sessions as sessions will reuse open connections as seen fit, and this column will store the more accurate information to complete the join. Another column is most_recent_sql_handle which its text can be returned by passing the handle to the sys.dm_exec_sql_text DMF. For the auth_scheme column if the value is SQL then it is SQL Server authentication, other than that it is Windows authentication … P38-39.
• sys.dm_exec_sessions – returns information about each user and internal system session on a SQL Server instance including session settings, security, and cumulative CPU, memory, and I/O usage. Note that many of the columns in this DMV may have a NULL value associated with them if the sessions are internal to Microsoft SQL Server (those with session_id < 51).  Values in this sessions DMV are updated only when their associated requests have finished executing. One of its column is status, If none of the session's requests are currently running, then the status is “sleeping”, or if the "work" owned by a session is currently being executed, then the value will be “running”, there is also a third status value of dormant, indicating a session that "has been reset due to connection pooling". Other interesting columns in this DMV are: total_elapsed_time, last_request_start_time, cpu_time, memory_usage (number of 8 KB pages), total_scheduled_time, logical_reads (from cache), and reads & writes (from disk) … P40-41.
• sys.dm_exec_requests – provides a range of query execution statistics, such as elapsed time, wait time, CPU time, and so on. It returns one row for every query currently executing. The information returned from sys.dm_exec_requests is real time; it's not returned after the fact.
• sys.dm_exec_sql_text – returns the text of the SQL batch identified by a sql_handle.
• sys.dm_exec_query_plan – returns, in XML format, the query plan, identified by a plan_handle.
• Who is connected? The query in Listing 2.3 identifies sources of multiple connections to your SQL Server instance and so will allow the DBA to identify where the bulk of the connections originate, for each of their instances ... P42.
• Listing 2.4 gives who is connected by SSMS, and what query they are running (you can also add des.login_name, to the selected column, to get the login) … P44.
• Listing 2.5: Return session-level settings for the current session. The various session settings determine, not only how the session handles requests and transactions that flow along its ownership chain, but also how the session interacts with other sessions running concurrently on the SQL Server instance. Sessions with elevated deadlock priority, for example, are able to run roughshod over any other sessions with which they may conflict in a deadlock situation … P46.
• Listing 2.6 reports on the number of sessions being run by each login on your SQL Server instance. It's especially useful for seeking out those logins that own more than a single session … P47.
• Context switching is the act of executing T-SQL code under the guise of another user connection, in order to utilize their credentials and level of rights. Listing 2.7 allow DBAs to identify its occurrence … P48.
• Inactive sessions: The query shown in Listing 2.8 identifies all sessions that are open and have associated transactions, but have had no active requests running in the last n days.  If cumulative activity is high, as indicated by the values of cpu_time, total_elapsed_time, total_scheduled_time, and so on, but the session has been inactive for a while, then it may be an application that keeps a more-or-less permanent session open, and therefore there is little to be done about it.… P49.
• Listing 2.9: Identifying sessions with orphaned transactions …P51.

sys.dm_exec_requests

• The status column of the _requests DMV reveals the status of a given request within a session. If a request is currently executing, its status is running. If it is in the "runnable" queue, which simply means it is in the queue to get on the processor, its status is runnable. This is referred to as a signal wait. If it is waiting for another resource, such as a locked page, to become available in order to proceed, or if a running request needs to perform I/O, then it is moved to the waiter list; this is a resource wait and the waiting request's status will be recorded as suspended … P55.
• The blocking_session_id column of the _requests DMV lists the session_id that is blocking the request; if no blocking exists, the value will be NULL; if the value is (-2), then the block is owned by an orphaned distributed transaction; if (-3) the block is owned by a deferred recovery transaction; and if (-4) the session_id of the blocking latch owner could not be identified … P56.
• The _requests DMV contains useful activity and workload columns: percent_complete, can be used as a metric for completion status for certain operations. cpu_time, the total amount of processing time spent on this request. row_count, the number of rows that were processed for the request. granted_query_memory, number of 8 KB pages allocated to the execution of the request. reads & writes, total physical disk reads/writes performed for this request. logical_reads, total number of reads from the data cache for this request … P57. 

• Listing 2.10: Retrieving the text for a currently executing ad hoc query … P58-60.
• Pass the sql_handle to the sys.dm_exec_sql_text DMF, to obtain the SQL text of the executing batch. This batch or procedure may consist of tens or even hundreds of SQL statements, so we'll get back the text for all those statements. A common pattern is to use the SUBSTRING function and the byte offset columns (statement_start_offset and statement_end_offset), supplied by sys.dm_exec_requests, to extract the text for only that statement within that batch that is currently executing, as in listing 2.14 … P61-63.
• By adapting the script in Listing 2.16, we can examine the activity of each currently active request in each active session in terms of CPU usage, number of pages allocated to the request in memory, amount of time spent waiting, current execution time, or number of physical reads… P65.
• Who is running what, right now? Listing 2.17 … P67.
• The script provided in Listing 2.20, essentially provides a more detailed version of sp_who2. It returns all of the columns provided by sp_who2, and augments it with the executing portion of the T-SQL text associated with the request and the query plan in XML format. Unlike in sp_who2, this query breaks down the Disk I/O information into reads and writes. Finally, it also includes wait metrics and the transaction_isolation_level, to provide insight into how this session is interacting with other sessions currently running on the instance. As a whole, this information offers a very useful snapshot of activity, resource impact, and processing health on the SQL instance. Others have taken this much further; in particular, SQL Server MVP Adam Machanic has created a script called Who Is Active, which is available in http://tinyurl.com/WhoIsActive and which returns, not only all columns provided by script in listing 2.20, but also detailed information on tempDB impact, context switches, memory consumption, thread metadata, and the query text rendered as XML…P69.

Reference: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476