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 minutesCreate Procedure dbo.Usp_Tracefor30minutesasset nocount ondeclare @stop datetime set @stop = dateadd(mi,30,getdate())declare @rc intdeclare @TraceID intdeclare @maxfilesize bigintset @maxfilesize = 50-- create traces and saves in folder specifiedexec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @stop if (@rc != 0) goto error-- Set the eventsdeclare @on bitset @on = 1exec sp_trace_setevent @TraceID, 17, 12, @onexec sp_trace_setevent @TraceID, 17, 1, @onexec sp_trace_setevent @TraceID, 17, 9, @onexec sp_trace_setevent @TraceID, 17, 6, @on -- 17 - Existing Connectionsexec sp_trace_setevent @TraceID, 17, 10, @onexec sp_trace_setevent @TraceID, 17, 14, @onexec sp_trace_setevent @TraceID, 17, 11, @onexec sp_trace_setevent @TraceID, 10, 15, @onexec sp_trace_setevent @TraceID, 10, 16, @onexec sp_trace_setevent @TraceID, 10, 1, @on -- 10 - RPC:Completedexec sp_trace_setevent @TraceID, 10, 17, @onexec sp_trace_setevent @TraceID, 10, 2, @onexec sp_trace_setevent @TraceID, 10, 10, @onexec sp_trace_setevent @TraceID, 10, 18, @onexec sp_trace_setevent @TraceID, 10, 11, @onexec sp_trace_setevent @TraceID, 10, 12, @onexec sp_trace_setevent @TraceID, 10, 13, @onexec sp_trace_setevent @TraceID, 10, 6, @onexec sp_trace_setevent @TraceID, 10, 14, @onexec sp_trace_setevent @TraceID, 43, 15, @onexec sp_trace_setevent @TraceID, 43, 1, @onexec sp_trace_setevent @TraceID, 43, 9, @on -- 43 - SP:Completedexec sp_trace_setevent @TraceID, 43, 10, @onexec sp_trace_setevent @TraceID, 43, 11, @onexec sp_trace_setevent @TraceID, 43, 12, @onexec sp_trace_setevent @TraceID, 43, 13, @onexec sp_trace_setevent @TraceID, 43, 6, @onexec sp_trace_setevent @TraceID, 43, 14, @onexec sp_trace_setevent @TraceID, 37, 1, @onexec sp_trace_setevent @TraceID, 37, 9, @onexec sp_trace_setevent @TraceID, 37, 6, @on -- 37 - SP:Recompileexec sp_trace_setevent @TraceID, 37, 10, @onexec sp_trace_setevent @TraceID, 37, 14, @onexec sp_trace_setevent @TraceID, 37, 11, @onexec sp_trace_setevent @TraceID, 37, 12, @onexec sp_trace_setevent @TraceID, 12, 15, @onexec sp_trace_setevent @TraceID, 12, 16, @onexec sp_trace_setevent @TraceID, 12, 1, @on -- 12 - SQL:BatchCompletedexec sp_trace_setevent @TraceID, 12, 9, @onexec sp_trace_setevent @TraceID, 12, 17, @onexec sp_trace_setevent @TraceID, 12, 6, @onexec sp_trace_setevent @TraceID, 12, 10, @onexec sp_trace_setevent @TraceID, 12, 14, @onexec sp_trace_setevent @TraceID, 12, 18, @onexec sp_trace_setevent @TraceID, 12, 11, @onexec sp_trace_setevent @TraceID, 12, 12, @onexec sp_trace_setevent @TraceID, 12, 13, @onexec sp_trace_setevent @TraceID, 13, 12, @onexec sp_trace_setevent @TraceID, 13, 1, @onexec sp_trace_setevent @TraceID, 13, 9, @on -- 13 SQL :BatchStartingexec sp_trace_setevent @TraceID, 13, 6, @onexec sp_trace_setevent @TraceID, 13, 10, @onexec sp_trace_setevent @TraceID, 13, 14, @onexec sp_trace_setevent @TraceID, 13, 11, @on-- Set the Filtersdeclare @intfilter intdeclare @bigintfilter bigintexec 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 startexec sp_trace_setstatus @TraceID, 1-- display trace id for future referencesselect TraceID=@TraceIDgoto finisherror: select ErrorCode=@rcfinish: go--Exec SPExec dbo.Usp_Tracefor30minutes |
|
|
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? |
|
|
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. |
|
|
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? |
|
|
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. |
|
|
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? |
|
|
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. |
|
|
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? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-28 : 22:24:52
|
use mastergodeclare @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=@filego |
|
|
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 |
|
|
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. |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2008-06-29 : 13:07:39
|
yes thanks - i got the scheduled procedure working |
|
|
|
|
|