Skip to content

Instantly share code, notes, and snippets.

Created December 27, 2011 02:22
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save anonymous/1522552 to your computer and use it in GitHub Desktop.
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