November 28, 2008 at 1:52 pm
Don't use the profiler gui at all. Use profiler only to create a script. The profiler gui doesn't directly write to a file. It pulls the trace events into memory and writes them out to disk at intervals. It's not as slow as writing to a table, but it's not that much better.
Set up a trace in profiler against another server (dev server) with the events you want. Run it for a few moments, stop it, go to the file menu select export and script trace to file.
Take that file, open in querying tool of your choice and connect to the server that's having perf problems. There are a couple things in the script that you need to change. Main one is the file name. Use a drive that does not have any SQL data or log files on. Don't put the .trc extension. The trace procs add that automatically. The path and file you specify will be on that server, not on your local machine, so use drive letters
You can also set an end time if you want the trace to end automatically. Otherwise use the sp_trace_setstatus to stop the trace.
Run that script from the querying tool against your server. That is what is meant by a server-side trace.
Isn't there a proc to load the data?
Not a proc, but a function. fn_trace_gettable(). It takes a single parameter - the full path and name of the trace file to read.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2008 at 2:11 pm
I agree with Gail. The other issue with using the Profiler GUI is that events can be lost on a very busy server, while a server-side trace will not lose data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 29, 2008 at 5:54 am
Thanks Gail for the instructions on how to create the file with minimal impact. I will test it today and let you know the results.
November 29, 2008 at 6:33 am
GilaMonster (11/28/2008)
Set up a trace in profiler against another server (dev server) with the events you want. Run it for a few moments, stop it, go to the file menu select export and script trace to file.Take that file, open in querying tool of your choice and connect to the server that's having perf problems. There are a couple things in the script that you need to change. Main one is the file name. Use a drive that does not have any SQL data or log files on. Don't put the .trc extension. The trace procs add that automatically. The path and file you specify will be on that server, not on your local machine, so use drive letters
You can also set an end time if you want the trace to end automatically. Otherwise use the sp_trace_setstatus to stop the trace.
This is not clear - exactly how do you execute the stop trace because I received an error message when trying to stop the trace then the file would not open, saying the trc file was corrupt.
The command I executed to stop the trace:
declare @TraceID int
exec sp_trace_setstatus @TraceID, 0
The result:
Msg 214, Level 16, State 3, Procedure sp_trace_setstatus, Line 2
Procedure expects parameter '@traceid' of type 'int'.
The command I entered trying to open the file:
SELECT * FROM ::fn_trace_gettable('C:\TEMP\Trace_files\SQLServerTrace_20081201.trc', default)
The result:
Msg 567, Level 16, State 1, Line 2
File 'C:\TEMP\Trace_files\SQLServerTrace_20081201.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.
November 29, 2008 at 6:42 am
Just so you have all the information I will post the script also:
/****************************************************/
/* Created by: SQL Server Profiler */
/* Date: 11/29/2008 07:59:53 AM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- 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:\TEMP\Trace_files\SQLServerTrace_20081201', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
-- SQL Server Yukon specific events will not be scripted
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 92, 11, @on
exec sp_trace_setevent @TraceID, 92, 15, @on
exec sp_trace_setevent @TraceID, 92, 8, @on
exec sp_trace_setevent @TraceID, 92, 12, @on
exec sp_trace_setevent @TraceID, 92, 13, @on
exec sp_trace_setevent @TraceID, 92, 21, @on
exec sp_trace_setevent @TraceID, 92, 25, @on
exec sp_trace_setevent @TraceID, 92, 10, @on
exec sp_trace_setevent @TraceID, 92, 14, @on
exec sp_trace_setevent @TraceID, 93, 11, @on
exec sp_trace_setevent @TraceID, 93, 15, @on
exec sp_trace_setevent @TraceID, 93, 8, @on
exec sp_trace_setevent @TraceID, 93, 12, @on
exec sp_trace_setevent @TraceID, 93, 13, @on
exec sp_trace_setevent @TraceID, 93, 21, @on
exec sp_trace_setevent @TraceID, 93, 25, @on
exec sp_trace_setevent @TraceID, 93, 10, @on
exec sp_trace_setevent @TraceID, 93, 14, @on
exec sp_trace_setevent @TraceID, 22, 11, @on
exec sp_trace_setevent @TraceID, 22, 31, @on
exec sp_trace_setevent @TraceID, 22, 8, @on
exec sp_trace_setevent @TraceID, 22, 12, @on
exec sp_trace_setevent @TraceID, 22, 1, @on
exec sp_trace_setevent @TraceID, 22, 21, @on
exec sp_trace_setevent @TraceID, 22, 6, @on
exec sp_trace_setevent @TraceID, 22, 10, @on
exec sp_trace_setevent @TraceID, 22, 14, @on
exec sp_trace_setevent @TraceID, 21, 11, @on
exec sp_trace_setevent @TraceID, 21, 31, @on
exec sp_trace_setevent @TraceID, 21, 8, @on
exec sp_trace_setevent @TraceID, 21, 12, @on
exec sp_trace_setevent @TraceID, 21, 1, @on
exec sp_trace_setevent @TraceID, 21, 21, @on
exec sp_trace_setevent @TraceID, 21, 6, @on
exec sp_trace_setevent @TraceID, 21, 10, @on
exec sp_trace_setevent @TraceID, 21, 14, @on
exec sp_trace_setevent @TraceID, 33, 11, @on
exec sp_trace_setevent @TraceID, 33, 31, @on
exec sp_trace_setevent @TraceID, 33, 8, @on
exec sp_trace_setevent @TraceID, 33, 12, @on
exec sp_trace_setevent @TraceID, 33, 1, @on
exec sp_trace_setevent @TraceID, 33, 21, @on
exec sp_trace_setevent @TraceID, 33, 6, @on
exec sp_trace_setevent @TraceID, 33, 10, @on
exec sp_trace_setevent @TraceID, 33, 14, @on
exec sp_trace_setevent @TraceID, 25, 15, @on
exec sp_trace_setevent @TraceID, 25, 8, @on
exec sp_trace_setevent @TraceID, 25, 12, @on
exec sp_trace_setevent @TraceID, 25, 13, @on
exec sp_trace_setevent @TraceID, 25, 21, @on
exec sp_trace_setevent @TraceID, 25, 25, @on
exec sp_trace_setevent @TraceID, 25, 6, @on
exec sp_trace_setevent @TraceID, 25, 10, @on
exec sp_trace_setevent @TraceID, 25, 14, @on
exec sp_trace_setevent @TraceID, 25, 11, @on
exec sp_trace_setevent @TraceID, 59, 8, @on
exec sp_trace_setevent @TraceID, 59, 12, @on
exec sp_trace_setevent @TraceID, 59, 13, @on
exec sp_trace_setevent @TraceID, 59, 21, @on
exec sp_trace_setevent @TraceID, 59, 25, @on
exec sp_trace_setevent @TraceID, 59, 6, @on
exec sp_trace_setevent @TraceID, 59, 10, @on
exec sp_trace_setevent @TraceID, 59, 14, @on
exec sp_trace_setevent @TraceID, 59, 11, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 21, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 11, 11, @on
exec sp_trace_setevent @TraceID, 11, 8, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 11, 1, @on
exec sp_trace_setevent @TraceID, 11, 21, @on
exec sp_trace_setevent @TraceID, 11, 6, @on
exec sp_trace_setevent @TraceID, 11, 10, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 38, 11, @on
exec sp_trace_setevent @TraceID, 38, 8, @on
exec sp_trace_setevent @TraceID, 38, 12, @on
exec sp_trace_setevent @TraceID, 38, 1, @on
exec sp_trace_setevent @TraceID, 38, 21, @on
exec sp_trace_setevent @TraceID, 38, 6, @on
exec sp_trace_setevent @TraceID, 38, 10, @on
exec sp_trace_setevent @TraceID, 38, 14, @on
exec sp_trace_setevent @TraceID, 38, 34, @on
exec sp_trace_setevent @TraceID, 35, 11, @on
exec sp_trace_setevent @TraceID, 35, 8, @on
exec sp_trace_setevent @TraceID, 35, 12, @on
exec sp_trace_setevent @TraceID, 35, 1, @on
exec sp_trace_setevent @TraceID, 35, 21, @on
exec sp_trace_setevent @TraceID, 35, 6, @on
exec sp_trace_setevent @TraceID, 35, 10, @on
exec sp_trace_setevent @TraceID, 35, 14, @on
exec sp_trace_setevent @TraceID, 35, 34, @on
exec sp_trace_setevent @TraceID, 34, 11, @on
exec sp_trace_setevent @TraceID, 34, 8, @on
exec sp_trace_setevent @TraceID, 34, 12, @on
exec sp_trace_setevent @TraceID, 34, 1, @on
exec sp_trace_setevent @TraceID, 34, 21, @on
exec sp_trace_setevent @TraceID, 34, 6, @on
exec sp_trace_setevent @TraceID, 34, 10, @on
exec sp_trace_setevent @TraceID, 34, 14, @on
exec sp_trace_setevent @TraceID, 34, 34, @on
exec sp_trace_setevent @TraceID, 36, 11, @on
exec sp_trace_setevent @TraceID, 36, 8, @on
exec sp_trace_setevent @TraceID, 36, 12, @on
exec sp_trace_setevent @TraceID, 36, 1, @on
exec sp_trace_setevent @TraceID, 36, 21, @on
exec sp_trace_setevent @TraceID, 36, 6, @on
exec sp_trace_setevent @TraceID, 36, 10, @on
exec sp_trace_setevent @TraceID, 36, 14, @on
exec sp_trace_setevent @TraceID, 36, 34, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 21, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 39, 11, @on
exec sp_trace_setevent @TraceID, 39, 8, @on
exec sp_trace_setevent @TraceID, 39, 12, @on
exec sp_trace_setevent @TraceID, 39, 1, @on
exec sp_trace_setevent @TraceID, 39, 21, @on
exec sp_trace_setevent @TraceID, 39, 6, @on
exec sp_trace_setevent @TraceID, 39, 10, @on
exec sp_trace_setevent @TraceID, 39, 14, @on
exec sp_trace_setevent @TraceID, 39, 34, @on
exec sp_trace_setevent @TraceID, 37, 15, @on
exec sp_trace_setevent @TraceID, 37, 8, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 13, @on
exec sp_trace_setevent @TraceID, 37, 21, @on
exec sp_trace_setevent @TraceID, 37, 6, @on
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 18, @on
exec sp_trace_setevent @TraceID, 37, 34, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 42, 11, @on
exec sp_trace_setevent @TraceID, 42, 8, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 21, @on
exec sp_trace_setevent @TraceID, 42, 6, @on
exec sp_trace_setevent @TraceID, 42, 10, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 25, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 27, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 13, @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, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 25, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - b7ad8805-2bd1-45d8-bf56-c0384ea9ccb5'
-- 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
November 29, 2008 at 9:22 am
The command I executed to stop the trace:
declare @TraceID int
exec sp_trace_setstatus @TraceID, 0
You need to set the variable to the ID of the trace you're trying to manipulate. There can be many traces running concurrently. SQL can't know which one you mean if you don't specify the ID
When the script that creates the trace runs, it will print out a trace ID at the end.
select TraceID=@TraceID
Use that traceID when you want to stop the trace. You can also use fn_trace_getinfo function. Specify 0 for the parameter and it will display all traces that exist.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2008 at 9:24 am
barb.wendling (11/29/2008)
The command I executed to stop the trace:
declare @TraceID int
exec sp_trace_setstatus @TraceID, 0
The result:
Msg 214, Level 16, State 3, Procedure sp_trace_setstatus, Line 2
Procedure expects parameter '@traceid' of type 'int'.
You never set the value of @TraceId in this code snippet so it is NULL and you need to supply a value. You can find it but runnning fn_trace_getinfo.
The command I entered trying to open the file:
SELECT * FROM ::fn_trace_gettable('C:\TEMP\Trace_files\SQLServerTrace_20081201.trc', default)
The result:
Msg 567, Level 16, State 1, Line 2
File 'C:\TEMP\Trace_files\SQLServerTrace_20081201.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.
Are you trying to open the trace file using Query Analyzer on your PC? If you are that is why it is failing with the error because query analyzer is trying to open a file on your local PC. You need to specify a path to the file on the SQL Server or you need to open it using query analyzer on the server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 29, 2008 at 9:34 am
Jack Corbett (11/29/2008)
Are you trying to open the trace file using Query Analyzer on your PC? If you are that is why it is failing with the error because query analyzer is trying to open a file on your local PC. You need to specify a path to the file on the SQL Server or you need to open it using query analyzer on the server.
Or, preferably, copy the trace file to your local machine and work with it there.
p.s. all of the trace procedures and functions are documented in Books Online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2008 at 5:46 am
To complete the testing, I was able to:
-execute the script to start the trace
-write the trace results to a file on the server where the data/log files are NOT located
-stop the trace (by entering the traceID displayed during script execution)
-copy the trace file to my local server
-load the results into a table for analysis
So, I think I am ready for the call that the server is sluggish. Hopefully, I can capture what is causing the issue.
I was not able to delete the trace files after the trace was stopped. Can you explain why/how to remove these files?
Thank you for all your help. I will let you know the outcome of the events.
November 30, 2008 at 6:49 am
barb.wendling (11/30/2008)
I was not able to delete the trace files after the trace was stopped. Can you explain why/how to remove these files?
If the trace is stopped and closed (not just paused) you should be able to delete them like any other file. I've never had problems deleting trace files
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2008 at 9:03 am
I stopped the trace, but what command do you use to close the trace?
November 30, 2008 at 9:27 am
Per Books Online:
sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status
Arguments
[ @traceid = ] trace_id
Is the ID of the trace to be modified. trace_id is int, with no default. The user employs this trace_id value to identify, modify, and control the trace. For information about retrieving the trace_id, see fn_trace_getinfo (Transact-SQL).
[ @status = ] status
Specifies the action to implement on the trace. status is int, with no default.
The following table lists the status that may be specified.
Status Description
0
Stops the specified trace.
1
Starts the specified trace.
2
Closes the specified trace and deletes its definition from the server.
Note:
A trace must be stopped first before it can be closed. A trace must be stopped and closed first before it can be viewed.
If you're going to be working with server-side traces, I would recommend you read over the sections in BoL on each of the procedures. It's waaay faster than asking here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2008 at 4:44 am
Thanks, I have downloaded the SQL 2005 - 2007 version of BoL. I have read online and others agree it is not comprehensive or very helpful on this subject, probably because the Profiler GUI is provided with SQL server. For example, I did not see the EventClass ID definitions in BoL.
At any rate, with your assistance, I have been able to get a much better understanding of traces and I am optimistic about identifing the server "problems". I will post results later. Thanks for following up.
December 1, 2008 at 5:05 am
barb.wendling (12/1/2008)
For example, I did not see the EventClass ID definitions in BoL.
In the 2008 Books Online, the section title is "SQL Server Event Class Reference" and the address is ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/0f0fe567-e115-4ace-b63c-73dc3428c0f6.htm
In the 2005 Books Online, the section title is "SQL Server Event Class Reference" and the address is ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0f0fe567-e115-4ace-b63c-73dc3428c0f6.htm
Or, if you prefer online.
SQL 2008: http://msdn.microsoft.com/en-us/library/ms175481.aspx
SQL 2005: http://msdn.microsoft.com/en-us/library/ms175481(SQL.90).aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 10:08 am
Yesterday was interesting, I was prepared. After all the traces were evaluated, we found that it wasn't the SQL server. There were some report queries that were taking 8 minutes to complete. But the suspicious delays for the users has been identified as the Crystal Report server, not the database or DB server. 😀 I am still monitoring closely.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply