SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 scheduling sql profiler
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 06/22/2008 :  05:47:06  Show Profile  Reply with Quote
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
7174 Posts

Posted - 06/22/2008 :  07:09:56  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 06/22/2008 :  08:02:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 06/22/2008 :  08:58:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1393 Posts

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

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 06/22/2008 :  09:05:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 06/23/2008 :  06:42:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

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

esthera
Flowing Fount of Yak Knowledge

1393 Posts

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

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 06/28/2008 :  22:24:52  Show Profile  Reply with Quote
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

USA
100 Posts

Posted - 06/29/2008 :  12:32:45  Show Profile  Send maninder a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 06/29/2008 :  12:44:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 06/29/2008 :  13:07:39  Show Profile  Reply with Quote
yes thanks - i got the scheduled procedure working
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000