TexCopy Sql Server 2005

While working in SQL Server, most of the time we create trace files(.trc) to identify performance bottleneck. You can create trace files using SQL Profiler or you can use SQL as well. Most of us would go SQL Profiler way, because simply putting is very easy and quite elegant too, further more it gives you WYSIWYG environment.

Now suppose somebody has already generated a trace file, and you just how to analyze it. Here too you can do two things, use SQL Profiler or use SQL to read the trace file. SQL Profiler is the tool of choice over here too, for all the reasons mentioned above. But consider you dont have access to SQL Profiler, for a strange reason ofcourse, what do you do?

Well this is where SQL Server comes to rescue with a very nice function “fn_trace_gettable“. fn_trace_gettable will give essentially the same information which you would get using SQL Profiler.

The syntax for fn_trace_gettable function is as follows

fn_trace_gettable( filename , number_files )

–read trace file using fn_trace_gettable
SELECT *
FROM fn_trace_gettable(‘D:\Audit\SampleTrace.trc’, default)

 

 

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

The true power of reading trace files using fn_trace_gettable is that you can utilise the full power of ANSI SQL. For e.g. you can order the trace according to the duration, i.e. list all the queries in the order in which it took execute. or find out which query is called most often, some times small queries called multiple times can be a performance bottleneck in its own right.

When you execute the fn_trace_gettable function and select EventClass Datacolumn, you should notice that it doesnt give you a descriptive name, but rather an integer value. To get the descriptive name you can use the following system table “sys.trace_events“, it lists all the events that you see in the SQL profiler.

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 TextData ,
        NTUserName ,
        ClientProcessID ,
        ApplicationName ,
        LoginName ,
        SPID ,
        Duration ,
        StartTime ,
        EndTime ,
        ServerName,
        eventClass,
        EventName = (SELECT NAME FROM sys.trace_events e WHERE e.trace_event_id = a.eventClass)
FROM ::fn_trace_gettable('D:\Audit\SampleTrace.trc', DEFAULT) a
WHERE EventClass IN (10,12, 41, 43, 45,46,47)

fn_trace_gettable is very powerful and very flexible. Explore it, it is an excellent tool to have in your arsenal for SQL performance tuning.