Query Actual Execution Plan VERY different from Trace results

Question

I have rebuilt indexes and updated statistics.

The query is straightforward, with a subquery in the WHERE clause.

SELECT TOP 1 * from MeetingPost_reg
WHERE userid = 1234 AND meetingpost_regid <> 9999 
AND DateStart < (SELECT DateStart FROM MeetingPost_reg WHERE meetingpost_regid = 9999)
ORDER BY DateStart desc

There is an index on datastart, userid. meetingpost_regid is the PK, with clustered index.

SHOWPLAN_TEXT:

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  |--Compute Scalar(DEFINE:([MeetingPost_reg].[message]=[MeetingPost_reg].[message], [MeetingPost_reg].[ProcessedComment]=[MeetingPost_reg].[ProcessedComment], [MeetingPost_reg].[ProcessedMsg]=[MeetingPost_reg].[ProcessedMsg], [MeetingPost_reg].[pos_discou
       |--Top(1)
            |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([dbo].[MeetingPost_reg]))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([MeetingPost_reg].[datestart]))
                      |--Clustered Index Seek(OBJECT:([dbo].[MeetingPost_reg].[PK_MeetingPost_reg]), SEEK:([MeetingPost_reg].[meetingpost_regid]=9999) ORDERED FORWARD)
                      |--Index Seek(OBJECT:([dbo].[MeetingPost_reg].[MeetingPost_reg12]), SEEK:([MeetingPost_reg].[datestart] < [MeetingPost_reg].[datestart]),  WHERE:([MeetingPost_reg].[meetingpost_regid]<>9999 AND Convert([MeetingPost_reg

(6 row(s) affected)

Execution Plan:

The Execution Plan diagram shows an Index Seek with 0 rows, and a Clustered Index Seek with 1 row, for a total Cost of ~ .0006, total CPU Cost ~ .002

The Trace shows a Duration of 250, CPU 172, Reads 11. The Trace Event Class is SQL:StmtCompleted

Why is this query using so much CPU?

Answer 1

Profiler event shows 172ms worker time and 250ms elapsed time, 11 page reads. Is that for the SQL:StmtCompleted or for the SQL:BatchCompleted event? If later, is this query the only statement in the batch?

For the execution plan, you better post the actual plan, reducing an entire plan to 2 operators and 4 numbers (0, 1, .0006 and .002) looses quite a lot from the plan information.

Does the captured plan and the statement execution refer to exactly the same execution? If yes, have you considered that the cost of returning the plan is included in the trace event?