Skip to content

Instantly share code, notes, and snippets.

/gist:1522552

Created December 27, 2011 02:22
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save anonymous/1522552 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save anonymous/1522552 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @invalid-email-address Anonymous created this gist Dec 27, 2011.
    80 changes: 80 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,80 @@
    SET NOCOUNT ON;
    DROP TABLE Results

    CREATE TABLE Results(
    EventSequence bigint NULL,
    CachedPlanSize int NULL,
    CompileTime int NULL,
    CompileCPU int NULL,
    CompileMemory int NULL
    );


    DECLARE @TraceID INT
    DECLARE @maxfilesize BIGINT = 5000
    DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36)
    DECLARE @spid INT = @@spid

    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



    DECLARE @Script nvarchar(max) = N'INSERT INTO T_TESTS (TestId, FirstName, LastName, Age)
    VALUES '


    declare @i INT = 0;

    WHILE(@i < 1000)
    BEGIN

    IF (@i = 0)
    SET @Script = @Script + '(''' + CAST(NEWID() AS VARCHAR(50)) + ''', ''First ' + LEFT(@i,10) + ''', ''Last ' + LEFT(@i,10) + ''', ' + LEFT(@i,10) + ')'
    ELSE
    SET @Script = @Script + ',(''' + CAST(NEWID() AS VARCHAR(50)) + ''', ''First ' + LEFT(@i,10) + ''', ''Last ' + LEFT(@i,10) + ''', ' + LEFT(@i,10) + ')'
    EXEC(@Script)

    raiserror('@i = %d',0,1, @i) with nowait

    SET @i = @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
    ) 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

    SELECT ROW_NUMBER() OVER (ORDER BY EventSequence),
    CachedPlanSize,
    CompileTime,
    CompileCPU,
    CompileMemory
    FROM Results
    ORDER BY EventSequence