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?

  • 2
    Extended Events: simple-talk.com/sql/database-administration/… Not sure if the Extended Events interface in SSMS works against a SQL Server 2008 instance though. – user507 Feb 21 '14 at 17:41
  • 1
    Beware that if you plan to use query_post_execution_showplan you should read Jonathan Kehayias warning in the discussion thread at: simple-talk.com/sql/database-administration/… – RLF Feb 21 '14 at 18:21
  • 1
    SSMS 2012 does not show an Extended Events node for pre-2012 servers. Also, some of the Extended Events objects changed from 2008 R2 and 2012 (I don't remember the specifics), so the DDL may need to be different between versions. – Jon Seigel Feb 21 '14 at 18:53
  • Is there any example of extended events for the purpose? – u23432534 Feb 21 '14 at 22:09

You can identify problematic queries through DMVs using this query:

USE [master]
SELECT  session_id,
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0

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.

| improve this answer | |

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.

| improve this answer | |
  • Another option is a commercial solution such as Confio Ignite (what we use), Red Gate SQL Monitor, or any of the other tools out there. Any good product will capture the queries that are running and show stats for number of execution and wait stats as well. – RubberChickenLeader Feb 21 '14 at 20:23
  • My vote to Adam Machanic's solution too, is very effective and tons of people are using it so it's fully tested and approved by the community. – Yaroslav Feb 22 '14 at 22:18

SQL for starting/stopping traces:

-- 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

select ErrorCode=@rc


SELECT * FROM :: fn_trace_getinfo(default)
--    * 1 - Trace options chosen when the trace was created with sp_trace_create procedure
--              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.
--              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.

SELECT * FROM :: fn_trace_geteventinfo(4)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.