Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 scheduling sql profiler

Author  Topic 

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-22 : 05:47:06
what's the best way to schedule sql profiler to keep track of any big queries run on my database so I can know which queries are using the cpu (and how often) and try to optimize them more.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-22 : 07:09:56

Here is the script I have written which will stop after 30 mins:(Do some testing with this and it can be scheduled as well)

--Stored Procedure to run trace for 30 minutes

Create Procedure dbo.Usp_Tracefor30minutes
as
set nocount on

declare @stop datetime
set @stop = dateadd(mi,30,getdate())
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
-- create traces and saves in folder specified
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @stop
if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
-- 17 - Existing Connections
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
-- 10 - RPC:Completed
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, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
-- 43 - SP:Completed
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 9, @on
exec sp_trace_setevent @TraceID, 37, 6, @on
-- 37 - SP:Recompile
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @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
-- 12 - SQL:BatchCompleted
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, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
-- 13 SQL :BatchStarting
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


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

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler'
set @bigintfilter = 5000-- Track queries more than 5 sec.
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

--Exec SP
Exec dbo.Usp_Tracefor30minutes

Go to Top of Page

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-22 : 08:02:03
ok i'm actually looking to start traces very other hour for an hour and then the best tools to analyze the longer queries in time and in cpu usage?

there is so much on the web but i'm not finding really what i need.

what do you advise?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-22 : 08:58:39
Change it to how long you want. Like dateadd(hh,1,getdate()) for 1 hour.It will track all queries more than 5 secs and CPU usage as well.
Go to Top of Page

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-22 : 09:01:27
and then you import it to a table and run queries based on the cpu time and duration?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-22 : 09:05:43
you can see in the file you stored. But in offline hours, you can import in tables from files like this:

SELECT * INTO yourtracetable FROM ::fn_trace_gettable('C:\MyTrace.trc', default)

and query.
Go to Top of Page

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-23 : 06:42:53
thanks -- will it overwrite the file or add to it if I schedule this?
can I schedule it to right to a file with the datetime?
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-23 : 23:03:10
Possible to put date time in file name with dynamic sql.
Go to Top of Page

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-24 : 03:27:30
can you guide me with how I would do that?
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-28 : 22:24:52
use master
go
declare @file varchar(255)
select @file = '\\host\share\db_name_' + convert(char(8), getdate(), 112) + convert(char(2), datepart(hh, getdate()))
+ convert(char(2), datepart(mi, getdate())) + '.bak'

backup DATABASE db_name TO DISK=@file
go
Go to Top of Page

maninder
Posting Yak Master

100 Posts

Posted - 2008-06-29 : 12:32:45
Create a Trace in SQL Profiler and Script it out as you want.
you can also Use it to Stop at a vertain interval.
All these can be scripted from the SQL Profiler--> File--> Export--> Script Trace Definition.

Maninder
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-29 : 12:44:16
quote:
Originally posted by maninder

Create a Trace in SQL Profiler and Script it out as you want.
you can also Use it to Stop at a vertain interval.
All these can be scripted from the SQL Profiler--> File--> Export--> Script Trace Definition.

Maninder



That has already been explained.
Go to Top of Page

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-29 : 13:07:39
yes thanks - i got the scheduled procedure working
Go to Top of Page
   

- Advertisement -