Skip to content

anonymous /gist:1522552
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.