Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior
Example, if trace flag 3205 is set when an instance of SQL Server starts, hardware compression for tape drivers is disabled. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems.
Remarks: In SQL Server, there are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only to that connection. Global trace flags are set at the server level and are visible to every connection on the server. Some flags can only be enabled as global, and some can be enabled at either global or session scope.
select * from sys.traces --Find the trace ID QUERY
EXEC sp_trace_setstatus @traceid = 2, @status = 0
--Then
EXEC sp_trace_setstatus @traceid = 2, @status = 2
Examples
The following example sets trace flag 3205 on by using DBCC TRACEON.
DBCC TRACEON (3205,-1)
fn_trace_gettable( filename , number_files )
–Read trace file using fn_trace_gettable
SELECT *
FROM fn_trace_gettable(‘D:\TraceFile.trc’, default) Trace
The output of fn_trace_gettable is same as the data columns displayed in SQL Profiler, for e.g.
TextData
BinaryData
DatabaseID
ObjectName
ApplicationName
EventClass
DatabaseName …
When analyzing for performance bottlenecks, we would general look at the following
TextData
Duration
StartTime
EndTime
Reads
Writes
EventClass
DatabaseName
There are various events which are exposed by SQL Server. You can get a full list with the description on MSDN (sp_trace_setevent)
–Read the trace file and select only a few events. Display the descriptive names of the events selected
SELECT EventClass, Events.Name As EventClassName, TextData,
Duration, StartTime, EndTime, Reads, Writes
–INTO Trace_Cols
FROM fn_trace_gettable(‘D:\TraceFile.trc’, default) Trace
INNER JOIN sys.trace_events Events
ON Trace.EventClass = Events.trace_event_id
WHERE ApplicationName <> ‘Report Server’
AND EventClass IN (12, 41, 43, 45, 10) –select only a few events
AND SUBSTRING(TextData, 1,24 ) <> ‘exec sp_reset_connection’
ORDER BY duration
Then you can run a query to aggregate the data such as this one
SELECT COUNT(*) AS TotalExecutions, EventClass, CAST(TextData as nvarchar(2000)) ,SUM(Duration) AS DurationTotal ,SUM(CPU) AS CPUTotal ,SUM(Reads) AS ReadsTotal ,SUM(Writes) AS WritesTotal FROM Trace_Cols GROUP BY EventClass, CAST(TextData as nvarchar(2000))ORDER BY ReadsTotal DESC
SELECT event_time, session_id, session_server_principal_name, server_instance_name, statement
--cast(datediff(s, cast(event_time as datetime), getdate()) as varchar(5))
FROM fn_get_audit_file ('m:\Data\SQLServer\MSSQL10_50.SIVISTPROD19\MSSQL\Backup\SQL_Audit\SQL_Audit_540815E3-66E1-4CAF-8EAD-8A04109E209C_0_130394941140390000.sqlaudit', NULL, NULL)
where session_id = 108
and event_time between '2014-03-25 00:00:00.0000000' and '2014-03-26 00:00:00.0000000'
and (statement like '%RECONFIGURE WITH OVERRIDE%' or
statement like '%xp_cmdshell%')
order by cast(event_time as datetime)
USING DMV The Following script gives you the result
SELECT TOP 10
SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads,
qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC
CHECK THE DEFAULT TRACE FILE LOCATION QUERY
SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 256)) AS DefaultTraceLocation
FROM sys.traces
WHERE is_default = 1