Capture very expensive queries

Question

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?

Answer 1

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.

Answer 2

I would recommend checking out Adam Machanic's great free script 'sp_WhoIsActive': http://whoisactive.com

We run this on a job every two minutes. It collects ten seconds of data and logs it to a table.

Using this information you could easily see things like: - Long running queries - Blocking - Query costs - Wait information

Whenever our server slows down we can pull up the latest data from our job (using an SSRS report) and see what has been going on, and what seems to be causing the slow down. This script has changed the way we troubleshoot server issues.

Answer 3

SQL for starting/stopping traces:

-----------------------------------------------------------------------------------------------------------------------------------
-- STEP 1: CREATE A TUNING TRACE
-----------------------------------------------------------------------------------------------------------------------------------
-- Create a trace recording TextData, Duration, SPID, DatabaseID, DatabaseName, ObjectType, LoginName for the events RPC:Completed, SP:StmtCompleted & SQL:BatchCompleted
-- for all events running longer than 2000ms (2s)

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 2, N'D:\trace\TraceOutput_', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @bigintfilter = 2000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

-----------------------------------------------------------------------------------------------------------------------------------
-- STEP 2: LIST ACTIVE TRACES
-----------------------------------------------------------------------------------------------------------------------------------
SELECT * FROM :: fn_trace_getinfo(default)
--Properties:
--    * 1 - Trace options chosen when the trace was created with sp_trace_create procedure
--          2 TRACE_FILE_ROLLOVER
--              Specifies that when the max_file_size is reached, the current trace file is closed and a new file is created. All new records will be written to the new file. The new file will have the same name as the previous file, but an integer will be appended to indicate its sequence. For example, if the original trace file is named filename.trc, the next trace file is named filename_1.trc, the following trace file is filename_2.trc, and so on.
--              As more rollover trace files are created, the integer value appended to the file name increases sequentially.
--              SQL Server uses the default value of max_file_size (5 MB) if this option is specified without specifying a value for max_file_size.
--          4 SHUTDOWN_ON_ERROR
--              Specifies that if the trace cannot be written to the file for whatever reason, SQL Server shuts down. This option is useful when performing security audit traces.
--          8 TRACE_PRODUCE_BLACKBOX
--              Specifies that a record of the last 5 MB of trace information produced by the server will be saved by the server. TRACE_PRODUCE_BLACKBOX is incompatible with all other options.
--    * 2 - File name
--    * 3 - Maximum size of the trace file
--    * 4 - Stop time if specified with sp_trace_create
--    * 5 - Current trace status (0 = stopped. 1 = running.)

-----------------------------------------------------------------------------------------------------------------------------------
--STEP 3: Start/Stop/Close TRACE
-----------------------------------------------------------------------------------------------------------------------------------
sp_trace_setstatus 4, 0
--0 Stops the specified trace.
--1 Starts the specified trace.
--2 Closes the specified trace and deletes its definition from the server.

-----------------------------------------------------------------------------------------------------------------------------------
-- STEP 4: VIEW RESULTS (EG FOR TRACE #4)
-----------------------------------------------------------------------------------------------------------------------------------
SELECT * FROM :: fn_trace_geteventinfo(4)