Hi Tim,

I'm using the new Server Trace event to check how long various requests take.  Although the Information field sometimes shows which stored procedure is being called, a lot of the time it is blank or has the number 2.

I have attached an example screenshot.

Is there any way to find out which stored procedure is being called?

Thanks.

= Steve

Steve,

<< I'm using the new Server Trace event to check how long various requests take.  Although the Information field sometimes shows which stored procedure is being called, a lot of the time it is blank or has the number 2. >>

Yeah, I should probably include a server trace "reader" application that interprets a lot of this.

The "2" is the handle for the query, stored procedure, etc.  It is logged as the handle because it is possible that there is an error with the handle and that it doesn't actually refer to a legitimate instance (think adversarial input from a client, or just plain old messed-up data coming across).  So, for the sake of performance and accuracy, the tracing just logs the handle.

You can get the actual stored procedure name by back-tracking until you find the Prepare Procedure for the same session name or thread ID.  It's not ideal, though, so I'll see about improving this to include the actual procedure, etc. names.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

<< Yeah, I should probably include a server trace "reader" application that interprets a lot of this.>>

That would be a handy addition.

<< You can get the actual stored procedure name by back-tracking until you find the Prepare Procedure for the same session name or thread ID. >>

Ok, I'll try doing that, maybe using a tree structure instead of a grid so I can group them.

<<  It's not ideal, though, so I'll see about improving this to include the actual procedure, etc. names. >>

That would be awesome.

Thanks.

= Steve

Steve Gill wrote:

Hi Tim,

<< Yeah, I should probably include a server trace "reader" application that interprets a lot of this.>>

I'm looking at the results from the edbsrv.exe trace files trying to identify why the server service periodically restarts. I noticed that on the service starts it overwrites the current trace file rather than appending it, is there a way of preserving what immediately occurred before the service restart

Also although the columns are similar to the TEDBServerTrace there are some additional columns, was there ever a trace reader release which would help identify the columns and codes.

Many thanks

Anthony

Anthony,

<< I'm looking at the results from the edbsrv.exe trace files trying to identify why the server service periodically restarts. I noticed that on the service starts it overwrites the current trace file rather than appending it, is there a way of preserving what immediately occurred before the service restart >>

This isn't a perfect solution, but yes, you can use the "Auto-Increment Trace File Name" setting in the EDB Server to control this in a way that will get you what you want.  The EDB Server will still overwrite the *first* trace file after restart, but the Nth trace file will still exist from the last process instance before the restart.

<< Also although the columns are similar to the TEDBServerTrace there are some additional columns, was there ever a trace reader release which would help identify the columns and codes. >>

I've still got that on the list, but for now here is what I'm using:

DDL:

CREATE TABLE "TraceEvents"
(
"SessionVersion" DECIMAL(19,2),
"SessionBuild" INTEGER,
"SessionType" VARCHAR(30) COLLATE "ANSI",
"SessionAddress" VARCHAR(60) COLLATE "ANSI",
"SessionProcessName" VARCHAR(60) COLLATE "ANSI",
"SessionName" VARCHAR(60) COLLATE "ANSI",
"SessionDescription" VARCHAR(100) COLLATE "ANSI",
"SessionThreadID" INTEGER,
"SessionEncrypted" BOOLEAN,
"EventType" VARCHAR(30) COLLATE "ANSI",
"DateTime" TIMESTAMP,
"ElapsedTime" INTEGER,
"Compression" INTEGER,
"FunctionCode" INTEGER,
"FunctionName" VARCHAR(60) COLLATE "ANSI",
"ResultCode" INTEGER,
"ResultElapsedTime" INTEGER,
"Size" INTEGER,
"Info" CLOB COLLATE "ANSI"
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768!

CREATE INDEX "FunctionCode" ON "TraceEvents" ("FunctionCode")!
CREATE INDEX "FunctionName" ON "TraceEvents" ("FunctionName" COLLATE "ANSI")!
CREATE INDEX "ResultCode" ON "TraceEvents" ("ResultCode")!
CREATE INDEX "SessionProcessName" ON "TraceEvents" ("SessionProcessName" COLLATE "ANSI")!
CREATE INDEX "SessionThreadID" ON "TraceEvents" ("SessionThreadID")!

Import:

EMPTY TABLE "TraceEvents"!

IMPORT TABLE "TraceEvents"
FROM "elevatetrace9.txt"
IN STORE "TraceFiles"
FORMAT DELIMITED
ENCODING AUTO
DELIMITER CHAR '|'
QUOTE CHAR #0
DATE FORMAT 'yyyy-mm-dd'
TIME FORMAT 'HH:mm:ss' AM LITERAL 'AM' PM LITERAL 'PM'
DECIMAL CHAR '.'
BOOLEAN TRUE LITERAL 'TRUE' FALSE LITERAL 'FALSE'
MAX ROWS -1!

UPDATE "TraceEvents" SET Info=REPLACE('<#CR#><#LF#>',#13+#10,Info)!

You'll notice that both of these are just EDB Manager query window "scripts" in that they are just regular statements and use the ! statement terminator for statement separation, as opposed to an actual EDB script.

Also, be sure to specify "UNI" instead of "ANSI" if you're using a Unicode setup.

Tim Young
Elevate Software
www.elevatesoft.com