Trace - object descriptions
When you have installed the MiracleDB - trace component, you have these objects of interests:

  • trace.Init
    • This stored procedure creates a trace on the SQL-server.
  • trace.Start
    • This stored procedure starts a trace created by trace.Init
  • trace.Stop
    • This stored procedure stops a trace created by trace.Init and started by trace.Start
  • trace.Remove
    • This stored procedure removes the trace completely from the SQL-server.
  • trace.Status
    • This stored procedure returns all traces from your SQL-server and their current status.
      Also traces not initiated by MircleDB - traces.
  • trace.Import
    • This stored procedure imports data from the collected trace files into a table (trace.Result).
  • trace.Report
    • This stored procedure selects the data stored from the import and displays the data in a easy overview.

Remark:

If unsure if there is traces on your system that you have not stopped at any point, you can run:

SELECT	*
FROM	sys.traces

 

Default trace (id = 1) can be disabled, but is not part of these scripts
Should you find traces and wish to stop them, simply run:

 

sp_trace_setstatus id, 0 /* Stop the trace */
sp_trace_setstatus id, 2 /* Remove the trace */

 

Examples

Creating a trace:

DECLARE @intTraceId INT
EXEC @intTraceId = trace.Init
@intTypeId = 1, /* Allways = 1, not yet implemented */
@intRunTimeInMiliseconds = 0, /* For how long do you wish the trace to run */
@intMaxSize = 50, /* Max file size in MB for each trace file */
@intRollOver = true, /* When a tracefile is filled up, should it start a new one */
@intDurationGreaterThanInMiliseconds = 0, /* Filter to only collect executions that run for more than x milliseconds */
@intReadsGreaterThan = 0, /* Filter to only collect executions that reads more than x times */
@intWritesGreaterThan = 0, /* Filter to only collect executions that writes more than x times */
@strPath = 'G:\TraceFiles', /* Path to store trace files */
@intAutoStart = 0 /* Specifies if you wish to start the trace right away */

PRINT @intTraceId /* Prints out the TraceId */

Starting a trace:
Prerequisite: Create a trace.

EXEC trace.Start @intTraceId

Stopping a trace:
Prerequisite: Trace must be started or set to autostart.

EXEC trace.Stop @intTraceId

Removing a trace from SQL-server:
Prerequisite: Trace must be stopped.

EXEC trace.Remove @intTraceId

Getting status of all traces, including traces not started by this tool:

EXEC trace.Status	

Import trace from tracefiles:
Prerequisite: Trace must be stopped.

EXEC trace.Import 
	@intTypeId = 1,					/* Allways = 1, not yet implemented */
	@strDateStart = '2010-07-28',			/* Start date for trace files */
	@strDateStop = '2010-07-28',			/* Stop date for trace files */
	@strHourStart = '00:00:01',			/* Start time for trace files (If date expands over several days, it will only include traces from this time) */
	@strHourStop = '23:59:59',			/* Stop time for trace files (If date expands over several days, it will only include traces to this time) */
	@intAppend = 0					/* Append to existing imports. As long as the files (and MiracleDB) exists you can always import files again. */

Looking at the result:
Prerequisite: None, but to get results you must import first.

EXEC trace.Report 
	@dtmDateStart = '2010-07-28 00:00:00',		/* From time to look at */
	@dtmDateStop = '2010-07-28 23:59:59',		/* To time to look at */
	@intReportTypeId = 1,				/* 1: reads, 2: duration, 3: writes, 4: occurencies, 5: CPU */
	@intMinValue = 0,				/* Min value for the type selected in ReportType */
	@intMax = 100					/* Max number of results */

Last edited Sep 24, 2010 at 8:23 AM by kimwkristensen, version 13

Comments

No comments yet.