Earlier I have written article about how to automate process to start and stop SQL Server Profiler Trace. Server side tracing generates .TRC file which is hard to read if you do it manually.
I like set based data and it is great if we can execute SELECT statement on trace file so that either we can see properly managed result set or store the result in SQL Server table by INSERT….SELECT command or by SELECT….INTO command.
As I said trace files are very powerful weapon of DBA and they can’t afford to make distance of it. Trace file can keep close eyes on each batch / query / SP runs in your database for 24*7 and helps you to debug the issues. Trace file always helps me a lot in finding the performance related issues.
I will use the same trace file of AdventureWorks2012 database which I have generated from my earlier article. You can generate same kind of file from the article or you can have your own trace file.
Here is the SELECT statement I use:
SELECT ServerName ,DatabaseName ,SPID AS SessionId ,ObjectName ,EventClass AS EventNumber ,RowCounts ,CASE WHEN Duration IS NOT NULL THEN Duration/1000000.00 ELSE Duration END AS DurationInSeconds ,CPU AS CPUInMiliseconds ,StartTime ,EndTime ,TextData FROM fn_trace_gettable('D:\TraceCollection\AdventureWorks2012.trc', DEFAULT) GO
This script is very easy yet powerful and part of my day-to-day DBA duty.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.