May 22, 2012 at 12:01 pm
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