[SQL]Get SQL Connections Sessions TCP Statement Info - Part 1

Purposes: Collection of Connections, Sessions, T-SQLs..etc

• These DMV could do a collection of I/O-related database management objects(DMOs), also will help you investigate the related I/O info 
• I/O investigation derived from 

DMVs Used:

    --sys.dm_exec_connections, http://msdn.microsoft.com/en-us/library/ms181509.aspx
    --sys.dm_exec_sessions, http://msdn.microsoft.com/en-us/library/ms176013.aspx
    --sys.dm_exec_requests, http://msdn.microsoft.com/en-us/library/ms177648.aspx
  
    --sys.dm_exec_query_stats, http://msdn.microsoft.com/en-us/library/ms189741.aspx
    --sys.dm_exec_sql_text, http://msdn.microsoft.com/en-us/library/ms181929.aspx


 DMVs descriptions from Microsfot Magazine, http://technet.microsoft.com/en-us/magazine/jj643251.aspx

    sys.dm_exec_query_stats – I/O that a given query has cost over the times it has been executed
    sys.dm_exec_connections – I/O that has taken place on that connection
    sys.dm_exec_sessions – I/O that has taken place during that session
    sys.dm_os_workers – I/O pending for a given worker thread

Note:

You can just use the first 3 DMV to see the Connection info, Session, hostname, IP, etc
Combined with the last 2 DMV was for you to see the exact T-SQL that the request was running
If you just want to see TOP N quesries, you can use the examples from Microsfot MSDN at 02/03 T-SQL

Code:

SELECT   
  --1 sys.dm_exec_connections ec
  ec.session_id
 ,ec.net_transport
 ,ec.encrypt_option
 ,ec.auth_scheme
 ,ec.client_net_address
 ,es.host_name
 ,ec.client_tcp_port
 ,ec.local_net_address AS [Destination]
 ,ec.local_tcp_port AS [Dest Port]
 --2 Join sys.dm_exec_sessions es
 ,es.program_name
 ,es.client_interface_name
 ,es.login_name
 ,es.login_time
 ,CASE 
  WHEN es.transaction_isolation_level = 0
   THEN 'Unspecified'
  WHEN es.transaction_isolation_level = 1
   THEN 'ReadUncomitted'
  WHEN es.transaction_isolation_level = 2
   THEN 'ReadCommitted'
  WHEN es.transaction_isolation_level = 3
   THEN 'Repeatable'
  WHEN es.transaction_isolation_level = 4
   THEN 'Serializable'
  WHEN es.transaction_isolation_level = 5
   THEN 'Snapshot'
  END [Transaction_Iso_Level],
  --3 Left join sys.dm_exec_requests as er
   --[Wait_Time_mSec], [last_wait_type], [wait_resource],If the request is currently blocked, this column returns the duration in milliseconds, of the current wait
  er.command, er.wait_time as [Wait_Time_mSec], er.last_wait_type, er.wait_resource,
   --[cpu_time],in milliseconds that is used by the request
  er.cpu_time, er.reads, er.writes, er.logical_reads, er.text_size,
  er.row_count, er.granted_query_memory as [granted_query_memory_Pages],
  --4 LEFT JOIN sys.dm_exec_query_stats and reference sys.dm_exec_sql_text
  QUERY_STATS.query_hash as [Query Hash]
  ,QUERY_STATS.statement_text as [Statement Text]
  --<1>
   --,SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time"
   --,MIN(query_stats.statement_text) AS "Statement Text"
  --<1> End
FROM sys.dm_exec_connections AS ec
JOIN sys.dm_exec_sessions AS es ON ec.session_id = es.session_id
LEFT JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
--T-SQL example from Microsoft A. Finding the Top N queries, could be wrong Match
LEFT JOIN (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) AS QUERY_STATS
  ON er.sql_handle=QUERY_STATS.sql_handle
 
--<1>
--GROUP BY ec.session_id
-- ,ec.net_transport
-- ,ec.encrypt_option
-- ,ec.auth_scheme
-- ,ec.client_net_address
-- ,es.host_name
-- ,ec.client_tcp_port
-- ,ec.local_net_address 
-- ,ec.local_tcp_port 
-- ,es.program_name
-- ,es.client_interface_name
-- ,es.login_name
-- ,es.login_time
-- ,es.transaction_isolation_level
-- ,er.command, er.wait_time , er.last_wait_type, er.wait_resource
-- ,er.cpu_time, er.reads, er.writes, er.logical_reads, er.text_size
-- ,er.row_count, er.granted_query_memory
-- ,query_stats.query_hash
--ORDER BY 2 DESC;

--02 Finding the TOP N queries
USE AdventureWorks2012;
GO
SELECT TOP 5 query_stats.query_hash AS "Query Hash", 
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM 
    (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

--03 Returning row count aggregates for a query
SELECT qs.execution_count,
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
                 (CASE WHEN qs.statement_end_offset = -1 
                       THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
                       ELSE qs.statement_end_offset end -
                            qs.statement_start_offset
                 )/2
             ) AS query_text, 
     qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid, 
     qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
WHERE qt.text like '%SELECT%' 
ORDER BY qs.execution_count DESC;

Jump to Part 2, i'll write another note

sys.dm_io_virtual_file_stats
sys.dm_io_pending_io_requests