When troubleshooting a SQL Server problem, one of the
tools comes in mind is Profiler. This tool allows us to collect statements that
run on our SQL Server for troubleshooting.
Uses of SQL Profiler:
1.
Its major use is by applications to troubleshoot them
2.
In Performance issues it is hardly used as it eat lot
of resources and trouble your server which is already hit by performance
3.
It could be used to see performance of all stored
procedures just after a migration (you must have metrics of before migration)
The problem with using Profiler is that it is a client
tool and unless it is run on the server itself the connection may be lost and
your trace stops. This could hit you hard if you are running profiler for
hours. This usually happens right before the problem your trying to
troubleshoot occurs and you don’t end up collecting that valuable information
you need.
Client side trace:
1. Any time you open
SQL Server Profiler and run a trace, you’re running a client-side trace.
2. If you open SQL
Profiler on the server and run it there, it’s still client-side.
One alternative to using Profiler is to run a Server Side
Trace. This process runs on the server and collects trace statistics
pretty much the same way that you do using Profiler, but the process involves
running a T-SQL script to start, run and stop the trace vs. using the Profiler
GUI.
To run a server-side trace, one needs to create a script
using below steps.
1.
Open up Profiler and create a new trace as per your
requirement.
2.
Select Save to File and select a location to save trace
file.
3.
Select Enable File Rollover.
Select Enable Stop Time (Auto stop time for trace).Saurabh Sinha Page 1 6/14/2017
4.
Choose your events and columns from the Events
Selection tab.
5.
Run the trace and then stop it right away.
6.
From the File menu, choose Export > Script Trace
Definition > and save the script to file.
7.
Open your file in SSMS, making sure you’re connected to
the instance you want to profile.
8.
Search for sp_trace_create and replace fileName with exact location
where you want to save your trace ; Replace 0 with 2 to enable file rollover
Example:
exec @rc = sp_trace_create @TraceID output, 0, N’InsertFileNameHere’,
@maxfilesize, @Datetime
Syntax: Reference
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value ---> Set it 2 for file rollover
, [ @tracefile = ] ‘trace_file’
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] ‘stop_time’ ]
[ , [ @filecount = ] ‘max_rollover_files’ ]
The server side trace can be modified to collect any event
that the trace process can capture what data to collect
To create the trace for these events and columns the command would look as follows: this generated as sql script from above mention steps
/***********************************************/
/* Server Side Trace */
/***********************************************/
/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler */
/* Date: 04/07/2013
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = ‘2013-04-07 03:23:00.000’
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’InsertFileNameHere’, @maxfilesize,
@Datetime
if (@rc !=
0) goto error
-- Please modify 2nd column of sp_trace_create with 2 from 0
to enable file rollover
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Server Profiler -
d3b8ff1d-57c8-400f-81b7-d193ab7c4ce8’
-- 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
|
There are basically four components to this to get this running:
·
sp_trace_create - this procedure creates the
trace and has 5 parameters
o
TraceID - the ID of the trace
o
Options - various options that can be set
o
TraceFile - physical file name where you want to
write the trace file
o
MaxFileSize - size of the file, before closing
and creating subsequent files
o
StopTime - time to stop the trace
·
sp_trace_setevent - this procedure specifies
what event to capture and what column to capture
o
TraceID - the ID of the trace
o
EventID - the ID of the event you want to
capture
o
ColumnID - the ID of the column you want to
capture
o
On - whether you want to turn this event on or
off
·
sp_trace_setfilter - this procedure specifies
the filters to set. This determines whether you include or exclude data
o
TraceID - the ID of the trace
o
ColumnID - the ID of the column you want to set
the filter on
o
LogicalOperator - specifies whether this is an
AND or OR operation
o
ComparisonOperator - specify whether the value
is equal, greater then, less the, like, etc...
o
Value - the value to use for your comparison
·
sp_trace_setstatus
o
TraceID - the ID of the trace
o
Status - stop, start or close a trace
To add additional events and columns we would just include additional sp_trace_setevent commands such as the following to collect event
EXEC sp_trace_setevent @TraceID, 9, 1, @on EXEC sp_trace_setevent @TraceID, 9, 12, @on EXEC sp_trace_setevent @TraceID, 9, 13, @on |
To start, stop and delete a trace you use the following commands.
|
Task
|
Command
|
Notes
|
|
To find traceid
|
SELECT * FROM :: fn_trace_getinfo(default)
Select * from sys.traces
---SQL
2005 onwards
|
This will give you a list of all of the traces
that are running on the server.
|
|
To start a trace
|
sp_trace_setstatus traceid, 1
|
TraceId would be the value of the trace
|
|
To stop a trace
|
sp_trace_setstatus traceid, 0
|
TraceId would be the value of the trace
|
|
To close and delete a trace
|
sp_trace_setstatus traceid,0
sp_trace_setstatus traceid, 2 |
To delete you need to stop the trace first and
then you can delete the trace. This will close out the trace file that is
written.
|
Once the data has been collected you can view in no. of ways
1.
Just
drag and drop them in sql profiler
SELECT DB_NAME (DatabaseID) ,* FROM
fn_trace_gettable( convert (varchar(1000),
‘location of file\tracefile.trc’) , default)
order by
starttime
3.
Load
the data into a trace table and then run queries against the trace file.
Using below commands .
|
Task
|
Command
|
Notes
|
|
To load a trace
|
--Load into a new table SELECT * INTO Table_trace
FROM ::fn_trace_gettable(‘traceFileName’, DEFAULT) --Load into an existing table INSERT INTO Table_trace SELECT * FROM ::fn_trace_gettable(‘traceFileName’, DEFAULT) |
|
|
To query the table
|
SELECT * FROM Table_trace |
|
The following table describes the code values that users
may get following completion of the stored procedure.
|
Return code
|
Description
|
|
0
|
No
error.
|
|
1
|
Unknown
error.
|
|
2
|
The
trace is currently running. Changing the trace at this time will result in an
error.
|
|
3
|
The
specified event is not valid. The event may not exist or it is not an
appropriate one for the store procedure.
|
|
4
|
The
specified column is not valid.
|
|
9
|
The
specified trace handle is not valid.
|
|
11
|
The
specified column is used internally and cannot be removed.
|
|
12
|
Could
not create a trace file.(The system cannot find the path specified or file
already exists)
|
|
13
|
Out
of memory. Returned when there is not enough memory to perform the specified
action.
|
|
14
|
The
requested trace stop time has been already passed.
|
|
16
|
The
function is not valid for this trace.
|
Blog credit to Saurabh Sinha http://saurabhsinhainblogs.blogspot.com/
No comments:
Post a Comment