How to use TRACE in query analyzer

[tsuable]

Hi,

I am in the process of optimizing my stored procedure. I am using sql Query analyzer to do a test run of the SP and get baseline figures on how optimized my SP is.

Can anyone tell me how to READ the TRACE tab? I have created indexes but somehow, the values in TRACE is not as consistent as I expect them to be.

Thanks,

 

[vongrunt]

Trace tab?

Text - SQL statement
Event class - type of traced event (SQL:StmCompleted most of the time)
Duration - duration in milliseconds, smaller = better
CPU - CPU usage, smaller = better
Reads - number of logical reads, not the same as physical reads
Writes - self-explanatory

Exactly what is not consistent

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[tsuable]

Thanks for the quick post.

this is the total of what i got when i did serveral runs which i think is not consistent...because some runs with the index, the figures look better and some don't and vice versa..

without index
D CPU Reads Writes
11240 8888 20097 1342 -->1st run
10960 10083 19922 1375 -->3rd run
10451 9844 20039 1335 -->4th run
10790 9674 19558 1299 -->7th run

with index
D CPU Reads Writes
11640 9715 19555 1344 -->2nd run
10449 9669 19351 1117 -->5th run
11246 9150 19455 1316 -->6th run
10867 9306 19288 1313 -->8th run

 

[vongrunt]

There are no significant differences... this probably means index is not used at all. Take a look at estimated execution plan instead.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[tsuable]

ok. =) i was trying to avoid looking at the execution plan because it looks much more complicated than TRACE..

How do i read the estimated execution plan?

is there a way i can save it and post it here?

thanks,

 

[SQLDenis]

yes you can do this

SET SHOWPLAN_TEXT ON
GO

--Query here



SET SHOWPLAN_TEXT OFF
GO


paste results here

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> is there a way i can save it and post it here?

Yup, with SET SHOWPLAN_TEXT ON/OFF.

Or: take a screenshot, upload it on some free image service a la imageshack and post link here. That isn't practical for large exec plans though.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[tsuable]

I used SET SHOWPLAN_TEXT for one of the line items in the stored proc..this is what i got. pardon me, i do not know how to make this any more readable..

|--Table Insert(OBJECT[DTWMS_TrainerReports].[dbo].[tmpRptUtilizationByDateRange_v2]), SET[tmpRptUtilizationByDateRange_v2].[CorpID]=[Expr1013], [tmpRptUtilizationByDateRange_v2].[RegionID]=[Expr1012], [tmpRptUtilizationByDateRange_v2].[CenterID]=[Ex
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE[Expr1004]=1, [Expr1005]='DEPARTMENT TOTALS', [Expr1006]=2.0, [Expr1007]='New Patients Registered', [Expr1008]=Convert([M].[MonthName]), [Expr1009]=Convert(isnull([Expr1003], 0)), [Expr1010]=16, [Expr1011]='(0)', [Expr
|--Compute Scalar(DEFINE[Expr1003]=Convert([globalagg1017])))
|--Stream Aggregate(GROUP BY[M].[MonthID], [M].[MonthName]) DEFINE[globalagg1017]=SUM([partialagg1016])))
|--Sort(ORDER BY[M].[MonthID] ASC, [M].[MonthName] ASC))
|--Compute Scalar(DEFINE[partialagg1016]=If ([partialagg1016] IS NULL) then 0 else [partialagg1016]))
|--Merge Join(Right Outer Join, MERGE[Expr1002])=([M].[MonthID]), RESIDUAL[Expr1002]=[M].[MonthID]))
|--Stream Aggregate(GROUP BY[Expr1002]) DEFINE[partialagg1016]=Count(*)))
| |--Sort(ORDER BY[Expr1002] ASC))
| |--Compute Scalar(DEFINE[Expr1002]=100*datepart(year, [JKAWEB05].[DTWMS_T].[dbo].[PAT_EPS].[patep_admit_dt])+datepart(month, [JKAWEB05].[DTWMS_T].[dbo].[PAT_EPS].[patep_admit_dt])))
| |--Remote Query(SOURCEJKAWEB05), QUERYSELECT PE."patep_admit_dt" Col1058 FROM "DTWMS_T"."dbo"."PAT_EPS" PE WHERE PE."patep_admit_dt">='2005-01-01T00:00:00' AND PE."patep_admit_dt"<='2005-12-31T00
|--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[DTWMS_TrainerReports].[dbo].[tmpRptSummaryByDateRangeMonths] AS [M]))
|--Index Scan(OBJECT[DTWMS_TrainerReports].[dbo].[tmpRptSummaryByDateRangeMonths].[IX_tmpRptSummaryByDateRangeMonths_MonthID] AS [M]), ORDERED FORWARD)

 

[SQLDenis]

Convert's are a table scan's best friend

Convert([globalagg1017])))
Convert([M].[MonthName]), [Expr1009]=Convert(isnull([Expr1003], 0)),


can you post some of the SQL?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[tsuable]

this is the SQL statement

INSERT INTO dbo.tmpRptUtilizationByDateRange_v2
(SectionPriority, SectionName, ItemPriority, ItemName,
MonthID, MonthName, Amount,CenterID,CenterList,
RegionID, CorpID)
SELECT 1 AS SectionPriority,
'DEPARTMENT TOTALS' AS SectionName,
2 AS ItemPriority,
'New Patients Registered' AS ItemName,
M.MonthID AS MonthID,
M.MonthName AS MonthName,
ISNULL(COUNT(PE.patep_id), 0) AS Amount,
16 AS CenterID,
'(0)' AS CenterList,
0 AS RegionID,
0 AS CorpID
FROM dbo.tmpRptSummaryByDateRangeMonths M
LEFT OUTER JOIN
(SELECT patep_id, (100 * YEAR(PE.patep_admit_dt) + MONTH(PE.patep_admit_dt)) AS MonthID
FROM JKAWEB05.DTWMS_T.dbo.PAT_EPS PE
WHERE (PE.patep_admit_dt BETWEEN '01/01/2005' AND '12/31/2005')
AND PE.CTR_ID = 16) PE ON PE.MonthID = M.MonthID
GROUP BY M.MonthName, M.MonthID

 

[vongrunt]

Run only SELECT without INSERT. How long it takes to finish?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]