What does SQL Server execution plan show?

Question

There is the following code:

declare @XmlData xml =
'<Locations>
<Location rid="1"/>
</Locations>'

declare @LocationList table (RID char(32));
insert into @LocationList(RID)
select Location.RID.value('@rid','CHAR(32)') 
from @XmlData.nodes('/Locations/Location') Location(RID)

insert into @LocationList(RID)
select A2RID from tblCdbA2

Table tblCdbA2 has 172810 rows.

I have executed the batch in SSMS with “Include Actual execution plan “ and having Profiler running.

The plan shows that the first query cost is 88% relative to the batch and the second is 12%, but the profiler says that durations of the first and second query are 17ms and 210 ms respectively, the overall time is 229, which is not 12 and 88.. What is going on? Is there a way how I can determine in the execution plan which is the slowest part of the query?

Answer 1

Duration != cost.

Cost includes other factors, such as creating and freeing objects (like your XML Read with XPath filter), memory usage, writes, reads, temp table access, etc.

Edit:

Look at your Execution Plan, and hover over the parts that "cost" the most. In your case, it "costs" the server (in compile time, cpu time, memory time, etc) the most for creating and executing the XML Reader and filter table valued function.

Another thing to try is to use your SQL Server Profiler, and monitor the events "Showplan All", and "Showplan XML". You can get the same views as the Execution Plan by clicking on the "Showplan XML" event in the profiler, and get extra details by clicking on the "Showplan All" event.

Here's a good article that discusses these events:

Capturing Graphical Query Plans with SQL Server Profiler