Importing Multipe Trace Files To A SQL Server Table 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 &lt;= @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.
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.