public
anonymous / gist:1522552
Created

  • Download Gist
gistfile1.txt
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.