Is there an alternative to SET STATISTICS TIME which also shows the statements?

Question

SET STATISTICS TIME statement is only useful while developing as with it one can performance tune additional statement being added to the query or UDF/SP being worked on. However when one has to performance tune existing code, e.g. a SP with hundreds or thousands of lines of code, the output of this statement is pretty totally useless as it is not clear which to which SQL-statement the recorded times belong to.

Isn't there any alternatives to SET STATISTICS TIME which also show the Statements to which the recorded times belong to?

Answer 1

I would recommend to use advanced tool. Here is example of one call of sp with all and every internal details. On the right you have different runs history which can be commented and analyzed later. All you need for stats/index usage/io/waits - everything available on different tabs. Util: SentryOne Plan Explorer (free).

Answer 2

If your Stored Procedures are granular then you could use this DMV to get an idea of times.

SELECT 
    DB_NAME(qs.database_id) AS DBName
    ,qs.database_id
    ,qs.object_id
    ,OBJECT_NAME(qs.object_id,qs.database_id) AS ObjectName
    ,qs.cached_time
    ,qs.last_execution_time
    ,qs.plan_handle
    ,qs.execution_count
    ,total_worker_time
    ,last_worker_time
    ,min_worker_time
    ,max_worker_time
    ,total_physical_reads
    ,last_physical_reads
    ,min_physical_reads
    ,max_physical_reads
    ,total_logical_writes
    ,last_logical_writes
    ,min_logical_writes
    ,max_logical_writes
    ,total_logical_reads
    ,last_logical_reads
    ,min_logical_reads
    ,max_logical_reads
    ,total_elapsed_time
    ,last_elapsed_time
    ,min_elapsed_time
    ,max_elapsed_time
FROM 
    sys.dm_exec_procedure_stats qs