Using SQL Profiler

The SQL Profiler extension allows a user to locate and optimize inefficient SQL code. Microsoft's SQL Server Profiler is very similar to PEM’s SQL Profiler in operation and capabilities.

SQL Profiler works with PEM to allow you to profile a server's workload. You can install and enable the SQL Profiler extension on servers with or without a PEM agent. However, you can run traces only in ad hoc mode on unmanaged servers and you can schedule them only on managed servers.

SQL Profiler captures and displays a specific SQL workload for analysis in a SQL trace. You can start and review captured SQL traces immediately or save captured traces for review later. You can use SQL Profiler to create and store up to 15 named traces.

SQL Profiler functions

The following functions are available in SQL Profiler:

• sp_activate
• sp_deactivate
• sp_active_traces
• sp_traces_list
• sp_load_trace
• sp_cleanup
• sp_profiler_version

sp_activate

Use this function to start a new trace for a provided user and database. You can use create a trace for any database on which SQL Profiler was installed and configured. You can define a trace for logging the statements against specific users and databases. You can have multiple traces logging the query statements at the same time.

sp_activate(
	comments character varying(100), 
    users oidvector, 
    databases oidvector, 
    max_log_limit int4, 
    log_min_duration int4, 
    time_limit interval = NULL
)

Example:

Start a new trace named 'tracefortestuser', for user with OID 0011, on database with OID 134, with a maximum trace log size of 25MB, for queries that run at least 10 seconds, up to 30 minutes:

SELECT sp_activate(tracefortestuser,0011,134,25,10000,30);

sp_deactivate

Use this function to stop a trace for the given trace_id. The function will return true on success.

sp_deactivate(
	OUT trace_id         text,
)

Example:

Stop a trace with trace_id 10:

SELECT sp_deactivate(10);

sp_active_traces

Use this function to show the details of active traces.

sp_active_traces(
	OUT trace_id         text,
	OUT owner            oid,
	OUT start_time       timestamptz,
	OUT max_log_size     int4,
	OUT curr_log_size    int4,
	OUT log_min_duration int4,
	OUT running_queries  int4,
	OUT users            oidvector,
	OUT databases        oidvector
)

Example:

SELECT sp_active_traces()

For superusers: List down all the active trace(s). For non-superusers: List down the active trace(s) initiated by them.

sp_traces_list

Use this function to list the details of all traces.

sp_traces_list(
	OUT trace_id    text,
	OUT comments    text,
	OUT start_time  timestamptz,
	OUT owner       oid,
	OUT users       oidvector,
	OUT databases   oidvector,
	OUT max_size    int4,
	OUT end_time    timestamptz,
	OUT finish_time timestamptz,
	OUT status      int4,
	OUT version     float8
)

Example:

SELECT sp_traces_list()

For superusers: List down all the active trace(s). For non-superusers: List down the active trace(s) initiated by them.

sp_load_trace

Use this function to load all the trace files of a particular trace_id into a temporary table named _sp_tmp_tbl_sql_profiler. Returns true on successful loading of the trace.

sp_load_trace(
	IN trace_id  text,
    IN refresh   bool
)

Example:

Load the trace files for trace_id 10, removing the temp table data for trace_id 10, and reload it from scratch:

SELECT sp_load_trace(10, true)

sp_cleanup

Use this function to permanently remove a trace. Returns true on successful removal of the trace.

sp_cleanup(
	IN trace_id  text,
    IN remove_active   bool
)

Example:

Permanently remove the trace files for trace_id 10, ensusring that trace_id 10 is not the active trace:

SELECT sp_cleanup(10, true)

sp_profiler_version

Use this function to show the current version of SQL Profiler.

sp_profiler_version()

Example:

Show the current version of SQL Profiler:

SELECT sp_profiler_version();