useful sql server sys queries

server.sql

--last mod date for all tables
SELECT 
	t.name,
	t.modify_date,
	i.rowcnt
FROM sys.tables t
left outer join sysindexes i
ON t.object_id = i.id 
WHERE i.indid < 2
order by
	t.modify_date

-- results displayed in microseconds..
SELECT  creation_time 
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads 
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,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) st
ORDER BY total_elapsed_time / execution_count DESC;


--get current executing requests

select
    r.session_id,
    s.login_name,
    c.client_net_address,
    s.host_name,
    s.program_name,
    st.text
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
left join sys.dm_exec_connections c
on r.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
--where st.text like '%your query string to search for%';


---Wiht the query execution plan

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1),
  qs.execution_count,
  qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads,
  qs.max_logical_reads, qs.total_physical_reads, qs.last_physical_reads,
  qs.min_physical_reads, qs.max_physical_reads,
  qs.total_elapsed_time / 1000000 As total_elapsed_time, 
  qs.last_elapsed_time / 1000000 As last_elapsed_time,
  qs.min_elapsed_time / 1000000 As min_elapsed_time, 
  qs.max_elapsed_time / 1000000 As max_elapsed_time,
  qs.last_execution_time, qs.creation_time, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
AND last_execution_time >= DATEADD (MINUTE , -5 , CURRENT_TIMESTAMP )
ORDER BY qs.total_logical_reads DESC
--ORDER BY qs.total_physical_reads DESC


--get session and connection information...

select
	s.login_name,
    c.client_net_address,
    s.host_name,
    s.program_name,
	s.*
from sys.dm_exec_sessions s WITH (NOLOCK)
left outer join sys.dm_exec_connections c WITH (NOLOCK)
on s.session_id = c.session_id
where
	login_name <> 'sa'