| Author |
 Topic  |
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
 Posted - 06/22/2008 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 06/22/2008 : 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
|
 |
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 06/22/2008 : 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? |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 06/22/2008 : 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. |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 06/22/2008 : 09:01:27
|
| and then you import it to a table and run queries based on the cpu time and duration? |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 06/22/2008 : 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. |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 06/23/2008 : 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? |
|
|
|
rmiao
Flowing Fount of Yak Knowledge
USA
7266 Posts |
Posted - 06/23/2008 : 23:03:10
|
| Possible to put date time in file name with dynamic sql. |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 06/24/2008 : 03:27:30
|
| can you guide me with how I would do that? |
|
|
|
rmiao
Flowing Fount of Yak Knowledge
USA
7266 Posts |
Posted - 06/28/2008 : 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 |
|
|
|
maninder
Posting Yak Master
USA
100 Posts |
Posted - 06/29/2008 : 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 |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 06/29/2008 : 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. |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 06/29/2008 : 13:07:39
|
| yes thanks - i got the scheduled procedure working |
|
|
| |
Topic |
|
|
|
scheduling sql profiler
