Column Name

Comments

Equivalent XML Execution Plan Node

total_dop

This information is available in the XML plan and now without having to parse XML, you will be able to get this information using direct SELECTs to a DMV.

                                <QueryPlan DegreeOfParallelism=”4″ MemoryGrant=”28968″ CachedPlanSize=”128″ CompileTime=”9″ CompileCPU=”9″ CompileMemory=”832″>

last_dop

min_dop

max_dop

total_grant_kb

Again you are saved from XML parsing and this information lets you get statistical averages of the memory grants per execution.

<MemoryGrantInfo SerialRequiredMemory=”7168″ SerialDesiredMemory=”7392″ RequiredMemory=”28744″ DesiredMemory=”28968″ RequestedMemory=”28968″ GrantWaitTime=”0″ GrantedMemory=”28968″ MaxUsedMemory=”4384″ />

last_grant_kb

min_grant_kb

max_grant_kb

total_used_grant_kb

Again this is something that you can fetch from the XML execution plan but it is available in the DMV now. This lets you check how much of the granted memory is being used during execution.

last_used_grant_kb

min_used_grant_kb

max_used_grant_kb

total_ideal_grant_kb

This is the a good field to look at to check if there was a difference in the ideal and the actual granted values. If this is an abnormal value, then this warrants investigation.

last_ideal_grant_kb

min_ideal_grant_kb

max_ideal_grant_kb

total_reserved_threads

This is quite useful when tracking down queries which have a high number of worker threads usage

            <ThreadStat Branches=”1″ UsedThreads=”4″>
              <ThreadReservation NodeId=”0″ ReservedThreads=”5″ />
            </ThreadStat>

last_reserved_threads

min_reserved_threads

max_reserved_threads

total_used_threads

last_used_threads

min_used_threads

max_used_threads