Skip to content

Instantly share code, notes, and snippets.

Created January 8, 2012 22:52
  • 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/1580007 to your computer and use it in GitHub Desktop.
SET NOCOUNT ON;
IF OBJECT_ID('T_TESTS', 'U') IS NOT NULL
DROP TABLE T_TESTS
CREATE TABLE T_TESTS
(
TestId UNIQUEIDENTIFIER,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
)
IF OBJECT_ID('Results', 'U') IS NOT NULL
DROP TABLE Results
CREATE TABLE Results
(
EventSequence BIGINT NOT NULL,
CachedPlanSize INT NULL,
CompileTime INT NULL,
CompileCPU INT NULL,
CompileMemory INT NULL,
Mode INT NOT NULL
);
GO
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT = 5000
DECLARE @spid INT = @@SPID
DECLARE @filepath NVARCHAR(200);
DECLARE @i INT = 0;
DECLARE @Script NVARCHAR(max);
DECLARE @Mode int = 0
WHILE @Mode < 3
BEGIN
SET @filepath = N'C:\trace_' + LEFT(NEWID(), 36)
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
SET @Script = N'INSERT INTO T_TESTS (TestId, FirstName, LastName, Age)
VALUES '
SET @i = 0;
WHILE( @i < 1000 )
BEGIN
IF ( @i > 0 )
SET @Script = @Script + ',';
IF ( @Mode = 0 )
SET @Script = @Script + '(''' + CAST(NEWID() AS VARCHAR(50)) + ''', ''F' + STR(@i, 9) + ''', ''L' + STR(@i, 9) + ''', ' + LEFT(@i, 10) + ')'
ELSE IF ( @Mode = 1 )
SET @Script = @Script + '(''' + CAST(NEWID() AS VARCHAR(50)) + ''', ''F' + STR(@i, 49) + ''', ''L' + STR(@i, 49) + ''', ' + LEFT(@i, 10) + ')'
ELSE IF ( @Mode = 2 )
SET @Script = @Script + '(''' + CAST(NEWID() AS VARCHAR(50)) + ''', ''F' + STR(1, 9) + ''', ''L' + STR(1, 9) + ''', ' + LEFT(@i, 10) + ')'
--Go up in steps of 5 after 300 for speed reasons
IF (@i < 300 OR @i % 5 = 0)
BEGIN
EXEC(@Script)
RAISERROR('@Mode = %d, @i = %d',0,1,@Mode, @i) WITH NOWAIT
END
SET @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,
@Mode AS Mode
) 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
SET @Mode += 1
END
;WITH T AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY Mode ORDER BY EventSequence) AS I,
CachedPlanSize,
CompileTime,
CompileCPU,
CompileMemory,
Mode
FROM Results)
SELECT CASE WHEN I < 300 THEN I ELSE (I-300)*5 + 300 END AS RowsInserted,
*
FROM T
ORDER BY Mode, I
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment