Script

SET NOCOUNT ON;

IF OBJECT_ID('T_TESTS', 'U') IS NOT NULL
  DROP TABLE T_TESTS

CREATE TABLE T_TESTS
  (
     TestId    UNIQUEIDENTIFIER,
     FirstName VARCHAR(50),
     LastName  VARCHAR(50),
     Age       INT
  )

IF OBJECT_ID('Results', 'U') IS NOT NULL
  DROP TABLE Results

CREATE TABLE Results
  (
     EventSequence  BIGINT NOT NULL,
     CachedPlanSize INT NULL,
     CompileTime    INT NULL,
     CompileCPU     INT NULL,
     CompileMemory  INT NULL,
     Mode           INT NOT NULL 
  );

GO

DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT = 5000
DECLARE @spid INT = @@SPID
DECLARE @filepath NVARCHAR(200);
DECLARE @i INT = 0;
DECLARE @Script NVARCHAR(max);

DECLARE @Mode int = 0
WHILE @Mode < 3
BEGIN
	SET @filepath = N'C:\trace_' + LEFT(NEWID(), 36)

	EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL

	EXEC sp_trace_setevent @TraceID, 146, 1, 1
	EXEC sp_trace_setevent @TraceID, 146, 22, 1
	EXEC sp_trace_setevent @TraceID, 146, 34, 1
	EXEC sp_trace_setevent @TraceID, 146, 51, 1
	EXEC sp_trace_setevent @TraceID, 146, 12, 1
	-- filter for spid
	EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @spid
	-- start the trace
	EXEC sp_trace_setstatus @TraceID, 1

	SET @Script = N'INSERT INTO T_TESTS (TestId, FirstName, LastName, Age)
	VALUES '
	SET @i = 0;


	WHILE( @i < 1000 )
	  BEGIN
		  IF ( @i > 0 )
			SET @Script = @Script + ',';

		  IF ( @Mode = 0 )
			SET @Script = @Script + '(''' + CAST(NEWID() AS VARCHAR(50)) + ''', ''F' + STR(@i, 9) + ''', ''L' + STR(@i, 9) + ''', ' + LEFT(@i, 10) + ')'
		  ELSE IF ( @Mode = 1 )
			SET @Script = @Script + '(''' + CAST(NEWID() AS VARCHAR(50)) + ''', ''F' + STR(@i, 49) + ''', ''L' + STR(@i, 49) + ''', ' + LEFT(@i, 10) + ')'
		  ELSE IF ( @Mode = 2 )
			SET @Script = @Script + '(''' + CAST(NEWID() AS VARCHAR(50)) + ''', ''F' + STR(1, 9) + ''', ''L' + STR(1, 9) + ''', ' + LEFT(@i, 10) + ')'

		 --Go up in steps of 5 after 300 for speed reasons
          IF (@i < 300 OR @i % 5 = 0)
          BEGIN
		  EXEC(@Script)

		  RAISERROR('@Mode = %d, @i = %d',0,1,@Mode, @i) WITH NOWAIT
		  END

          
		  SET @i += 1;
	  END;

	WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sql)
		INSERT INTO Results
		SELECT EventSequence,
			   runTimeCounters.*
				 FROM fn_trace_getinfo(@TraceID) fn
						CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1)
						CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x
						CROSS APPLY (SELECT
			xPlan.value('(//sql:QueryPlan/@CachedPlanSize)[1]', 'int') AS CachedPlanSize,
			xPlan.value('(//sql:QueryPlan/@CompileTime)[1]', 'int') AS CompileTime,
			xPlan.value('(//sql:QueryPlan/@CompileCPU)[1]', 'int') AS CompileCPU,
			xPlan.value('(//sql:QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemory,
			@Mode AS Mode
		) runTimeCounters
				 WHERE property = 2
					   AND TextData LIKE '%T_TESTS%'
	       
	EXEC sp_trace_setstatus @TraceID,0

	-- Close and delete the trace
	EXEC sp_trace_setstatus @TraceID,2
	SET @Mode += 1
	END

;WITH T AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY Mode ORDER BY EventSequence) AS I,
       CachedPlanSize,
       CompileTime,
       CompileCPU,
       CompileMemory,
       Mode
FROM   Results)
SELECT  CASE WHEN I < 300 THEN I ELSE (I-300)*5 + 300 END AS RowsInserted,
        *
FROM T        
ORDER  BY Mode, I