SCHEDULING a trace from PROFILER (sql server 2005)

Last Post 23 Jan 2008 10:25 AM by oshaw. 10 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
oshaw
New Member
New Member

--
18 Jan 2008 02:54 AM
Many times, I have tried to create a script to run a profiler trace but although it creates the script file and seems to run, the trace file is always empty. The database is in 2000 mode so initially, I created the script in 2000 mode. The script was created and I was able to use the traceID to seeminlg start and stop the trace but the file I assigned was empty. The same thing happened in 2005 mode. These are the instructions used:

http://support.microsoft.com/kb/270599
SQLUSA
New Member
New Member

--
18 Jan 2008 06:25 AM
What are you looking for? Slow running queries? Read hogs? Blocking?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
oshaw
New Member
New Member

--
18 Jan 2008 11:21 AM
thank You. I may not have explained my steps clearly. I did exactly what you suggested. using, not the script in the document but the script created after a short trace. It creates an empty trace file.
oshaw
New Member
New Member

--
21 Jan 2008 04:18 AM
This is the script (created by profiler):

/****************************************************/
/* Created by: SQL Profiler */
/* Date: 01/17/2008 02:31:13 PM */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500

-- 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, 0, N'c:\mytrace', @maxfilesize, NULL
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, 30, 3, @on
exec sp_trace_setevent @TraceID, 30, 11, @on
exec sp_trace_setevent @TraceID, 30, 12, @on
exec sp_trace_setevent @TraceID, 30, 26, @on
exec sp_trace_setevent @TraceID, 30, 40, @on
exec sp_trace_setevent @TraceID, 40, 3, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 26, @on
exec sp_trace_setevent @TraceID, 40, 40, @on
exec sp_trace_setevent @TraceID, 41, 3, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 26, @on
exec sp_trace_setevent @TraceID, 41, 40, @on
exec sp_trace_setevent @TraceID, 42, 3, @on
exec sp_trace_setevent @TraceID, 42, 11, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 42, 26, @on
exec sp_trace_setevent @TraceID, 42, 40, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 26, @on
exec sp_trace_setevent @TraceID, 45, 40, @on


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

exec sp_trace_setfilter @TraceID, 40, 1, 6, N'CRFTracking'


-- 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

____________________________________________________________________________

After I issue the command to stop the trace -
sp_trace_setstatus @TraceID, 0

I check and the file is empty. It exist but it is empty.

_____________________________________________________________________________

We do sp 1 applied.

SQLUSA
New Member
New Member

--
21 Jan 2008 05:40 AM
This is one reason I never run trace from command prompt.

You can start as many traces as you need from the GUI SQL Server Profiler module and see what's is going on, record simultaneously.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
oshaw
New Member
New Member

--
23 Jan 2008 07:44 AM
You're quite right about running a trace on a busy system! It absolutely does pull on performance.

gunneyk - Thank you for your suggestions. The trace was the standard tuning trace with the selection criteria. I did try viewing the trace before and after the close command thinking it was a source of the problem. The trace file did exist but each time it was empty. It may have been the selection criteria and the absence of column data. I will try again as I need to be able to schedule traces for short periods of time so as not to pull on performance. Periodically, I've been running from the GUI but of course this does not suffice to collect data during my off hours.


SQLUSA
New Member
New Member

--
23 Jan 2008 08:05 AM
>You're quite right about running a trace on a busy system! It absolutely does pull on performance.

Busy is a relative term. Give me figures pls....describe your platform......

If FOCUSED tracing (well selected events and filters) bring down your system when you are monitoring and saving trace to a second server, then you have an underpowered system....probably the easiest solution hardware upgrade, also database access optimization (sprocs, queries, batch jobs).

Let us know what is your environment....there are several ways you can improve it....

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
oshaw
New Member
New Member

--
23 Jan 2008 10:25 AM
gunneyk

You were right again! I selected the trace without selection criteria and it worked like a charm. The trace must be stopped and deleted before the file can be used in the gui. The filter I used evidently screened out all records so the screening field is probably not what I think it is.

Thanks again!!!!
oshaw
New Member
New Member

--
23 Jan 2008 10:32 AM
SQLUSA

Our system is pretty stressed. We have eased some of the pain by tweeking queries and moving databases to other servers but the particular server in question was running at 98 - 99% CPU usage with lots of timeouts. Our CPU usage is much better. I enlarged and moved the tempdb.

We need to monitor to make sure our code is as efficient as possible.

Thank You
SQLUSA
New Member
New Member

--
23 Jan 2008 12:51 PM
Russell,

The biggest myth in DBA circles that SQL Server Profiler monitoring will pull your server under!

Never, if you monitor the way I suggested in the previous post, you notice nothing!

I agree with Russell & Andy that careless monitoring may cause problems. You have to be an expert or get some expert help (consultant).

Here is what pulls your server down:

1. Poorly written sprocs
2. Missing indexes
3. Fragmented indexes
4. Not enough memory
5. Poorly configured/undersized disk system
6. Batch jobs running frequently on OLTP system
7. Blocking (poorly designed tables/transactional software)

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!





SQLUSA
New Member
New Member

--
23 Jan 2008 12:59 PM
Oshaw,

Obviously running 98%+ withouth the trace. So SQL Server Profiler cannot be blamed.

If you are not in the designing of a moon shuttle system (heavy scientific cpu use), the reason you are running that high: TOO MANY READS.

Setup monitoring the way I suggested (duration > 3000). Check the trace table for frequently executed READ HOGS.

Optimize/kick out the READ HOGS one by one.

You will see improvent.

Also make sure that your indexes are not in poor shape. Don't forget the FILL FACTOR when reindexing for dynamic tables (are you reindexing every night the busy OLTP tables?)

If your underlying database design is poor, that is a different issue. It requires reengineering, BIG undertaking.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!

You are not authorized to post a reply.

Acceptable Use Policy