Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am in the process of importing 200+ trace files (which are massive), and my current approach is to perform a loop and insert the trace data (see the below script). I looked around to see if there was a faster way to do this, whether through SSIS or C# and it appears that they still call the below function, similar to the script below this.

Anyone have any other methods that they use to import multiple traces? Don't get me wrong, the below code works, but I'm curious if there's something faster that I'm not considering.

DECLARE @start INT = 1, @stop INT = 223, @sql NVARCHAR(MAX), @path VARCHAR(1000)


WHILE @start <= @stop
BEGIN

       SET @path = 'N:\Traces\TraceFile_' + CAST(@start AS VARCHAR(5)) + '.trc'

       SET @sql = 'INSERT INTO SavedTraces
                   SELECT *
                   FROM ::fn_trace_gettable(''' + @path + ''', default)
                    '

       EXECUTE sp_executesql @sql

       SET @start = @start + 1
       SET @path = ''
       SET @sql = ''

END

Data Notes: 490MB (~.5G), which holds 11,700,000+ rows, requires 13:11 minutes to import.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

Depending on what I am looking for in the trace files, I would normally use

Fast, easy data loading: You can quickly and easily load SQL Trace files; T-SQL script output, including SQL DMV queries; and Performance Monitor logs into a SQL Server database for analysis. All three facilities use bulk load APIs to insert data quickly. You can also create your own importer for a custom file type.

Visualize loaded data via reports: Once the data is loaded, you can fire up several different charts and reports to analyze it.

Trace aggregation to show the TOP N most expensive queries (using ReadTrace).

  • Clear Trace. Very nice + it has command line options as well.

Obviously, you can use powershell as well, but I have found the above ones more useful when you have to load large number of trace files efficiently.

share|improve this answer
1  
The best PowerShell script I have seen for multiple trace files (have not gotten to use it yet though): simple-talk.com/sysadmin/powershell/… –  Shawn Melton Oct 8 '13 at 15:26
    
Also note that ClearTrace cannot be used with most trace files, it only looks at RPC:Completed and SQL:BatchCompleted, will skip everything else. –  Shawn Melton Oct 8 '13 at 15:28
    
@ShawnMelton I am not aware about that - looks at RPC:Completed and SQL:BatchCompleted, will skip everything else.. Thanks for the info! Thats the reason I mentioned - depending on what I am looking into trace files. The good thing about clear trace is that it will normalize the sql statements, which is the best thing when troubleshooting specific issues. So it depends on what you want to look into trace and then accordingly use the tools. –  Kin Oct 8 '13 at 15:40
add comment

It will be entirely dependend on where the bottleneck is. Which requires you to measure, and approach this just like any other performance problem.

  • If the bottleneck is the INSERT performance then you should make sure you achieve minimal logging. The The Data Loading Performance Guide is, of course, mandatory reading.

  • If the bottleneck is reading the traces fn_trace_gettable then... you have a problem. It could be a slow source disk (make sure the access is serialized and the head does not get randomized) or fn_trace_gettable itself may be slow (is not, is the disk, but you should measure...). Remember that even if reading the source TRC files is slow, reading many of them in parallel from non-overlapping input access paths (ie. separate disks) may achieve much better throughput.

  • If the bottleneck is... hmmm... I don't see what else could be the bottleneck. But of course, you should measure, get perf counters, identify where the time goes.

Ultimately your problem is rather esoteric so the gist of it is that you need to measure and adapt to your findings.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.