public
anonymous / Script
Created

  • Download Gist
Script
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 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118
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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.