Created
December 27, 2011 02:22
-
-
Save anonymous/1522552 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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