|
|
Trace Messages Part IV: Getting the Trace Results
Volume 3 #6
February 8,
2005 Full TOC
by Andrew Novick
Sign up for this newsletter at:
http://www.novicksoftware.com/coding-in-sql/coding-in-sql-signup.htm
The
current theme is about application trace messages. So far we’ve
created AppSession rows to organize a session, AppSessionTrace
rows with the trace lines, and AppSessionMeasurement rows to store
metrics. At the end of each script that uses the usp_AppSession_*
stored procedures you will see a few lines of script that retrieve
the session, trace, and metric information. Here’s a sample:
DECLARE @RC int,
@TID int
EXEC @RC = usp_AppSession_Example
'00:00:10'
-- Duration 10 seconds
, 3 --
Trace Level Informational
, @TID OUTPUT --
AppSession.SessionID
PRINT 'RC='+
coalesce(convert(varchar(20),@RC), '<null>')
+ ' Session = ' +
coalesce(convert(varchar(20),@TID), '<null>')
SELECT * FROM AppSession WHERE SessionID = @TID
SELECT * FROM AppSessionTrace where SessionID = @TID
SELECT * From AppSessionMeasurement WHERE SessionID = @TID
GO
(Results – Truncated
on the Right)
11:41:30:123-usp_AppSession_Example-Testing dur=1900-01-01
00:00:10. Session=32
11:41:30:123-usp_AppSession_Example_W-Worker1 Result1=7
11:41:30:123-usp_AppSession_Example_W-Worker2 Result2=9
11:41:33:123-usp_AppSession_Example_W-Worker1 Result1=3
11:41:33:123-usp_AppSession_Example_W-Worker2 Result2=2
11:41:36:123-usp_AppSession_Example_W-Worker1 Result1=4
11:41:36:123-usp_AppSession_Example_W-Worker2 Result2=1
11:41:39:123-usp_AppSession_Example_W-Worker1 Result1=0
11:41:39:123-usp_AppSession_Example_W-Worker2 Result2=2
RC=0 Session = 32
SessionID ApplicationID SessionGUID
----------- ------------- -----------------------------------
32 0 E6432C60-1F42-4E5A-BA78-1D050A3F9F2
(1 row(s) affected)
TraceMessageID SessionID Source Category
-------------- ----------- ------------------------ ------------
(0 row(s) affected)
SessionID ParameterName Measurement
----------- -------------------------------- ----------------------
32 Worker1 Tot 14.0
32 Worker2 Tot 14.0
32 WorstError 0.0
(3 row(s) affected)
That works okay but
could be improved in several ways:
- Eliminate the (n
rows(s) affected) messages to simplify the output.
- Better selection
of columns would show more interesting information in the area
of the results pane that doesn't have to be scrolled.
- Eliminating
columns such as SessionID that don't add value.
- Ensuring the
ordering of rows by adding ORDER BY clauses.
To get these
improvements I've created two SQL Server objects:
udf_AppSession_Trace and usp_AppSession_Rpt. Let's start with the
user-defined function. Here's the CREATE FUNCTION script:
CREATE FUNCTION
dbo.udf_AppSession_Trace (
@SessionID int
-- Session to retrieve
) RETURNS TABLE
/*
* Retrieves the columns of AppSessionTrace for easy reading
*
* Example:
select * FROM dbo.udf_AppSession_Trace (1)
* History:
* When Who Description
* ------------- ------- -----------------------------------------
* 2004-12-04 ASN Initial Coding
*
* © Copyright 2005 Andrew Novick http://www.NovickSoftware.com
* You may use this code in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* udf either in print or electronically.
* Published as Coding in SQL Newsletter Vol 3 #6 2/8/05
http://www.NovickSoftware.com/coding-in-sql.htm
****************************************************************/
AS RETURN
SELECT TOP 100 Percent WITH TIES
SUBSTRING (CONVERT(varchar,
MsgDT, 121), 12, 12) [Time]
, Msg
, Source
, Category
, MessageTraceLevel
FROM AppSessionTrace
WHERE SessionID = @SessionID
ORDER BY TraceMessageID
GO
GRANT SELECT on
dbo.udf_AppSession_Trace to PUBLIC
GO
I don't use udf_AppSession_Trace directly.
Instead I used to store procedure to get results from all tables.
Here's the CREATE PROC script:
CREATE PROCEDURE
dbo.usp_AppSession_Rpt
@SessionID int
-- Session ID from usp_AppSession_Create
/* Reports on a SessionID including it's AppSession,
* AppSessionTrace, and AppSessionMeasurement rows.
*
* Example:
Declare @SessionID int
, @User_Name varchar(20)
exec dbo.usp_AppSession_Create 0, 'SQLJOB', @@SPID, 'MyServer'
, 'usp_AppSession_End', 0, 'TestServer'
, 'TestUser'
, @SessionID OUTPUT
exec dbo.usp_AppSession_End @SessionID
, 'TESTSTRING|Test Date|Test Number|TEST Missing'
, 'My Measure|7/10/1956|17.234'
, '|' -- Pipe as seperator
exec dbo.usp_AppSession_Rpt @SessionID
*
* © Copyright 2005 Andrew Novick http://www.NovickSoftware.com
* You may use this code in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* procedure either in print or electronically.
* Published as Coding in SQL Newsletter Vol 3 #6 2/8/05
http://www.NovickSoftware.com/coding-in-sql.htm
**************************************************************/
AS
SET NOCOUNT ON
---------------------------------------------------------------
---------------------------------------------------------------
-- Session
SELECT SessionID [Ses]
, ApplicationID [App]
, CreatedDT [Run]
, DurationDur [Duration]
, ClientProgramName [Cli-Prog]
, ClientComputerName [Cli-Comp]
, ProgramTypeCD [Prog]
FROM dbo.AppSession
WHERE SessionID=@SessionID
---------------------------------------------------------------
---------------------------------------------------------------
-- Trace
SELECT *
FROM dbo.udf_AppSession_Trace(@SessionID)
---------------------------------------------------------------
---------------------------------------------------------------
-- Measurements
SELECT ParameterName
, Measurement
FROM dbo.AppSessionMeasurement
WHERE SessionID=@SessionID
ORDER BY ParameterName
GO
GRANT EXEC on dbo.usp_AppSession_Rpt
to PUBLIC
GO
Now let's try out that original scripts using
usp_AppSession_Rpt:
DECLARE @RC int,
@TID int
EXEC @RC = usp_AppSession_Example
'00:00:10'
-- Duration 10 seconds
, 3 --
Trace Level Informational
, @TID OUTPUT --
AppSession.SessionID
PRINT 'RC='+
coalesce(convert(varchar(20),@RC), '<null>')
+ ' Session = ' +
coalesce(convert(varchar(20),@TID), '<null>')
exec dbo.usp_AppSession_Rpt @TID
(Results – Truncated on the right)
11:58:26:937-usp_AppSession_Example-Testing dur=1900-01-01 00:00:10.
Session=33
11:58:26:937-usp_AppSession_Example_W-Worker1 Result1=7
11:58:26:937-usp_AppSession_Example_W-Worker2 Result2=1
11:58:29:937-usp_AppSession_Example_W-Worker1 Result1=0
11:58:29:937-usp_AppSession_Example_W-Worker2 Result2=4
11:58:32:937-usp_AppSession_Example_W-Worker1 Result1=4
11:58:32:937-usp_AppSession_Example_W-Worker2 Result2=3
11:58:35:950-usp_AppSession_Example_W-Worker1 Result1=7
11:58:35:950-usp_AppSession_Example_W-Worker2 Result2=6
RC=0 Session = 33
Ses App Run Duration
----------- -----------
------------------------------------------------------
33 0 2005-01-25 11:58:26.937 1900-01-01
00:00:12.013
Time Msg
------------
-------------------------------------------------------------------
ParameterName Measurement
--------------------------------
-----------------------------------------------
Worker1 Tot 18.0
Worker2 Tot 14.0
WorstError 0.0
The results are still truncted on the right. However,
there are few less extraneous messages, a few more fields on the
left-hand side of the results pane, and therefore a little bit less
scrolling. I find the output of usp_AppSession_Rpt
easier to read.
In the next issue, we'll wrap up this series
with store procedure used to purge the AppSession, AppSessionTrace,
and AppSessionMeasurement tables. After a while all those rows
tend to add up.
As always, if you have comments about the
newsletter and in particular, the new direction that I’m planning on
taking, please let me know at
coding-in-sql@novicksoftware.com.
Transact-SQL User-Defined Functions has been published! Take a look at it
now!
Do you have an interesting stored procedure, user-defined-function,
or other sample code? If you'd like to share them please send
them in an email to:
coding-in-sql@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andy
Andrew Novick
|
|
