Developer Forum

You might consider loading the trace file into a temp table for ad-hoc
analysis:

SELECT *
INTO #MyTraceData
FROM ::fn_trace_gettable('C:\Traces\trace 10272005.trc', 1)
GO

Using this table, I identified the top 10 long-running queries:

SELECT TOP 10 Duration, StartTime, TextData
FROM #MyTraceData
ORDER BY Duration DESC

Of all the queries in your trace. only one ran longer than a second. The
query below ran about 30 seconds: It looks like it's doing a lot of
formatting, which is probably better handled in the presentation layer
rather than Transact-SQL. Some tuning might help too.

SELECT
RTRIM(cast(dbo.la_fn_PadNumber (INC2_CASE_NO_1, 4) AS char)) + '-' +
RTRIM(cast(dbo.la_fn_PadNumber (INC2_CASE_NO_2, 7) AS char)) + '(' +
INC2_CASE_NO_X + ')',
RTRIM(cast(dbo.la_fn_PadNumber (INC2_SUPP_NO, 3) AS char)),
RTRIM(dbo.la_fn_GetClass_By_CaseNo(INC2_CASE_NO_1, INC2_CASE_NO_2,
INC2_CASE_NO_X)),
convert(varchar(10),INC2_SUPP_DATE,101),
case when INC2_STATUS = 0 then 'REOPENED'
when INC2_STATUS = 1 then 'RETURNED'
when INC2_STATUS = 2 then 'NO ACTION'
when INC2_STATUS = 3 then 'PENDING'
when INC2_STATUS = 4 then 'APPROVED'
when INC2_STATUS = 8 then 'REVIEWED'
else 'UNKNOWN' end,
dbo.la_fn_GetPerName(INC2_RPT_OFCR_1),
dbo.la_fn_MLB1_GetDesc(INC2_C_STAT_S,
INC2_C_STAT_L,
INC2_C_STAT_C),
INC2_CASE_NO_1,
INC2_CASE_NO_2,
INC2_CASE_NO_X,
INC2_SUPP_NO
FROM INC2 JOIN INC1 ON
INC2_CASE_NO_1 = INC1_CASE_NO_1 AND
INC2_CASE_NO_2 = INC1_CASE_NO_2 AND
INC2_CASE_NO_X = INC1_CASE_NO_X
WHERE (INC2_CASE_NO_1 = 2001 AND INC2_CASE_NO_2 !> 0012061) OR
( INC2_CASE_NO_1 < 2001)
ORDER BY
INC2_CASE_NO_1 DESC,
INC2_CASE_NO_2 DESC,
INC2_CASE_NO_X, INC2_SUPP_NO


--
Hope this helps.

Dan Guzman
SQL Server MVP