Capture very expensive queries

I have an Sql servers of 2008/2012. Some users run very expensive queries, such as updating 1G rows in one transaction or cross join several very big tables, and it causes the server no response from time to time.

What's the good way to trace/trigger the server to save the running queries when it happens?

You can identify problematic queries through DMVs using this query:

USE [master]
GO
SELECT  session_id,
blocking_session_id,
wait_time,
wait_type,
last_wait_type,
wait_resource,
transaction_isolati,
on_level,
lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
GO

If you want to collect historical data or automate the process, you should consider 3rd party solutions. I’m using ApexSQL Monitor, but there are many others on the market. WindRaven mentioned some but there are also Idera Diagnostic, Nagios, SQL Sentry, Quest Spotlight.

StackExchange Database Administrators Q#59372, answer score: 7