Last active
August 22, 2017 22:25
-
-
Save mkowoods/9a71feb148ed1617b3d88c3eca48b985 to your computer and use it in GitHub Desktop.
useful sql server sys queries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment