September 4, 2014 at 11:05 am
We have been having some issues almost everyday between 12:30AM and 7:00AM. Now I am given a task to run a trace which starts at 11:30 PM and runs until 9:00AM. SO we are looking at close to 10 hours window. I would like to use the trace to see what caused the hiccups and maybe prevent a problem to reoccur.
Things that I would like to capture is that what runs between that time, which queries get executed, which queries are CPU intensive, just overall view of what happens in that 10 hour window while I am in BED relaxing.
Need I help with:
I can create a trace from Profiler but which are some of the useful events I should have
I also need help with a script which automatically starts at 11:30 PM tonight and runs until 9:00AM
We have plenty of space available so even if this trace generates 20GB worth of data, we should be OK.
What kind of permissions I have to have to run the trace?
Any help will be highly appreciated!
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
September 4, 2014 at 11:38 am
I ran a profiler and selected Standard template and then save that to a file and this is what I have.
This is what I need help with:
How do I get this to run at 11:30 PM sharp and stop at 9:00PM?
Is there a way to save the captured data in a form of a table instead of a file?
/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler */
/* Date: 09/04/2014 12:27:00 AM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1024
-- 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'C:\Users\dba\Desktop', @maxfilesize, NULL, 20
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, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - cb71af7e-e005-4ebe-916d-a110535d552c'
-- 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
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
September 4, 2014 at 12:26 pm
Start time, you need to schedule it to run somehow. Generally, I'd use SQL Agent, but you can put anything to work on it that can fire off at a given time. To make it stop, just use the @StopTime parameter. Here's the documentation on the command. It should help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2014 at 1:14 pm
Grant Fritchey (9/4/2014)
Start time, you need to schedule it to run somehow. Generally, I'd use SQL Agent, but you can put anything to work on it that can fire off at a given time. To make it stop, just use the @StopTime parameter. Here's the documentation on the command. It should help.
I was just using this document written by you.
http://www.sqlservercentral.com/articles/Performance+Tuning/71549/
1 more Q. Is there a way to put all that trace data into a table so I can sort the data by CPU time or startdate etc?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
September 4, 2014 at 1:30 pm
Yep, fn_trace_gettable is the way to do it.
Also, if you're looking at consuming trace data, I'd suggest getting a copy of the RML Utilities. YOu might have to poke around there to find the right version for 2008.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply