Trace Flags :

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.

sp_trace_setstatus: Modifies the current state of the specified trace.

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)

Reading SQL Trace Files using fn_trace_gettable function

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

Reading SQL Audit Logs ....

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

http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/