dm_exec_procedure_stats's total_worker_time doesnt tell true story

  • Usually people compare the procedure performance based on the data in the dm_exec_procedure_stats table.Howver,

    total_worker_time( or cput time) counter doesnt show the correct value if the plan chosen is parallel. Other counter shows the correct values ( atleast for a single query. I will try it for multiple query and post if it will have any issue).

    Run the below code and see yourself..

    --create a proc use the salesorderheader and salesorderdetail table.

    --Use an order by to make sure that we get a parallel plan

    drop proc myparallelplanproc

    go

    create proc myparallelplanproc

    as

    begin

    select *

    --/parallelplan/--

    from sales.SalesOrderDetail sod

    inner join sales.SalesOrderHeader soh on sod.SalesOrderID = soh.SalesOrderID

    order by LineTotal desc

    end

    go

    --execute the proc say 5 times...Before running it discard the result set using the ssms result set option

    exec myparallelplanproc

    go 5 --exec 5 times...

    --run the below in separate ssms window

    --technically the elapsed time and cpu time of proc should be >= elapsed time and cpu time of the query in the procedure(I am considering just 1 sql statement)

    select OBJECT_NAME(object_id),deps.total_worker_time,deps.total_logical_reads,deps.total_elapsed_time, * from sys.dm_exec_procedure_stats deps where OBJECT_NAME(object_id) like '%myparallelplanproc%'

    select object_name(dest.objectid) procname,deqs.total_worker_time,deqs.total_logical_reads,deqs.total_elapsed_time,* from sys.dm_exec_query_stats deqs cross apply sys.dm_exec_sql_text(deqs.sql_handle) dest

    where dest.text like '%--/parallelplan/--%' and dest.text not like '%deqs%'

    go

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply